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

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:
[tt]
[highlight #E9B96E]=INDEX(Return_Range, SUMPRODUCT((Lookup_Range1=Criteria1)*(Lookup_Range2=Criteria2)*(ROW(Return_Range)-MIN(ROW(Return_Range))+1)))[/highlight]
[/tt]
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)
[pre]
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
[/pre]

And here are my Lookup Values starting in G2702 seeking to return the WORK_CENTER:
[pre]
PART ORDER_NO OPER
widget 001000000841 0040
bibit 001000208851 0030
[/pre]
And here is the formula in J2702
[tt]
J2701: =INDEX([highlight #FCAF3E]WORK_CNTR[/highlight], SUMPRODUCT(([highlight #8AE234]PART[/highlight]=G2702)*([highlight #729FCF]ORDER_NO[/highlight]=H2702)*([highlight #AD7FA8]OPER[/highlight]=I2702)*(ROW([highlight #FCAF3E]WORK_CNTR[/highlight])-MIN(ROW([highlight #FCAF3E]WORK_CNTR[/highlight]))+1)))
[/tt]
And my results:
[pre]
PART ORDER_NO OPER WORK_CNTR
widget 001000000841 0040 TGH16613
bibit 001000208851 0030 DEW16476

[/pre]


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:
[tt]
[highlight #E9B96E]=INDEX(Return_Range, SUMPRODUCT((Lookup_Range1=Criteria1)*(Lookup_Range2=Criteria2)*(ROW(Return_Range)-1))[/highlight]
[/tt]
Happy lookups!