Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP How to Use INDEX and MATCH with Multiple Criteria in Excel

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:
Solution examples
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
Hello, I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error. =INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0)) No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question. How do I fix this error? Thanks
Solved by F. H. in 40 mins
I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.
Solved by D. D. in 11 mins
I have two columns with names and a third one with email addresses. For every name that matches, I need to copy the email address to an empty column next to the matching name. .
Solved by E. U. in 15 mins
help with INDEX(IndexArea,MATCH($M$5,MatchRows,0),MATCH($M$6,MatchColumns,0))
Solved by Z. Y. in 12 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc