INDEX-MATCH with Multiple Matches in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel provides different functions and ways to fetch matching values. Depending on the situation, users can choose their preferences. This tutorial is going to show you how to gain results using INDEX MATCH with multiple matches in Excel.

First things first, let’s get to know about today’s workbook.

index match with multiple matches dataset

In the sheets of today’s workbook, you will find products and their price relationship. Using this relationship we will see a few examples to fetch value with multiple criteria.

In the real world you may need to handle datasets of several relationships and set different criteria to produce the results. To keep it simple for the time being, we will retrieve the Price of a Product matching name and size.


INDEX-MATCH Functions: Overview

Basics of INDEX Function

The INDEX function returns a value or the reference to a value from within a table or range. It can be used to retrieve individual values, or any entire rows and columns. Let’s see the syntax of the INDEX function.

INDEX(array/reference, row_number, column_number,area_number)

array or reference: A cell or range of cells to look at

row_number: A row in the array from which to return a value

column_number: The column in the array from which to return a value

area_number: Selects a range in reference from which to return the intersection of row_num and column_num. This is an optional field.

While writing the formula you can choose whether to provide row_number or column_number. If you provide row_number then it’s optional to use column_number and vice versa.

You can check out the Microsoft support site for a deeper syntax breakdown.

Basics of MATCH Function

Practically, one function you will find more often with the INDEX function is the MATCH function. The MATCH function is used to locate the position of a specified item in a range of cells. It returns the relative position of a particular item in the range.

Syntax of the MATCH function is

MATCH(lookup_value, lookup_array, match_type)

lookup_value: The value to search in the lookup_array.

lookup_array: A range of cells that are being searched.

match_type: This is an optional field. You can insert 3 values.

1 = Smaller or equal to lookup_value

0 = Exact lookup_value

-1 = Greater or equal to lookup_value

For a deeper understanding, you can check out the Microsoft support site.

Now we will put these formulas and theories into action in our dataset. We have solved different scenarios using the INDEX-MATCH with multiple matches in Excel and have included them in different sections for better understanding. Follow along to see how we can apply them in different scenarios or if you prefer a specific one, you can find it in the table above.


1. Applying INDEX-MATCH with Multiple Matches in Excel

For fetching values with multiple criteria first of all set the criteria. For example, if you want to retrieve the price of a small-size shirt(in our workbook), you need to set the Product name – Shirt and Size – Small.

preparing dataset for index match with multiple matches

Now follow these steps to see how we can use the formula to find the index match with these multiple matches in Excel.

Steps:

  • First, select cell G6.
  • Then write down the following formula.

=INDEX(D5:D15,MATCH(1,(G4=B5:B15)*(G5=C5:C15),0))

🔎 Breakdown of the Formula

INDEX(D5:D15,MATCH(1,(G4=B5:B15)*(G5=C5:C15),0))

👉 (G4=B5:B15) and (G5=C5:C15) are both conditions and return either TRUE or FALSE depending on whether the conditions are true or not. Numerically, they are 1 or 0. So the multiplication returns 1 where both of them are true.

👉 MATCH(1,(G4=B5:B15)*(G5=C5:C15),0) returns the position where both the conditions are true. In this case, it is 1.

👉 INDEX(D5:D15,MATCH(1,(G4=B5:B15)*(G5=C5:C15),0)) returns the value in the position the previous portion of the formula returned.

  • Finally, press Enter.

index match with multiple matches with multiple criteria

This is how we can use the INDEX MATCH for multiple criteria or matches in Excel.

Read More: How to Match Multiple Criteria from Different Arrays in Excel


2. Using INDEX-MATCH with Multiple Criteria Belonging to Rows and Columns

In this section, we will discuss how to perform a lookup by testing two or more criteria in rows and columns. It may seem a bit tricky and complex.

We bring a bit of change in our example, our table is now arranged in such a way that Size values (Small, Large, M, XL) represent individual columns.

Similar to the previous section, set the product and required size as criteria values.

Follow these steps to see how we can use the formula for this section.

Steps:

  • First, select cell I6.
  • Then write down the following formula in it.

=INDEX(C5:F7,MATCH(I4,B5:B7,0),MATCH(I5,C4:F4,0))

