Excel datasets are used to keep records of information. Searching for information from large datasets can be time-consuming. Excel has some useful formulas to search and match queries to get accurate results. INDEX and MATCH are some of the most used ones that work for both single and multiple criteria. The article will explain 3 formulas to use INDEX-MATCH with multiple criteria in Excel provided with proper explanations.
Before going to the next part, let’s have a quick look to the methods we will be using here and the relevant output.
How to Use INDEX MATCH with Multiple Criteria in Excel: 3 Ways
The INDEX function returns a value or reference of the cell at the intersection of a particular row and column in a given range.
The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.
We will use the following dataset to explain 3 formulas to Excel Index and Match them with multiple criteria.
The dataset contains 5 columns with Product ID, Color, Size, and Price list of the products of a company. Now if you have multiple criteria and you want to match multiple criteria to get a value related to the matched value. The following sections of the article will show 3 different formulas with the INDEX and MATCH functions with multiple criteria. So, let us move forward.
1. Nested Formula Using INDEX and MATCH Functions
Let us assume that we have to find out the price of a product from the dataset by matching the product ID, color and size.
You can use the following formula using Excel INDEX and MATCH function to get the result:
=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))
Here you can see the formula matches the multiple criteria from the dataset and then show the exact result.
🔎 Formula Breakdown
- Using the MATCH function the 3 criteria: Product ID, Color, and Size are matched with ranges B5:B11, C5:C11, and D5:D11 respectively from the dataset. Here the match type is 0 which gives an exact match.
- Lastly, using the INDEX function it gets the price of that particular product from the range E5:E11.
Read More: INDEX-MATCH with Multiple Matches in Excel
2. Nested Formula with Two INDEX Functions and MATCH Function
Furthermore, there is another formula that includes two INDEX functions along with a MATCH function with multiple criteria to get a value from a given range of data. You can also use the INDEX MATCH functions with multiple criteria to get multiple results in Excel.
The formula is:
=INDEX(E5:E12,MATCH(B15&C15&D15,INDEX(B5:B12&C5:C12&D5:D12,),0))
The result matches the 3 criteria with the given data ranges and gives the result of matched criteria value in the range specified for the output.
🔎 Formula Breakdown
- MATCH function takes lookup values as B15, C15, and D15 using AND in between them.
- Next, it takes the INDEX function within which the lookup arrays for each of the lookup values are B5:B12, C5:C12, and D5:D12.
- The last argument of the MATCH function is 0 to give the exact match.
All these are nested inside another INDEX function whose first argument is the range from where the result will be finally shown.
Read More: How to Use INDEX-MATCH Function for Multiple Results in Excel
3. Using INDEX with Two MATCH Functions with Multiple Criteria
However, the opposite of the above method is that the formula with 2 MATCH functions nested with an INDEX function can also do the work.
Now, let us say we have a modified version of the given dataset including information about the Hoodie and T-shirt and arranged in the following way.
The formula:
=INDEX(C6:F7,MATCH(I4,B6:B7,0),MATCH(I5&I6,C4:F4&C5:F5,0))
In this case, we have used two MATCH functions to match values from the dataset. One match for the row and the other for the column. Both the MATCH formula is nested inside an INDEX function which works perfectly.
🔎 Formula Breakdown
- The first MATCH formula matches the product name T-Shirt will the values in the row (B6 and B7).
- The secondMATCH formula takes two criteria color and size (Blue and Medium) with the range C4:F4 and C5:F5 respectively.
- Both the MATCH formula is nested inside the INDEX formula as the second argument. The first argument of the INDEX formula takes the first argument as the range of data from which output will be extracted and the third is 0 for an exact match.
Read More: INDEX MATCH Formula with Multiple Criteria in Different Sheet
Alternative to INDEX-MATCH: FILTER Function
Moreover, if you are using Microsoft 365 which has dynamic arrays then you can use the FILTER function with multiple criteria as an alternative to the INDEX-MATCH formulas.
Follow the steps to know how to apply the FILTER function for this purpose:
- Select the whole dataset.
- Choose Table from the Insert tab.
- Check the range of the table and tick My table has headers.
- Then click OK.
Your table will look like below.
Now suppose you have the 3 criteria(shown in the picture) using which you have to find the price of that particular product.
- Write the formula in the cell where you want to see the result:
=FILTER(Table2[[Price ]],(Table2[Product ID]=B15)*(Table2[Color]=C15)*(Table2[Size]=D15))
The result will be shown in the cell.
🔎 Formula Breakdown
- The formula takes 3 arguments,
- The first argument is an array which is the range of data from which the return value will be extracted.
- The second argument is include which includes the criteria. In our case, the criteria are Product ID, Color, and Size.
- The third argument is empty_if which takes a return value if the result is empty. This one is optional and we do not require this in our case.
- It matches the criteria and provides the result from the range in the first argument.
Things to Remember
- You can press CTRL+SHIFT+ENTER from the keyboard by keeping the cursor at the end of the formulas, including arrays. Though it works fine by simply pressing ENTER, you can use this technique while working with arrays to be safe.
- The FILTER function is only available for Microsoft 365 with a dynamic array feature. If you do not have this version and use an older version go for the other 3 formulas.
Download Practice Workbook
Download the practice workbook and practice yourself.
Conclusion
The article contains a brief description of INDEX and MATCH functions. Afterward, it used a dataset to apply 4 different formulas using INDEX, MATCH, and FILTER functions with multiple criteria in Excel. I hope the article was helpful to you. If you want to explore more, you can check out the related articles below. If you have any queries you can write in the comment section. See you!
Use INDEX MATCH with Multiple Criteria in Excel: Knowledge Hub
<< Go Back to INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you for these formula examples you provided in this article, Syeda. The INDEX MATCH INDEX example you showed really helped me figure out the formula I was looking for StackOverflow.
Thank you for your compliment, David!
Please click on the following link for more examples of the INDEX-MATCH combo:
https://www.exceldemy.com/tag/index-match-excel/