Go Back

How to Use INDEX and MATCH with Multiple Criteria in Excel

There are several lookup functions in Excel, but not all of them will allow you to use multiple criteria. Here is how you can use the INDEX and MATCH functions to lookup values in Excel with more than one criteria. 

How to use INDEX and MATCH with multiple criteria

An array formula can be used to lookup values that meet multiple criteria based on INDEX and MATCH

Formula using INDEX and MATCH

Generic formula syntax to lookup values with INDEX and MATCH with multiple criteria is:

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

Where,

  • Range1 is the range of cells to lookup for values that meet multiple criteria
  • Criteria1,2,3 are cell references to test multiple criteria
  • Range2,3,4 are ranges on which each criterion is tested on.

Explanation of formula

Generally, INDEX and MATCH formula has a MATCH set configured in it. This MATCH set helps to look through a one-column range and provides a match that is based on the supplied criteria. For you to supply more than one criteria, you need to use the method of concatenation in a helper column.

This formula uses Boolean logic to create an array of ones and zeros. These are used to represent all rows that match all the three criteria. Then you use the MATCH function to match the first 1 found.

Example

In this example, we want to use the INDEX and MATCH formula to find values in the price column. We shall supply the INDEX and MATCH formula so that it can look through the price column and match a certain cell with the supplied criteria. In this case, we want to look up for a value of shirt, whose size is small, and red in color, then find its price.

Figure 1: Use INDEX and MATCH to find values

Note that the formula uses Boolean logic to produce an array of ones and zeros to represent all the rows that match the criteria supplied. As we have already mentioned, you need to use an array formula to lookup values with the INDEX and MATCH formula.

In our example above, we put the formula in cell G5 which will be as follows:

Figure 2: Using INDEX and MATCH with multiple criteria

=INDEX($D$2:$D$8, MATCH(1, (G2=$A$2:$A$8)*(G3=$B$2:$B$8)*(G4=$C$2:$C$8),0))

The answer will be as shown in the figure below:

Figure 3 Use INDEX and MATCH to find values

In our working above, the initial results will only consist of {TRUE;FALSE}. But with the use of the multiplication, this is transformed into 0s and 1s.

Non-array version

There is a non-array version of above formula using INDEX and MATCH with multiple criteria. Simply, you need to add another INDEX function to the formula with zero row and one column. This second INDEX function handles the array natively, generated by boolean logic. It returns again the same array to MATCH function because zero trick forces second INDEX to return column 1 from the array as given below.

=INDEX($D$2:$D$8,MATCH(1,INDEX((G2=$A$2:$A$8)*(G3=$B$2:$B$8)*(G4=$C$2:$C$8),0,1),0))

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Index/Match formula with multiple criteria
Solved by T. S. in 27 mins
INDEX MATCH Multiple criteria and output horizontally
Solved by E. F. in 24 mins
I need help with an INDEX(MATCH()) using multiple criteria
Solved by M. E. in 20 mins

Leave a Comment

avatar