🔎 Breakdown of the Formula

👉 MATCH(I4,B5:B7,0) returns the exact match of the value of I4 in the range B5:B7.

👉 Similarly, MATCH(I5,C4:F4,0) returns the exact match of the value of I5 in the range C4:F4.

👉 Finally, INDEX(C5:F7,MATCH(I4,B5:B7,0),MATCH(I5,C4:F4,0)) takes the output of the first function as row number and the second function as the column number and returns the value that is in the position from the range C5:F7.

  • After that, press Enter.

index match with multiple matches criteria belonging to rows and columns

Thus, we can use the INDEX-MATCH with multiple criteria belonging to rows and columns.

Read More: Excel INDEX MATCH with Multiple Criteria and Multiple Results


Similar Readings


3. Inserting INDEX-MATCH for Multiple Non-Adjacent Columns Matches

In this section, we will show you an example of how to fetch matching values using two non-adjacent columns. Additionally, we need the IFERROR function for this scenario.

This will be the dataset for the demonstration.

Follow these steps to see how we can use the INDEX-MATCH for the non-adjacent columns (product and amount) in this dataset.

Steps:

  • First, select cell G6.
  • Then write down the following formula in it.

=IFERROR(INDEX(B4:D7,MATCH(G5,B4:B7,0),MATCH(F6,B4:D4,0)),"No Value")

index match with multiple matches from non-adjacent columns

🔎 Breakdown of the Formula

IFERROR(INDEX(B4:D7,MATCH(G5,B4:B7,0),MATCH(F6,B4:D4,0)),”No Value”)

👉 MATCH(G5,B4:B7,0) finds the exact match of value of cell G5 in the range B4:B7.

👉 and MATCH(F6,B4:D4,0) finds the exact match of F6 is the range B4:D4.

👉 Then INDEX(B4:D7,MATCH(G5,B4:B7,0),MATCH(F6,B4:D4,0)) takes the first function value as row number and the second function value as the column number and returns the value in that position in range B4:D7.

👉 Finally, IFERROR(INDEX(B4:D7,MATCH(G5,B4:B7,0),MATCH(F6,B4:D4,0)),”No Value”) returns the string “No Value” if there is an error while executing the formula. Otherwise, it returns the usual value.

  • Next, press Enter on your keyboard.

index match with multiple matches from non-adjacent columns final result

As a result, we can find the desired match using the INDEX-MATCH from non-adjacent columns for selected criteria, even for multiple ones, in Excel.

Read More: Excel INDEX MATCH Example (14 Examples + Alternative)


4. Using INDEX-MATCH for Matches from Multiple Tables

To find the matches from multiple tables we can use the INDEX-MATCH formula. Alongside this function, we will need SMALL, ISNUMBER, ROW, COUNTIF, and IFERROR functions as well.

In the example sheet, we have 2 shop’s Products. Using this sheet, we will see how to do the task.

index match with multiple matches from multiple tables dataset

Follow these steps to see how we can use the combination of these functions along with the INDEX-MATCH with multiple matches from this set of tables in Excel.

Steps:

  • First, select cell C14.
  • Now write down the following formula.

=IFERROR(INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), ""), ROWS($A$1:A1))), INDEX($F$6:$F$10, SMALL(IF(ISNUMBER(MATCH($E$6:$E$10, $C$12, 0)), MATCH(ROW($E$6:$E$10), ROW($E$6:$E$10)), ""), ROWS($A$1:A1)-COUNTIF($B$6:$B$10, $C$12))))

index match with multiple matches

🔎 Breakdown of the Formula

IFERROR(INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1))), INDEX($F$6:$F$10, SMALL(IF(ISNUMBER(MATCH($E$6:$E$10, $C$12, 0)), MATCH(ROW($E$6:$E$10), ROW($E$6:$E$10)), “”), ROWS($A$1:A1)-COUNTIF($B$6:$B$10, $C$12))))

👉 MATCH($B$6:$B$10, $C$12, 0) finds the exact match of C12 in the range B6:B10.

👉 ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)) checks if the value is a number in the function.

