Excel INDEX and MATCH Functions with Multiple Criteria (4 Formulas)

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

Objective:

It returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Generic Formula:

=INDEX(array, row_num,[column_num])

Arguments Description:

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

Objective:

It returns the relative position of an item in an array that matches a specified value in a specified order.

Generic Formula:

=MATCH(lookup_value,lookup_array,[match_type])

Argument Description:

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.

Excel Index Match multiple criteria: Dataset

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.

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

Result of nested INDEX and MATCH formula

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:

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

Result of using 2 INDEX with MATCH nested formula

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.

Modified dataset for INDEX MATCH multiple criteria

The formula:

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

Matched and Index with multiple criteria to get result from modified dataset

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.

Selecting Dataset to form table

  • Choose Table from the Insert tab.

Finding Table from the Insert tab

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

Checking the table range and ticking for including headers to table

Your table will look like below.

Converted dataset to Excel table

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

Showing three criteria to apply FILTER formula

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

Result of applying FILTER formula

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.


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 query you can write in the comment section.


Related Articles

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo