VLOOKUP with two or more criteria

Vlook is a very powerful tool to search data but it has limitation of searching data in single Column.  A search similar to vlookup can be performed using INDEX and MATCH.

How INDEX and MATCH work

First let’s see how INDEX and MATCH functions work

INDEX

Syntax of INDEX function

INDEX(array, row num, column num)

Array is array where is data is searched for. Row number and Column number is number of column and row respectively.

INDEX function returns array if column or row is set to zero in that case press CTRL+SHIFT+ENTER to enter formula.

MATCH

Syntax of MATCH function

MATCH(lookup_value, lookup_array, [match_type])

Lookup value is searched value in lookup array you want to match. Match type is optional with arguments -1,0 and 1.

How to use INDEX and MATCH for VLOOKUP with multiple criteria

Formula we will use here is

=INDEX(array,MATCH(1,(lookup_table=data1)*(lookup_table2=data2),0),column num)

Here we have data of date wise sale of fuel from four fuel pumps and we want to search for sale of fuel from particular pump on a particular date. Formula used here is

{=INDEX($A$2:$C$25,MATCH(1,($A$2:$A$25=F2)*($B$2:$B$25=G2),0),3)}

Since this is formula you have to press Ctrl+Shift+Enter  to  enter the formula once done this formula will be enclosed by curly braces ({}).

Now here MATCH will look in range $A$2:$A25 for value F2 and return A2, now next criteria will look $B2:$B25 for value G2 and return B2. Now it will return array with location where both criteria match it will return array with 1 where both criteria matches. If both matches are not found then match will return empty array.