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.

Workbook-Index Match with Multiple Matches

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.

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

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

Example - Index Match with Multiple Matches

Now as you are matching these two criteria our formula will be something like:

INDEX(Range to lookat, MATCH(1, (First Criteria Value= It’s range)*(Second Criteria Value = It’s range),0))

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)

Formula - Index Match with Multiple Matches

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.

IFERROR

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.

Change value

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

Change values

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.

ROw-column-Index Match with Multiple Matches

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

Our generic formula will be:

INDEX(array,MATCH(column search value, lookup_column range, 0),MATCH(row search value, lookup_row range,0))

Let’s write the formula in Excel.

Formula result

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.

Change value-row col

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

Values -row column-Index Match with Multiple Matches

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.

Non adjacent - Index Match with Multiple Matches

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,

INDEX(array,MATCH(column search value, lookup_column range, 0),MATCH(row search value, lookup_row range,0))

Write the formula in the Excel.

Formula Result

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.

Value change - Index Match with Multiple Matches

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.

Two tables

Our formula will be

IFERROR(INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(MATCH($B$3:$B$7, $C$9, 0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))), INDEX($F$3:$F$7, SMALL(IF(ISNUMBER(MATCH($E$3:$E$7, $C$9, 0)), MATCH(ROW($E$3:$E$7), ROW($E$3:$E$7)), ""), ROWS($A$1:A1)-COUNTIF($B$3:$B$7, $C$9))))

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

While writing in the Excel practice inserting formula within IFERROR

Two tables formula - Index Match with Multiple Matches

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

INDEX( range to look at the value, row_number, column_number) 

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.

Autofill-Index Match with Multiple Matches

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-Index Match with Multiple Matches

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

Shop 2-Index Match with Multiple Matches

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

‘Sheet name’! 

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

IFERROR(INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(MATCH($B$3:$B$7, $C$9, 0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))), INDEX('Shop 2'!$C$3:$C$7, SMALL(IF(ISNUMBER(MATCH('Shop 2'!$B$3:$B$7, $C$9, 0)), MATCH(ROW('Shop 2'!$B$3:$B$7), ROW('Shop 2'!$B$3:$B$7)), ""), ROWS($A$1:A1)-COUNTIF($B$3:$B$7, $C$9))))

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.

Two sheets formlua-Index Match with Multiple Matches

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.

Autofill two sheets-Index Match with Multiple Matches

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:

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo