7 Easy Steps to Master INDEX MATCH with Multiple Criteria

Index Match is a perfect formula if you wish to look up values in Excel. It searches the row position of a value/text in one column (using the MATCH function) and returns the value/text in the same row position from another column to the left or right (using the INDEX function).

One of the advantages of using Index Match is that you can search for a value that matches multiple criteria. Index Match with multiple criteria enables users to rapidly and effectively search and extract specific data from huge and complicated datasets by employing several criteria, such as matching values in distinct columns.

The capacity to search for and extract data based on a variety of criteria is crucial in applications such as financial modeling, data analysis, and other fields.

 

Formula Syntax

The general syntax for the Index Match function is –

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type])

What it means:
=INDEX(return the value/text, MATCH(from the row position of this value/text))

It can also be used when the result column is on the left side of the array. This is not possible when you are using VLOOKUP or HLOOKUP functions.

Index Match can be used if you have multiple criteria that you need to check in order to get the resultant value. Let’s understand this in a detailed step-by-step tutorial below.

• Array Function
• Non-Array Function

 

Download the Excel workbook to practice this tutorial on how to use Index Match with multiple criteria and follow along:

 

Array Function

The general syntax for Index Match with multiple criteria is –

=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2)*(criteria3=range3),0))

• return_range – It is the range that contains the lookup value
• criteria1, criteria2, and criteria3 are the conditions that need to be met
• range1, range2, and range3 are ranges on which the corresponding criteria should be tested

This is an array formula so you must hit Ctrl + Shift +Enter for the formula to work!

 

The test array will return TRUE or FALSE as a result where TRUE indicates that the condition has been met and similarly FALSE means the condition has not been met. The multiplication operator will convert the TRUE and FALSE to 1s and 0s. The row matching both criteria will return the value as “1”.

So, when a criteria is met, the resultant block in the formula would get converted to 1. As we are multiplying all the results,  the row matching both the criteria will return the value as “1”. Even if 1 criteria is not met, the entire value will become 0 or FALSE.

The MATCH function will return the position of the value 1 and the Index function will provide us with the resultant value.

Let’s look at an example to help us understand better.

 

In the example below, we want to match two criteria – Sales Representative and Region and use Index Match to provide the corresponding sales amount matching the criteria.

STEP 1: Enter the INDEX formula

=INDEX(

STEP 2: Enter the first argument of the INDEX formula – array. This is the array that contains the lookup value. Here, it is the column containing the sales amount.

=INDEX(C3:C22,

STEP 3: Enter the MATCH function

=INDEX(C3:C22,MATCH(

STEP 4: Enter the first argument of the MATCH function – lookup_value. Here, it is the value “1”.

=INDEX(C3:C22,MATCH(1,

STEP 5: For the second argument i.e. lookup_array -we enter our criteria. Here we are searching on the basis of two criteria – Sales Representative name and Region. So you need to enter the two tests –

• If the Sales Representative (A3:A22) is Kanye (F2)
• If the Region (B3:B22) is East (F3)

You need to multiply the values of these two tests.

=INDEX(C3:C22,MATCH(1,(F2=A3:A22)*(F3=B3:B22)

STEP 6: Enter 0 for an exact match.

The match_type argument specifies how Excel matches lookup_value with values in lookup_array.

• The default value for this argument is 1. MATCH finds the largest value that is less than or equal to lookup_value.
• If we want an exact match, we enter 0.
• The last option here is -1. MATCH finds the smallest value that is greater than or equal tolookup_value)

=INDEX(C3:C22,MATCH(1,(F2=A3:A22)*(F3=B3:B22),0))

STEP 7: Press Ctrl + Shift + Enter.

This is crucial for our array function to work.

{=INDEX(C3:C22,MATCH(1,(F2=A3:A22)*(F3=B3:B22),0))}

 

You can highlight the test array and press F9 to see that the function gets converted to TRUE and FALSE –

=INDEX(C3:C22,MATCH(1,({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})*({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}),0))

Once multiplied, the expression gets converted to 0s and 1s.As we were multiplying, only the row that had fulfilled both criteria got converted into 1 or TRUE-
=INDEX(C3:C22,MATCH(1,{0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0},0))

The match function will now provide the relative position of the row for which all the criteria are TRUE. In this example, it is the 7th position.

=INDEX(C3:C22,7)

The Index function will provide the 7th value from the range C3:C22. The resultant value will be –

=48168

 

Non-Array Function

For an array function to work, you need to make sure that you press Ctrl + Shift + Enter together. If you simply press enter, the formula will break. An array function can be a little tricky to use, so you can add another INDEX function to catch the array function. To do this, INDEX is set up with one column and zero rows.

The three arguments of the 2nd Index function will be –

• array – the 2 tests i.e., If the Sales Representative (A3:A22) is Kanye (F2) and if the Region (B3:B22) is East (F3)
  (F2=A3:A22)*(F3=B3:B22)
• row_number – It will be 0, this will cause the index function to return the column specified.
• col_number – It will be 1, as the resultant array will only be 1 column.

=INDEX((F2=A3:A22)*(F3=B3:B22),0,1)

The final formula will be –

=INDEX(C3:C22,MATCH(1,=INDEX((F2=A3:A22)*(F3=B3:B22),0,1),0))

Even though this new formula is more complicated, it will surely work without having to press Ctrl + Shift + Enter. This formula can come in handy as people can forget to press Ctrl + Shift + Enter, causing our earlier formula to break.

 

Conclusion

You can either use an array function and make sure to press Ctrl + Shift + Enter for the function to work. Or, you could simply add another Index function if you wish to use a non-array function.

Click here to learn more about Index Match and other lookup functions in Excel.