We often find circumstances where multiple lookup criteria are required to return a single value.
The VLOOKUP() function is usually the go-to approch, but with multiple lookup values, 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 2701 (for no particular reason other than it is other than row 1)
And here are my Lookup Values starting in G2702 seeking to return the WORK_CENTER:
PART ORDER_NO OPER
widget 001000000841 0040
bibit 001000208851 0030
And here is the formula in J2702
J2701: =INDEX(WORK_CNTR, SUMPRODUCT((PART=G2702)*(ORDER_NO=H2702)*(OPER=I2702)*(ROW(WORK_CNTR)-MIN(ROW(WORK_CNTR))+1)))
BTW, this can only be used where there is ONE and ONLY ONE row for the combination of criteria[/b] (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))
Talk To Other Members