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?

Solution 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.

Solution 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.

Solution 3

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.

Comments

[Jeff Axelrod]

Do you know of a way to do this without array formulas?

[Joseph]

@JeffAxelrod Adjusted the formula. Just curious, why do you not want to use array formulas?

[Jeff Axelrod]

Because I believe there's no easy way to copy and paste array formulas with relative references.

[Joseph]

@JeffAxelrod I see. Well, if you enter the array formula in one cell, then copy it vertically/horizontally, it will adjust the relative references. I tried it with both the fill selector and plain old copy/paste (copying a cell and not just the formula text)

[Cool Blue]

Wow! I've always used ALT-ENTER for controlling the format of literal content but I didn't realise that it would work in a formula! That is unbelievably useful information: Thanks!

[oscarius]

The only unfortunate thing in Excel is that you need to use copious numbers of spaces to do the indentation :-(

[Cool Blue]

Yes, no ALT-TAB! But, anyway... I'm still better off.