VLOOKUP with multiple criteria in Excel – Easy way in 2021

VLOOKUP is a very powerful tool to search data but it has limitation of searching data in single Column.  VLOOKUP with multiple criteria in Excel can be performed using combination of INDEX and MATCH.

How INDEX and MATCH work in Excel

First let’s see how INDEX and MATCH functions work

Use of INDEX function in Excel

INDEX function performs search across a reference array and return value from intersection of row and column number.

Syntax of INDEX function

INDEX(array, row num, column num)

Array is array of data 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.

Use of MATCH function in Excel

Match function is used to search for specified text or number in range of cell and return position of item specified.

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. If Match type argument is omitted its treated as Match type 1. Match type 1 finds largest value less than or equal specified lookup value, Match type 0 find values equal to specified lookup value and Match type -1 finds smallest value larger than or equal specified lookup value.

How to use INDEX and MATCH for VLOOKUP with multiple criteria in Excel

Below Example we will use INDEX and MATCH to perform VLOOKUP with multiple criteria in Excel.

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)}
VLOOKUP with multiple criteria in Excel
VLOOKUP multiple criteria in Excel using INDEX and MATCH

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 ({}).

Details of Working of this INDEX and MATCH combination for VLOOKUP with multiple criteria in Excel

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.