Excel INDEX MATCH formulas with returning multiple matches mean returning all matches based on single or multiple criteria given in the formula.

In this Excel tutorial, you will learn how to use several INDEX MATCH formulas to return multiple matches in Excel.

Look at the following image. Here we have a sales dataset consisting of the following columns: Brand, Device Type, Model No. and Units Sold. We wish to get all returns from Model No. column based on criteria in cell H5.

In this article, you will learn several **INDEX MATCH** formulas to do the following tasks:

– Return multiple matches based on single or multiple criteria in vertical and horizontal manner.

– Return all matches when the matching values contain duplicates.

– Return multiple matches from multiple lookup arrays.

– Return multiple matches based on partially matched criteria.

**Note:**

- When we aim to return multiple matches with
**INDEX-MATCH**formulas, we have to use other functions as well, like**SMALL**,**IF**,**ISNUMBER**,**ROW**,**ROWS**,**COLUMN**,**SEARCH**,**COUNTIF**, etc. - All these functions are available in Excel 2007 or later versions.
- We have used Excel for Microsoft 365 to prepare this tutorial.

⏷ Getting Multiple Matches from a Column for Single or Multiple Criteria

⏷ Get Multiple Matches When the Matching Values Contain Duplicates

⏷ Get Multiple Matches from 2 Lookup Array

⏷ Get All Partial Matches

## How Does the INDEX-MATCH Combo Work?

**INDEX and MATCH functions** together in a single formula are used to look up a value in a range of cells and return the corresponding value.

For example, look at the following image– here we have a sales dataset. Say, our lookup value is a brand name: HP and we want to know its sales quantity.

So, the lookup array for **the MATCH function** is **B4:B12** (the brand names lie here).

The array for **the INDEX function** is **C4:C12** (sales quantity data lie here).

** =MATCH("HP",B4:B12,0)**; this will return 2, as the relative position of HP is 2 in range

**B4:B12**. Here, the last 0 denotes the exact match.

Now, if you write ** =INDEX(C4:C12,MATCH("HP",B4:B12,0))** inside a cell, it is the same as writing

**. So, this formula will return the value from cell**

`=INDEX(C4:C12,2)`

**C5**(cell number 2 in range

**C4:C12**)

## 1. Getting Multiple Matches from a Column with INDEX MATCH Formula **for Single or Multiple Criteria**

Here, you will learn how to get multiple matches based on single or multiple criteria using suitable **INDEX MATCH** formulas.

### 1.1 Returning Multiple Matches for Single Criterion

From the dataset below, we want to get all the model numbers of the __Lenovo__ brand.

__Follow the steps below:__

- In cell
**H5**, input the brand name, ‘Lenovo’.

- Insert the following formula on cell
**G8**.

`=IFERROR(INDEX($D$6:$D$14,SMALL(IF(ISNUMBER(MATCH($B$6:$B$14,$H$5,0)),MATCH(ROW($B$6:$B$14),ROW($B$6:$B$14)),""),ROWS($A$1:A1))),"")`

Here, we have added **the IFERROR function** in the formula. So, if the main formula finds no match and hence returns *#N/A*, **IFERROR** returns an empty string instead of *#N/A* errors.

- Now, drag the
**Fill Handle**icon down, until you get blank cells.

**Note:**

To get the matches horizontally, insert the following formula in cell **G9**.

`=IFERROR(INDEX($D$6:$D$14,SMALL(IF($B$6:$B$14=$H$5,ROW($B$6:$B$14)-ROW($B$6)+1),COLUMN(A1))),"")`

Then drag the **Fill Handle** icon to the right.

**Alternative Formula (Applicable in Excel 2010 and Later)**

You can also use the following formula with **the AGGREGATE function**.

`=IFERROR(INDEX($D$6:$D$14,AGGREGATE(15,3,(($B$6:$B$14=$H$5)/($B$6:$B$14=$H$5)*ROW($B$6:$B$14))-ROW($B$5),ROWS($G$8:G8))),"")`

### 1.2 Returning Multiple Matches for Multiple Criteria

To get multiple matches for **multiple criteria**, you can use the same formula we have already used. But, since we will match multiple criteria, we have to change the formula’s **IF** part a bit.

Say, we want to get the model numbers of __Lenovo__ __notebooks__.

We have two criteria here: 1st criterion is __Lenovo__, and the 2nd criterion is the device type which is __Notebook__.

__Follow the steps below:__

- In cell
**H5**, select the brand name__Lenovo__. - In cell
**H6**, write__Notebook__.

- Insert the following formula on cell
**G9**and drag the formula down until you get some blank cells.