👉 IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”) ROW($B$6:$B$10)) checks if the previous function is a number or not. If that is, then it returns the output value of MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)) which is the position where the array of row numbers are matched in the first and second ROW functions. Otherwise, it returns an empty string.

👉 SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1)) returns the ROWS($A$1:A1)-th smallest value from the array.

👉 Finally. INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1))) returns the value in that position in the range C6:C10.

👉 INDEX($F$6:$F$10, SMALL(IF(ISNUMBER(MATCH($E$6:$E$10, $C$12, 0)), MATCH(ROW($E$6:$E$10), ROW($E$6:$E$10)), “”), ROWS($A$1:A1)-COUNTIF($B$6:$B$10, $C$12))) does the same thing, but from the second table as the ranges are clearly different in this portion of the formula.

👉 Finally, the whole function takes the whole function and returns the INDEX-MATCH combinations. The impact of the IFERROR function is if there were errors while executing the formula it wouldn’t return any value.

  • Then press Enter.

index match with multiple matches from multiple tables

  • After that, select the cell again and click and drag the fill handle icon down for multiple cells to find the rest of the values from the tables. You can drag extra cells, Excel will stop the values when there are no more of them.

This is how we can use the INDEX-MATCH using criteria from multiple tables in Excel.

Read More: INDEX, MATCH, and COUNTIF Functions with Multiple Criteria


5. Applying INDEX-MATCH for Multiple Matches from Different Worksheets

We can use the INDEX-MATCH formula over different sheets. Here we have these two tables over two different worksheets.

index match with multiple matches first sheet

Shop 1 sheet for Shop 1 and Shop 2 sheet for shop 2.

index match with multiple matches second sheet

To produce the result all we need to do is just provide the Sheet name ahead of the Cell Reference. Follow these steps for more details.

Steps:

  • First, select cell C14 from the sheet “Shop 1”.
  • Then write down the following formula.

=IFERROR(INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), ""), ROWS($A$1:A1))), INDEX('Shop 2'!$C$6:$C$10, SMALL(IF(ISNUMBER(MATCH('Shop 2'!$B$6:$B$10, $C$12, 0)), MATCH(ROW('Shop 2'!$B$6:$B$10), ROW('Shop 2'!$B$6:$B$10)), ""), ROWS($A$1:A1)-COUNTIF($B$6:$B$10, $C$12))))

index match with multiple matches from multiple tables

🔎 Breakdown of the Formula

IFERROR(INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1))), INDEX(‘Shop 2’!$C$6:$C$10, SMALL(IF(ISNUMBER(MATCH(‘Shop 2’!$B$6:$B$10, $C$12, 0)), MATCH(ROW(‘Shop 2’!$B$6:$B$10), ROW(‘Shop 2’!$B$6:$B$10)), “”), ROWS($A$1:A1)-COUNTIF($B$6:$B$10, $C$12))))

👉 MATCH($B$6:$B$10, $C$12, 0) searches for the exact match of the value of cell C12 in the range B6:B10.

👉 ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)) checks if the output of the previous function is a number or not. Which determines if there was a match or not. This is just to convert the number value into a boolean.

👉 Then IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”) checks for the boolean value and returns MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)) if the value is true. Otherwise, it returns an empty string. The MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)) portion is the series of numbers where ROW($B$6:$B$10) and ROW($B$6:$B$10) matches. This is just a handy trick to limit the total number of rows in a selected section.

👉 After that, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1) searches for the ROWS($A$1:A1)-th smallest value from the output of the IF portion.

👉 Finally, INDEX($C$6:$C$10, SMALL(IF(ISNUMBER(MATCH($B$6:$B$10, $C$12, 0)), MATCH(ROW($B$6:$B$10), ROW($B$6:$B$10)), “”), ROWS($A$1:A1))) takes the output of the previous function as the row number and ROWS($A$1:A1) as the column number and returns the value that is in this position in the range C6:C10.

