## INDEX MATCH – multiple results

*Article last updated on March 25, 2018*

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the value in cell E3.

The matching rows are 3, 5 and 8 so the array formula returns 3 values in cell range E6:E8.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Now copy cell E6 and paste to cells below as far as needed.

### Explaining formula in cell E6

*Step 1 - Find matching values*

The MATCH function matches a cell range against a single value returning an array.

MATCH($B$3:$B$8, $E$3, 0) becomes MATCH({"A"; "B"; "A"; "C"; "B"; "A"}, "A", 0) and returns {1; #N/A; 1; #N/A; #N/A; 1}.

If a value is equal to the search value MATCH function returns 1. If it is not equal the MATCH function returns #N/A.

The picture above displays the array in column A.

*Step 2 - Convert array values to boolean values*

The IF function cant process error values so to solve that I am going to use the ISNUMBER function to convert the array values to boolean values.

ISNUMBER(MATCH($B$3:$B$8, $E$3, 0)) becomes ISNUMBER({1; #N/A; 1; #N/A; #N/A; 1}) and returns {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}

The array is shown in column A, see picture below.

*Step 3 - Identify rows*

The IF function converts the boolean values into row numbers and blanks.

becomes

The MATCH and ROW functions calculate an array with sequential numbers, 1 to n, determined by the size of the cell range. In this case, $B$3:$B$8 has 6 values so the array becomes 1 to 6.

and returns {1;"";3;"";"";6}. The picture below shows the relative row numbers for cell range B3:B8.

#### Step 4 - Get the k-th smallest row number

To be able to return the correct value the formula must know which value to get. The SMALL function determines the value to get based on row number.

becomes

The ROWS function returns a number that changes when you copy the cell and paste to cells below.

and returns 1. In the next cell below ROWS($A$1:A1) changes to ROWS($A$1:A2) and returns 2.

*Step 5 - Get values from column C using row numbers*

becomes

The first cell value in cell range $C$3:$C$8 is 6, the INDEX function returns 6 in cell E6.

### Download Excel *.xlsx file

INDEX MATCH multiple results.xlsx

### Related post

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form