excel表格如果sheet2A列=sheet1A列且sheet2B列=sheet1B列且sheet2C列=sheet1C列,在sheet2E列返回sheet1E

Question

excel表格如果sheet2A列=sheet1A列且sheet2B列=sheet1B列且sheet2C列=sheet1C列,则在sheet2E列返回sheet1E的数据，两张表的每行顺序是不一样的。怎么设置函数？

Answer 1

Sheet2中的E2单元格输入以下公式：
=IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"",IF(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))<>B2,"",IF(INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0))<>C2,"",INDEX(Sheet1!E:E,MATCH(A2,Sheet1!A:A,0)))))
但是我看你的样数，好像是要反回D列的数据，如果是D列，就是在Sheet2的D2输入以下公式
=IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"",IF(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))<>B2,"",IF(INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0))<>C2,"",INDEX(Sheet1!D:D,MATCH(A2,Sheet1!A:A,0)))))

Answer 2

在E2中输入
=LOOKUP(1,0/((Sheet1!A:A=A2)*(Sheet1!B:B=B2)*(Sheet1!CC=C2)),Sheet1!E:E)
下拉填充。本回答被提问者采纳