Excel table lookup matching values of two columns

Question

I'd like to create a table lookup formula that matches two columns. For instance, suppose I'd like to find the value of the Letter column at the row where the Type column is Biennial and the Result column is Warning.

  A               B            C
1 Letter          Type         Result
2 A               Annual       Exceeds
3 B               Biennial     Warning
4 C               Biennial     DevelopmentNeeded
5 D               Biennial     PartiallyMeets
6 E               Annual       Meets

What would the formula look like to accomplish this?

Answer 1

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.

Answer 2

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.