EXCEL中多条件查找并引用数据的方法
EXCEL中多条件查找并引用数据的方法
SHEET1工作
C
SHEET2工作表C1单元格使用以下数组公式,可达到目的:
IF(OR(A1="",B1=""),"",OFFSET(Sheet1!$C$1,SUM(IF((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$100注意:输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
补充:
用VLOOKUP函数解决方法:
IF(OR(A1="",B1=""),"",VLOOKUP(A1&B1,IF({1,0},Sheet1!A$1:$A$1000&Sheet1!B$1:B$1000,Sheet1用INDEX和MATCH函数解决方法:
IF(OR(A1="",B1=""),"",INDEX(Sheet1!C$1:C$1000,MATCH(A1&B1,Sheet1!A$1:A$1000&Sheet1!B$1这两个也是数组公式。
另提供两个不用数组公式的解决方法:
IF(OR(A1="",B1=""),"",INDIRECT("Sheet1!$C"&SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B IF(OR(A1="",B1=""),"",LOOKUP(2,1/((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)),Sheet1! #REF!
题,SHEET1工作表内容如图:
表中查找并引用相应的C列的内容。SHEET2工作表如图:
A1)*(Sheet1!B$1:B$1000=B1),ROW(Sheet1!C$1:C$1000),0))-1,0,1,1)) et1!B$1:B$1000,Sheet1!C$1:C$1000),2,0))
$1:A$1000&Sheet1!B$1:B$1000,0)))
A$1000=A1)*(Sheet1!B$1:B$1000=B1)*ROW(Sheet1!C$1:C$1000)))) 1:B$1000=B1)),Sheet1!C$1:C$1000))