excel - How to do Multi Criteria Match Index Function

Question

I am trying to use Index/Match function to populate Column B of Sheet 1 based on the data in Sheet 2.

Sheet 1:

Below is how the sheet 2 looks like. Row 1 contains the line number. Column B and Column C belong to Line 1, and Column D and E belong to Line 2, etc.

I need to match the Project ID first. Then match the Activity Line # (Column C of sheet 1 with B1:G1 of sheet 2), then find the corresponding Activity #.

For example, on sheet one, it is asking for Activity # of Project 0000002/Activity Line 2, which should be "ES" based on the sheet 2.

So, ideally, the result should look like below

This is what I have for now, but it’s giving me a #REF! error…</p>

=IFNA(INDEX('Sheet2'!$B:$G, MATCH('Sheet1'!C2, INDEX('Sheet2'!$B$1:$G$1, MATCH('Sheet1'!A2, 'Sheet2'!$A:$A,0),0),0)),"")

标签： excelmatch

Answer 1

利用：

=IFERROR(INDEX('Sheet2'!$B:$G,MATCH(A2,'Sheet2'!$A:$A,0),MATCH(C2,'Sheet2'!$B$1:$G$1,0)),"")