将公式改为
=index(sheet1!c:c,max((sheet1!$a$2:$a$20=a1)*(sheet1!$b$2:$b$20=b1)*row($2:$20)))
双击该单元格,按“shift"+"ctrl"+"enter"三键完成公式,这时公式多了{}再将公式向下填充,如图:
不要”0“值,可在”工具“-->”选项“-->里进行设置,也可将公式改为:
=if(or(a1="",b1=""),"",index(sheet1!c:c,max((sheet1!$a$2:$a$20=a1)*(sheet1!$b$2:$b$20=b1)*row($2:$20))))