## INDEX MATCH – multiple results

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 file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.### 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.

INDEX and MATCH – multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]

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

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]

### 9 Responses to “INDEX MATCH – multiple results”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Any tips for doing this if theres multiple pairs of columns? Is it possible to concatenate results from multiple formulas of this kind into one column. For example lets say you were searching over 'n' pairs of the "text" and "amount" columns side by side, but still wanted the search results in a single column, like you have?

Joe Elizondo,

Yes, it is possible.

Array formula in cell C11:

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

Hi Oscar,

This example helped me to get closer to what I am looking for, but not completely yet :).

In my case, I am looking to retrieve the sum of the results returned in a single cell.

First of all, I have converted your formula to column based:

The formula used is:

`={IFERROR(INDEX($C$14:$L$14,SMALL(IF(ISNUMBER(MATCH($C$13:$L$13,C$16,0)),MATCH(COLUMN($C$13:$L$13),COLUMN($C$13:$L$13)),""),ROWS($A$1:A1))),0)}`

Text A G E C E A B G C C

Amount 2 4 1 3 2 3 1 3 1 2

Search A B C D E F G H

Results 2 1 3 0 1 0 4 0

3 0 1 0 2 0 3 0

0 0 2 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

SUM 5 1 6 0 3 0 7 0

As you see I have to create many rows to find all matches of each value. In my real case, I have more than 100 columns to match, and I don't know how many matches I will have. So I look for a way to return the sum of all matches (A - H) in a single row.

I am also working on a solution within VBA as back-up.

I hope you can help me. Please feel free to contact me if you need additional information.

Kind regards,

Jorgen

Hi Jorgen

Try this:

Formula in cell J3:

=SUMPRODUCT((B2:G13=I3)*1)

Hi Oscar,

This was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of Amounts and modified your formula to look up a given Amount and return the Text values that match that Amount. I got this to work with your INDEX(SMALL(IF(ISNUMBER(MATCH())))) and can pull all of the Text values from both Amount columns. I've also managed to return only Text values with that Amount from Amount2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the Text values within the given Amount array, while narrowing the search to a specific column within the array. Do you have any tips for this?

Formula in cell F5

`=INDEX($B$2:$B$14, SMALL(IF(ISNUMBER(MATCH($C$2:$D$14, $F$2, 0)), MATCH(ROW($C$2:$D$14), ROW($C$2:$D$14)), ""), ROWS($A$1:A1)))`

Formula in cell G5

`=INDEX($B$2:$B$14, MATCH($F$2, INDEX($C$2:$D$14, 0, MATCH($G$2, $C$1:$D$1,0)),0))`

Thanks,

Jeremiah

Jeremiah,

I believe you are looking for this formula:

https://www.get-digital-help.com/2011/06/22/vlookup-a-range-in-excel/

Oscar,

Thanks for the reply. It looks like my image link didn't come through, trying again here:

https://imgur.com/a/FjajCCc

The article you referenced is close to what I'm looking for, but it doesn't allow me to narrow my search within the array to return all matches from only one desired column. Hopefully my example in the screenshot linked above will clarify this, the objective in cell H4 is what I'm trying to figure out a formula for.

Best,

Jeremiah

Jeremiah,

Formula in cell B14:

=INDEX($B$3:$B$6, SMALL(IF((INDEX($C$3:$E$6, 0, MATCH($C$10,$C$2:$E$2, 0))=$C$9)*(COUNTIF($B$13:B13, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, ""), 1))

Table-1 Table-2

Number Name Number Name

10 A 10 search with Search result

11 B 13 C 13 #REF!

13 16 F

14 D 19 I

15 E 21 K

16

17 G

18 H

19

20 J

I have used this formula =+INDEX($D$3:$D12,MATCH($H4,$C$3:$C12,INDEX($G$3:$G7,MATCH($H4,$F$3:$F7,0))))

but is not working

I want there as 'C' because in Table-1 13 corresponding nothing so the formula should check with Table-2 with and result will be 'C'

Advance thanks