Excel table lookup matching values of two columns

The SUMPRODUCT() formula is really apt for situations where you want to lookup a value with multiple criteria. It is most convenient when wanting to look up numeric values, but it can be adjusted to look up string values as well. As a bonus, you can avoid having to use array formulas.

This particular problem can be tackled with the following formula (indentation added for legibility, which you can do in Excel formulas using ALT + ENTER):

=INDEX(       $A$2:$A$6,       SUMPRODUCT(                  ($B$2:$B$6 = "Biennial") *                  ($C$2:$C$6 = "Warning") *                  ROW($A$2:$A$6)                 ) - 1       )

First, SUMPRODUCT() is used to filter out the proper rows using ($B$2:$B$6 = "Biennial") and ($C$2:$C$6 = "Warning"); the multiplication operator * functions as an AND operator (the + operator would function as an OR operator).

Then the result is multiplied by ROW($A$2:$A$6) to find the particular row that has the combination. SUMPRODUCT() then adds everything up, which in this case gives us 3. As the result sought is actually on row 2 due to the column headings, we subtract 1. By applying the INDEX() function, we get the desired result: B.

Beware though that this is the case if and only if the combination sought is unique. If the combination sought exists more than once, this will break down.

Another method that avoids array entry is:

=INDEX($A$2:$A$6,MATCH(2,index(1/(($B$2:$B$6="Biennial")*($C$2:$C$6="Warning")),0)))

It exploits the fact that the match function ignores certain errors and that index manages arrays naturally.

You can use an array formula if you like:

=INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6="Biennial")*($C$2:$C$6="Warning"),0))

Enter in with Ctrl+Shift+Enter

If you want to do this without array formulas, one way you could do it is by creating a helper column.

Column D to have the formula:

=B2&C2

Copied down

Then the new formula could be:

=INDEX($A$2:$A$6,MATCH("BiennialWarning",$D$2:$D$6,0))

It's just a play on the text, really.