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.

**Table of Contents**hide

## Download Practice Workbook

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

## Basics of INDEX-MATCH

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

## 6 Suitable Examples of Using INDEX-MATCH Formula with Multiple Matches

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

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. INDEX-MATCH with Multiple Criteria Belongs 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**.

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

**Read More: ****Excel Index Match single/multiple criteria with single/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****Index Match Sum Multiple Rows in Excel (3 Ways)****INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)**

### 3. INDEX-MATCH from Non-Adjacent Columns

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

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

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.

### 4. INDEX-MATCH 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.

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

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

- 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: ****Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function**

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

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.

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

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

### 6. 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**.

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

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

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

- 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:** **INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)**

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

## Further Readings

**How to use INDEX & MATCH worksheet functions in 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 Methods)****INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)****How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results****IF with INDEX-MATCH in Excel (3 Suitable Approaches)**

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 useExcel 365.Good Luck!

Regards,

Sabrina Ayon.Author, ExcelDemy