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

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.

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.

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

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.

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.

• Finally, press Enter.

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.

• After that, press Enter.

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

• INDEX MATCH with 3 Criteria in Excel (4 Examples)
• INDEX MATCH across Multiple Sheets in Excel (With Alternative)
• Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
• How to Sum Multiple Rows Using INDEX MATCH Formula
• INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)

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.

• Next, press Enter on your keyboard.

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.

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.

• Then press Enter.

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

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

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.

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

• Then press Enter.

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

• Finally, press Enter.

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.

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

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

• How to Use INDEX MATCH with Excel VBA
• Excel INDEX MATCH to Return Multiple Values in One Cell
• INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
• How to Select Specific Data in Excel (6 Easy Methods)
• Use INDEX MATCH Instead of VLOOKUP in Excel
• INDEX-MATCH Formula to Generate Multiple Results in Excel
• How to Use IF with INDEX & MATCH Functions in Excel (3 Ways)