Index Match with Multiple Matches in Excel (5 Methods)

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.

Practice workbook

In order to making things easier we’ve shared the workbook, you are welcome to download it from the link below

Basics of INDEX-MATCH

Basics of INDEX

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 array from which to return a value

column_number: The column in 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

Practically, one function you will find more often with INDEX function is MATCH function. 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. 1 = Smaller or equal to lookup_value
2. 0 = Exact lookup_value
3. -1 = Greater or equal to lookup_value

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

INDEX-MATCH with Multiple matches

1. INDEX MATCH with multiple criteria

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 as you are matching these two criteria our formula will be something like:

Write the formula in Excel. And instead of pressing Enter use CTRL+SHIFT+ENTER (with this, you are commanding Excel to compare all the values in the search column)

We got the result. Price of a small sized shirt from our table has been fetched successfully.

Do you know why we’ve used 1 as lookup_value within the MATCH function? Let me clear the concept for you.

If you look at the formula you will see that we have checked the target product name F3 within A4:A14 and target size F4 within B4:B14 and multiplied the result. The result is whether TRUE or FALSE.

IF both the searches give TRUE then their product will be TRUE. 1 represents TRUE. We are checking for that 1 within this search.

You can use the IFERROR function to eradicate any error that occurs because of any missing or misinterpreted value.

For the time being, we’ve left it blank for any error. You write your suitable output.

You can find different values by changing the input.

Here we’ve changed the Size to L and it provided the Price of L size shirt.

You can change both the input like the image above.

2. INDEX MATCH with Multiple Criteria Belongs to Row and Column

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

Read more: Index Match Multiple Criteria in Rows and Columns in Excel

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

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

Our generic formula will be:

Let’s write the formula in Excel.

When you compare the formula, written in this example, to the generic one you’ll find:

array: B4:E6, as your price values stored within this range

column search value: H3, in this cell we have stored the value to be searched in the column

lookup_column range: A4:A6, our column search value will be searched within this range

row search value: H4, in this cell we have stored the value to be searched in the row

lookup_row range: B3:E3, row search value will be searched within this range.

Since at H3 and H4 cells we’ve set Shirt and L respectively, it gave us the L size shirt’s price 21. It will change dynamically. For example, if we change M instead of L, the price will be the same as an M size shirt.

You can change both the criteria values in order to get your result like the image below.

3. INDEX MATCH from Non-Adjacent Columns

In this section we will show you an example of how to fetch matching value using two non-adjacent columns.

In our example we will see how to retrieve the amount of a product. You are already familiar with the formula we are going to use here,

Write the formula in the Excel.

We have got the Amount of product Shirts. You can change the product name according to your preference, like Hoodie set at the place of Shirt in the image below.

4. INDEX MATCH from Multiple Tables

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

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

Our formula will be

You will find the formula from the downloaded workbook as well.

While writing in the Excel practice inserting formula within IFERROR

Here the SMALL function returns the smallest value, ISNUMBER checks whether the value is a number or not.

If you have a closer look, you will find there are two INDEX(SMALL(IF(ISNUMBER(MATCH())))) has been used to pull out the values from both the tables.

Simplifying the formula for you

row_number has been derived within SMALL. Within the SMALL function a IF statement is triggered. Inside the IF function we checked whether the MATCH returns a number or not using the ISNUMBER function.

The result from the ISNUMBER function is the value for our logic check within the IF statement. We created our statement such that for TRUE value it will compare the MATCH function lookup_value, provided by using the ROW function.

To provide the column_value we’ve used the ROWS function that returns the number of rows in an array.

Similar calculation performed within another INDEX function.

Using Excel AutoFill, you can find the quantity of shirts (for example) from both the tables.

5. INDEX MATCH from Multiple Worksheets

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

Read more: INDEX MATCH across Multiple Sheets in Excel (With Alternative)

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.

Syntax for adding sheet name is

Now adding the sheet name our generic formula will be similar to the previous section. Check it out below:

Since this formula is familiar to you, we’ve highlighted the changes by making differences Bold and Italic. You will find the formula from the downloaded workbook as well.

No need to provide the sheet name where you are calculating, just provide the other sheet’s name. We were calculating at the Shop 1 sheet, so provided Shop 2 ahead of the references of that sheet and got the answer.

Conclusion

That’s all for today. We have tried showing you a couple of ways to INDEX MATCH with multiple matches criteria. 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 to the task.

Further Readings:

• How to Use INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)
• How to Use INDEX and Match for Partial Match (2 Ways)
• Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function
• Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
• INDEX MATCH Multiple Criteria in Excel (Without Array Formula)
• How to use INDEX & MATCH worksheet functions in Excel VBA