👉 Similarly, INDEX(‘Shop 2’!$C$6:$C$10, SMALL(IF(ISNUMBER(MATCH(‘Shop 2’!$B$6:$B$10, $C$12, 0)), MATCH(ROW(‘Shop 2’!$B$6:$B$10), ROW(‘Shop 2’!$B$6:$B$10)), “”) does the same but from the second sheet. As the sheet name is “Shop 2”, we have added it before selecting ranges/cells. You do not need to add them to the sheet you are doing calculations on. So we didn’t do that for “Shop 1” in the previous portion of the formula.

👉 Finally, we have added the whole function in an IFERROR function. The reason is to return a blank in case errors occur while executing the formula.

  • Finally, press Enter.

  • Now, select the cell again. Then click and drag the fill handle icon for some cells down (more than the estimated amount of output cell should be fine).

Consequently, we will find all the matches using the INDEX-MATCH from multiple worksheets in Excel.

Read More: INDEX-MATCH with Duplicate Values in Excel (3 Quick Methods)


6. Inserting INDEX-MATCH for Multiple Criteria Without Array

We can also use the INDEX-MATCH for multiple matches or criteria without any array. For example, let’s take the following dataset.

<18>

But we need a helper column to achieve that first. We will use the CONCATENATE function in addition to the functions in question. Follow these steps for the full guide.

Steps:

  • First, select cell F5 and write down the following formula.

=CONCATENATE(C5,",",D5,",",E5)

  • Then press Enter.

concatenating columns

  • Now select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

  • Next, we will find the INDEX-MATCH for all the 100s in the original dataset. For that, select a cell to store the value (H5 in this case).
  • Then insert the following formula.

=INDEX(B5:B19,MATCH("100,100,100",F5:F19,0))

🔎 Breakdown of the Formula

👉 MATCH(“100,100,100”,F5:F19,0) searches for the exact match of 100,100,100 in the range F5:F19.

👉 Then INDEX(B5:B19,MATCH(“100,100,100”,F5:F19,0)) returns the value in the position where the value matches.

  • Finally, press Enter.

index match with multiple matches without array

This way, we can use INDEX-MATCH for multiple criteria or matches in Excel without any array.

Read More: How to Use INDEX MATCH Formula in Excel (9 Examples)


How to Return Multiple Values Vertically Using INDEX-MATCH Formula in Excel

In case you want to return multiple values vertically using INDEX-MATCH, let’s look at the following example.

Follow these steps to see how we can achieve that for the dataset.

Steps:

  • First, select cell F5.
  • Second, write down the following formula.

=IFERROR(INDEX($C$5:$C$14,SMALL(IF($E$5=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),"")

🔎 Breakdown of the Formula

👉 ROW($B$5:$B$14) returns an array containing the row numbers of the range B5:B14.

👉 ROW($B$5:$B$14)-ROW($B$5)+1 returns the difference between the array and the row number of the cell B5 which is just an array of 1 to 10 in this case.

👉 IF($E$5=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1) checks where the value of cell E5 is equal in the range B5:B14 and returns the number in the array where it is true from the previous array.

👉 SMALL(IF($E$5=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1) returns the smallest number from the array.

👉 INDEX($C$5:$C$14,SMALL(IF($E$5=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))) then returns the value in that position in the range C5:C14.

👉 Finally, IFERROR(INDEX($C$5:$C$14,SMALL(IF($E$5=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),””) makes sure that if a value results in an error in the formula, it returns an empty string.

formula for returning multiple values vertically using index match

  • Third, press Enter.

  • Then select the cell again. Finally, click and drag the fill handle icon down for some cells to get all the values.

returning multiple values vertically using index match

This way we can return multiple values vertically using the INDEX-MATCH in Excel.

Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally


Download Practice Workbook

You can download the workbook used for the demonstration with all the formulas from the link below.


Conclusion

That’s all for today. We have tried showing you a couple of ways to INDEX MATCH with multiple matches. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. You are welcome to notify us of any other methods for the task.

For more guides like this, visit Exceldemy.com.


Related Articles

Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

2 Comments
  1. Return Multiple Values Vertically Using INDEX-MATCH Formula in Excel example does not work with Excel 2016. Followed example and when dragging down list cells after the initial “Elizabeth” are Blank

    • Hello, Bob!
      Thanks for your comment!
      Yes! This formula won’t work in Excel 2016. I will suggest that use Excel 365.

      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy
      .

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo