When you need two VLOOKUPS together

VLOOKUP is a quick and easy way to return a value from a bunch of rows, by matching a single value. But once you need to match two values, all of sudden things become a bit more difficult.

One solution is to concatenate the two cells in the source and lookup tables. Another is to leverage INDEX and MATCH.

=INDEX(E2:E1000,MATCH(1,(C:C=A2)*(D:D=B2),0),0)

When entering this formula, because it's an array formula, you must enter it by using CTRL-SHFT-Enter, instead of just Enter.

Here's how this works:

• The MATCH syntax is

  MATCH(lookup_value, lookup_array, [match_type])

  where match type is 0 = exact, 1 is less than, and -1 is greater than. When the match occurs, it will return the relative position of the match within the range.
• In our MATCH formula, it's looking for an exact match to 1 (lookup_value = 1).
• If the value of A2 is found in column C, a value of 1 is returned, otherwise, a 0 is returned.
• Likewise for the value of B2 in column D.
• When multiplied together, if a matching value in columns C and D on the same row match the values from A2 and B2, then a 1 is returned, else we get a 0 (lookup_array).
• The INDEX syntax is

  INDEX(array, row_num, [column_num])

•  When MATCH returns a value of the matching row, we get the value in column E for the corresponding row.

With this formula, if you needed to add a 3rd or even more cell matches, you could just extend the multiplication technique.