How to use Multi Criteria in Vlookup using INDEX and Match

In Excel, we use Vlookup for pulling data from a table with single criteria but it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck.

To Achieve multiple criteria in a vlookup, Excel has a pair of functions called INDEX and MATCH

The syntax for INDEX and MATCH as follow:

=INDEX(array, row_num, [col_num])

The array is the table of data that contains the cell value you want.

The row_num is the relative row number of the cell you want.

The col_num is the relative column number of the cell you want.

=MATCH(lookup_value, lookup_array, [match_type])

The lookup_value is what you are searching for.

The lookup_array is the array of values you are trying to find the lookup_value in.

The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.

Using INDEX and MATCH with one Criteria

The syntax as follow:

=INDEX(array,MATCH(lookup_value, lookup_array, [match_type]) , [col_num])
=INDEX(C4:H1159,MATCH(A2,C4:C1159,0),6)

Using INDEX and MATCH with Two Criteria

The syntax as follow:

=INDEX(array,MATCH(lookup_value, (lookup_array)*(lookup_array), [match_type]) , [col_num])
=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

When you enter the formula, don’t just press ENTER. Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. You can tell you’ve done it right because the entered formula will be surrounded in curly braces {}.