Extract multiple matches into separate rows

[jordiejones]

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.

 

[Toadstool]

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:
[TABLE="class: grid, width: 222"]
<tbody>[TR]
[TD="width: 64"]Header1[/TD]
[TD="width: 64"]Data1[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 1[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 2[/TD]
[/TR]
[TR]
[TD]20B[/TD]
[TD]20B - 1[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 3[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 4[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 5[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 6[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 7[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 8[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 9[/TD]
[/TR]
[TR]
[TD]10A[/TD]
[TD]10A - 10[/TD]
[/TR]
</tbody>[/TABLE]

The you can change the last A3 to A4 to skip the first match

	A
Result
10A
10A - 2
10A - 3
10A - 4
10A - 5
10A - 6
10A - 7
10A - 8
10A - 9
10A - 10

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3
[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Is this what you were trying to do?