Posts

Showing posts from May, 2015

When you need two VLOOKUPS together

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