22 May, 2015

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 formual, 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.