Excel datasets are used to keep records of information. Searching 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 not only work for single criteria but also for multiple criteria. The article will explain 4 formulas to INDEX and MATCH with multiple criteria with suitable examples and proper explanations.
Download Practice Workbook
Download the practice workbook and practice yourself.
Introduction to INDEX and MATCH Functions
The INDEX Function
It returns a value or reference of the cell at the intersection of a particular row and column in a given range.
array= the range of the data.
row_num= the row number of the value to return.
column_num=the column number of the value to return.
The MATCH Function
It returns the relative position of an item in an array that matches a specified value in a specified order.
lookup_value = the searched value.
lookup_array = the range of data where the searched value exists.
match_type= -0, -1,1. 0 stands for an exact match, -1 for a value greater than the exact match, and 1 for the value less than the exact match.
3 Excel Formulas Using INDEX and MATCH Functions with Multiple Criteria
We will use the following dataset to explain 4 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 Excel Formula Using INDEX and MATCH Functions with Multiple Criteria
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.
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.
2. Nested Excel Formula with Two INDEX Functions and a MATCH Function with Multiple Criteria
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.
The formula is:
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.
3. Formula Using INDEX with Two MATCH Functions with Multiple Criteria in Excel
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.
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.
Alternative to the INDEX-MATCH: Use of 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.
Note: Select the range accordingly and it will show as the table name (Table2 in this case) including the header of the range (Price, Product ID, Color, and Size for ranges accordingly) in the formula since the dataset is converted into the Excel table.
🔎 Formula Breakdown:
- The formula takes 3 arguments,
- The first argument is 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
1. You can press CTRL+SHIFT+ENTER from the keyboard by keeping the cursor at the end of the formulas which include arrays. Though it works fine by simply pressing Enter, but to be safe you can use this technique while working with arrays.
2. 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.
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 query you can write in the comment section.
- INDEX MATCH Multiple Criteria in Excel (Without Array Formula)
- Excel Index Match single/multiple criteria with single/multiple results
- Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- INDEX, MATCH and MAX with Multiple Criteria in Excel
- Index Match Multiple Criteria in Rows and Columns in Excel