How can I perform a LOOKUP with MULTIPLE CRITERIA to return a SINGLE VALUE?

Engineering Software general discussion Spreadsheets
, , ,
#1

We often find circumstances where multiple lookup criteria are required to return a single value.

The VLOOKUP() function is usually the go-to approach with many Excel users, but with multiple lookup values/criteria, it becomes more difficult.

Moreover, VLOOKUP() requires the lookup column to be to the left of the return range, and even if you used a concatenated key, you would have to modify your table in order to make it useable. This is the primary reason that I hardly ever use the VLOOKUP() function, in favor of INDEX() & MATCH() for single lookup ranges or INDEX & SUMPRODUCT()/ROW() for multiple lookup ranges as follows:

=INDEX(Return_Range, SUMPRODUCT((Lookup_Range1=Criteria1)*(Lookup_Range2=Criteria2)*(ROW(Return_Range)-MIN(ROW(Return_Range))+1)))

Of course, you can have as many Lookup/Criteria as you need.

Here is an example of a lookup with 3 criteria:

Source Table, using Named Ranges based on Names in the TOP ROW, and the table headings in ROW 2 (for no particular reason other than it is other than row 1)

PART   ORDER_NO     OPER WORK_CNTR
widget 001000000841 0020 AZX00007
widget 001000000841 0030 WSD00WSD
widget 001000000841 0040 TGH16613
widget 001000000841 0050 GBN171F2
bibit  001000208851 0020 AZX00007
bibit  001000208851 0030 DEW16476
bibit  001000208851 0040 SAQ00081
bibit  001000208851 0050 WSD00WSD

(BTW, please notice that although ORDER_NO & OPER in this example, contain all numeric characters, they are not NUMBERS, rather IDENTIFIERS, and should be treated as TEXT. See…When is a NUMBER not a NUMBER)

And here are my Lookup Values starting in G2 seeking to return the WORK_CENTER:

PART   ORDER_NO     OPER
widget 001000000841 0040
bibit  001000208851 0030

And here is the formula in J2

J2: =INDEX(WORK_CNTR, SUMPRODUCT ( (PART=G2)* (ORDER_NO=H2)* (OPER=I2)* (ROW(WORK_CNTR)-MIN(ROW(WORK_CNTR))+1)) )

And my results:

PART   ORDER_NO     OPER WORK_CNTR
widget 001000000841 0040 TGH16613
bibit  001000208851 0030 DEW16476

BTW, this can only be used where there is ONE and ONLY ONE row for the combination of criteria values (use the PivotTable wizard to verify that this is the case if you are unsure). Otherwise the multiple ROW() values get summed, resulting in an incorrect result!

If you ALWAYS have your table headings in ROW 1, then the formula can be simplified as:

=INDEX(Return_Range, SUMPRODUCT( (Lookup_Range1=Criteria1)* (Lookup_Range2=Criteria2)* (ROW(Return_Range)-1) )

Happy multi-criteria lookups!

1 Like
#2

Thank you Skip. I didn’t know this is possible.

Is there any chance you can attach an example file so that I can see it in action?

#3

SE-MultiLookup.xlsx (12.2 KB)

Hope this example helps.

1 Like
#4

Thank you. I think this will be very handy. I stopped using VLOOKUP ages ago, because I often reformat calculations by dragging stuff around and it messes the VLOOKUP commands up, INDEX/MATCH is much more robust.