Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use INDEX MATCH with Multiple Criteria in Excel (3 Ways)

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.

Excel INDEX MATCH Multiple Criteria Overview


Download Practice Workbook

Download the practice workbook and practice yourself.


3 Formulas Using INDEX and MATCH Functions with Multiple Criteria in Excel

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.

Dataset Overview

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

Excel INDEX MATCH Multiple Criteria

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 3 Criteria in Excel (4 Examples)


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.

The formula is:

=INDEX(E5:E12,MATCH(B15&C15&D15,INDEX(B5:B12&C5:C12&D5:D12,),0))

Excel INDEX MATCH Multiple Criteria

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: INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)


Similar Readings


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.

Using One INDEX and 2 MATCH Functions in Excel

The formula:

=INDEX(C6:F7,MATCH(I4,B6:B7,0),MATCH(I5&I6,C4:F4&C5:F5,0))

Excel INDEX MATCH Multiple Criteria

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 Multiple Criteria in Rows and Columns in Excel


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.

Form Table

  • Check the range of the table and tick My table has headers.
  • Then click OK.

Pop up: Create Table

Your table will look like below.

Table Format

Now suppose you have the 3 criteria(shown in the picture) using which you have to find the price of that particular product.

Multiple Criteria

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

Excel INDEX MATCH Multiple Criteria

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 range header (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 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.

Read More: Excel INDEX MATCH to Return Multiple Values in One Cell


Things to Remember

  1. 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.
  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.

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!


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

2 Comments
  1. 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 here: https://stackoverflow.com/a/72370280/3390898

  2. 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/

Leave a reply

ExcelDemy
Logo