`=IFERROR(INDEX($D$6:$D$14,SMALL(IF(($B$6:$B$14=$H$5)*($C$6:$C$14=$H$6),ROW($B$6:$B$14)-ROW($B$6)+1),ROW(A1))),"")`

Look at the GIF below. Here, you will see how the matches change with the change in criteria automatically.

**Notes:**

- You have to set both criteria to get any match using this formula. Only one criterion will not return anything.
- If you want to get the matches in a horizontal manner, use the following formula in cell
**G9**and drag the**Fill Handle**to the right.

`=IFERROR(INDEX($D$6:$D$14,SMALL(IF(($B$6:$B$14=$H$5)*($C$6:$C$14=$H$6),ROW($D$6:$D$14)-ROW($D$6)+1),COLUMN(A1))),"")`

__Easier Alternative Formula for Excel 2019 or Later:__

You can also use the following formula based on **the FILTER function**. Since this is an array function, you do not need to drag the formula to get multiple matches.

`=IFERROR(FILTER(D6:D14,(B6:B14=H5)*(C6:C14=H6)),"")`

## 2. Formula to Get Multiple Matches When the Matching Values Contain **Duplicates**

To get all matches when the matching values contain duplicates with **INDEX-MATCH**, you have to also use **the COUNTIF function** inside the formula.

In the following data, Acer ACNB-2018 occurs twice.

So, the previous formulas we have shown in Section 1 will return **Acer ACNB-2018** twice, which is obviously not what you want.

In this section, you will learn a formula that can give you all matches avoiding duplicates.

Say, in the following sales data, I want to get all the model numbers of __Acer__.

In the main data, __Acer __is recorded 3 times. But 2 of these are the same. If I input the following formula, I get the wrong output.

`=IFERROR(INDEX($D$6:$D$14,SMALL(IF(ISNUMBER(MATCH($B$6:$B$14,$H$5,0)),MATCH(ROW($B$6:$B$14),ROW($B$6:$B$14)),""),ROWS($A$1:A1))),"")`

But if I use the following formula inside cell **G8 **to get all matches for __Acer__ – avoiding duplicates – I get correct outputs.

`=IFERROR(INDEX($D$6:$D$14,MATCH(1,(COUNTIF($G$7:G7,$D$6:$D$14)=0)*($B$6:$B$14=$H$5),0)),"")`

We can see there are no duplicates in the result, though the model ACNB-2018 of the Acer brand exists two times in the dataset.

## 3. INDEX-MATCH Formula with Multiple Matches from **2 Lookup Array**

Let’s say, we have 2 sales data in the same worksheet. 1. Computer Brands and their model numbers, and 2. Phone Brands and their model numbers.

Now, we want to get all __Lenovo__ computer and phone model numbers from these 2 lookup arrays using an **INDEX-MATCH** formula.

__Follow the steps below:__

- Insert the following formula on cell
**H10**and press the**Enter**button.

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

- Drag the formula down until you get blank cells.

- Look at the GIF below. When we change the brand name from the drop-down list of cell
**H7**, results in the range**H10:H15**changes accordingly.

*If your workbook has more than 2 tables to look up and return multiple matches, let us know in the comment box!*

## 4. INDEX MATCH Formula to **Get All Partially Matching Values**

This **INDEX-MATCH** formula uses **the SEARCH function** inside it to deal with partial matches in the lookup array.

Say, your dataset contains ‘Adidas Shoes’ and ‘Nike Shoes’ and you want to get all corresponding matches where only the ‘shoes’ part matches.

__Follow the steps below:__

- Set
__shoes__as the lookup value in cell**H5**.

*‘Shoes’ matches with two products in the Product Name column.*

- Insert the following formula in cell
**H8**and drag the**Fill Handle**icon.

`=IFERROR(INDEX(E6:E14,SMALL(IF(ISNUMBER(SEARCH($H$5,$B$6:$B$14)),ROW($B$6:$B$14)-ROW(B6)+1),ROWS(G$8:G8))),"")`

Here you see all supplier names for Adidas ‘Shoes’ and Nike ‘Shoes’.

**Note:**

This formula is not case-sensitive. So, you don’t have to care about the case when setting up the criteria.

**Download Practice Workbook**

*In this article, you have learned several INDEX-MATCH formulas to get multiple matches with single or multiple criteria. We have also shown how to get multiple matches avoiding duplicates when your data have repeated values. We have also covered the case when you have 2 lookup arrays to return multiple matches with the INDEX-MATCH formula. Lastly, we have shown how you can get all partial matches with the help of the SEARCH function inside an INDEX-MATCH formula. If your problem is different from these, then let us know in the comment box.*

**<< Go Back to INDEX MATCH | Formula List | Learn Excel**

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