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