# Thread: Extract multiple matches into separate rows Thanks: 0 Likes: 0

1. ## Extract multiple matches into separate rows

Hello world.
I'm having an issue where I have and index and match with multiple return values but running into problems.
Here's my setup:

A B C D E
1
2 10A
3 [first]
4 [2nd]
5 [3rd]
6 [4th]

=IFERROR(INDEX('BETA Care Plans'!\$B\$1:\$B\$215,SMALL(IF('BETA Care Plans'!\$A\$1:\$A\$215=A\$2,ROW('BETA Care Plans'!\$A\$1:\$A\$215)-MIN(ROW('BETA Care Plans'!\$A\$1:\$A\$215))+1),ROWS(\$A\$3:A3))),"")

I'm trying to index and match the value in A2 to another sheet and return all matching values rather than the first matching value.

Only thing I can think of causing problems:
The text values in the other sheet come from formulas as well
Some of the values have a " " at the start of the text

=IFERROR(INDEX('BETA Care Plans'!\$B\$1:\$B\$215,SMALL(IF('BETA Care Plans'!\$A\$1:\$A\$215=A\$2,ROW('BETA Care Plans'!\$A\$1:\$A\$215)-MIN(ROW('BETA Care Plans'!\$A\$1:\$A\$215))+1),ROWS(\$A\$3:A3))),"")

when I enter this without the array, it returns the second returned value and nothing else. When I enter as array it returns "". when I remove the IFERROR, it returns #NUM .

any help would be greatly appreciated.

2. ## Re: Extract multiple matches into separate rows

The use of SMALL means this should be entered as an array formula with Ctrl-Shift-Enter to get the curly brackets.

If your BETA Care Plans looks like this:
 Header1 Data1 10A 10A - 1 10A 10A - 2 20B 20B - 1 10A 10A - 3 10A 10A - 4 10A 10A - 5 10A 10A - 6 10A 10A - 7 10A 10A - 8 10A 10A - 9 10A 10A - 10

The you can change the last A3 to A4 to skip the first match
A
1 Result
2 10A
3 10A - 2
4 10A - 3
5 10A - 4
6 10A - 5
7 10A - 6
8 10A - 7
9 10A - 8
10 10A - 9
11 10A - 10
Sheet1

Array Formulas
Cell Formula
A3 {=IFERROR(INDEX('BETA Care Plans'!\$B\$1:\$B\$215,SMALL(IF('BETA Care Plans'!\$A\$1:\$A\$215=A\$2,ROW('BETA Care Plans'!\$A\$1:\$A\$215)-MIN(ROW('BETA Care Plans'!\$A\$1:\$A\$215))+1),ROWS(\$A\$3:A4))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Is this what you were trying to do?