Extract multiple matches into separate rows

jordiejones

New Member
Joined
May 5, 2010
Messages
29
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

Active Member
Joined
Mar 5, 2018
Messages
475
Office Version
2016
Platform
Windows
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:
Header1Data1
10A10A - 1
10A10A - 2
20B20B - 1
10A10A - 3
10A10A - 4
10A10A - 5
10A10A - 6
10A10A - 7
10A10A - 8
10A10A - 9
10A10A - 10

<tbody>
</tbody>

The you can change the last A3 to A4 to skip the first match
A
1Result
210A
310A - 2
410A - 3
510A - 4
610A - 5
710A - 6
810A - 7
910A - 8
1010A - 9
1110A - 10

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Is this what you were trying to do?
 

Forum statistics

Threads
1,086,069
Messages
5,387,605
Members
402,071
Latest member
Nauef

Some videos you may like

This Week's Hot Topics

Top