INDEX and MATCH with multiple 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

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

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))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar