excel - Lookup using INDEX and MATCH with two criteria

Question

I am trying to achieve a basic lookup using INDEX and MATCH. My layout is:

Sheet 1
NAME | SITE | DATE

Sheet 2
NAME | SITE | DATE

I want the 'SITE' column in Sheet 1 to automatically populate with the SITE from Sheet 2 where NAME and DATE match.

What I've Tried

=INDEX('Sheet2'!B:B,MATCH(A1,'Sheet2'!A:A,0))

This will successfully match NAME, but how can I incorporate an additional MATCH into the formula to match on both NAME and DATE?

Answer 1

You can use an "array formula" like this

=INDEX('Sheet2'!B:B,MATCH(1,(A1='Sheet2'!A:A)*(C1='Sheet2'!C:C),0))

CTRL+SHIFT+ENTER

....or you can add another INDEX function so that it doesn't need to be "array entered", i.e.

=INDEX('Sheet2'!B:B,MATCH(1,INDEX((A1='Sheet2'!A:A)*(C1='Sheet2'!C:C),0),0))

or another way is to use LOOKUP like this

=LOOKUP(2,1/(A1='Sheet2'!A:A)/(C1='Sheet2'!C:C),'Sheet2'!B:B)

That latter method would give you the last match if there is more than one......