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:
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!