Excel INDEX MATCH Formulas with Returning Multiple Matches

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.

⏷ 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 =INDEX(C4:C12,2). So, this formula will return the value from cell 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.

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.

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.

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.

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.

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.

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.

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

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.

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

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