Results 1 to 2 of 2

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

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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?
    Life advice found on a book of matches: "Keep cool. Keep away from children."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •