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?

Concatenate the name and date together (into a helper column or something like that). Then you can use vlookup or index/match or whatever you want on the helper column to make sure that both the name and dates match. — Stepan1010, Sep 12 '13 at 16:11

barry houdini · Answer 1 · 2013-09-12T15:06:52.987

13

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......

edited Sep 12 '13 at 15:06

answered Sep 12 '13 at 14:57

barry houdini

45,615
8
63
81

This seems like a correct solution, but after fighting for hours with this, I finally gave up (excel crashing all the time, maybe because of the amount of rows it needs to index?). – davejal Apr 25 '16 at 12:02

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

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'.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 12 '13 at 14:53

pnuts

58,317
11
87
139

after fighting with the solution by @barry houdini, I finally gave up and used a helper column as you suggested. – davejal Apr 25 '16 at 12:01

score 2 · Answer 3 · edited Aug 30 '17 at 14:08

Here is the solution without using an array and without using a helper 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 performs a grid lookup:

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

Lookup using INDEX and MATCH with two criteria

3 Answers3

Linked