Here we have a sales dataset consisting of the following columns: Brand, Device Type, Model No. and Units Sold. We want to get all results from the Model No. column based on criteria (brand name) in cell H5.

⏷ 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** 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**)

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

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

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

Drag the **Fill Handle** icon to the right.

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

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

### Case 1.2 Returning Multiple Matches for Multiple Criteria

Say, we want to get the model numbers of Lenovo notebooks.

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

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

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

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.

In the main data, Acer is recorded three times, but two of those 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)),"")`

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

Let’s say we have 2 sales data in the same worksheet: Computer Brands and their model numbers, and Phone Brands and their model numbers. We want to get all Lenovo computer and phone model numbers from these 2 lookup arrays.

__Follow the steps below:__

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

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

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

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

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

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

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