Search code examples
excelexcel-formulaexcel-2010lookupexcel-match

Lookup using INDEX and MATCH with two criteria

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?

Solution

  • I suggest the conventional solution to problems of this kind is to concatenate the pair of search terms (ie a helper column) and to add the concatenated pairs to the lookup array.

    SO18767439 example

    In the example above the concatenation of what to look up (rather than where to look up) is done 'on the fly'.