Search using INDEX and MATCH with two criteria

Question

I am trying to do a basic search using INDEX and MATCH. My layout:

  Sheet 1
 NAME |  SITE |  DATE

 Sheet 2
 NAME |  SITE |  DATE 

I want the SITE column in Sheet 1 to be automatically populated with the SITE from sheet 2, where NAME and DATE correspond.

What i tried

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

This will successfully match NAME, but how can I include an extra MATCH in the formula to match both NAME and DATE?

Answer 1

I suggest that the usual solution to such problems is to combine a pair of search terms (i.e. an auxiliary column) and add concatenated pairs to the search array.

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

Answer 2

You can use an "array formula" such as

=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 does not need to enter an "array", i.e.

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

or in another way - use LOOKUP, like this

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

This last method will give you a last match if there is more than one ...

Answer 3

Here is a solution without using an array and without using an auxiliary column:

 <i>=INDEX(Table[returnColumnName], MATCH(1, INDEX((Table[lookupColumn1] = "arraysAreSlow") * (Table[lookupColumn2] = "avoidWherePossible"), 0, 1), 0))</i> 

Here is a more advanced solution that does a grid search:

 <i>=INDEX(Table, MATCH(1, INDEX((Table[lookupColumn1] = "arraysAreSlow") * (Table[lookupColumn2] = "avoidWherePossible"), 0, 1), 0), MATCH("returnColumnName", Table[#Headers],0))</i>