Showing posts from 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 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 va…