Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Find multiple hits matching one criterion eg with index/match or vlookup etc

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find multiple hits matching one criterion eg with index/match or vlookup etc

    Hi, I've been trying to find mutliple hits matching one criterion (column E) so as to produce the following results in column F:


    A B C D E F
    TYPE NAME CRITERIA RETURN VALUE
    1.0.1 JOHN 1.0
    0 1.0.1 JOHN
    1.0.2 MARY 1.0.2 MARY
    0 2.0
    2.0.1 GEORGE 2.0.1 GEORGE
    0 2.0.2 ADAM
    2.0.2 ADAM 2.0.2 JOHN
    2.0.2 JOHN 2.1
    0 2.1.1 HELEN
    2.1.1 HELEN 2.1.1 MARY
    2.1.1 MARY 2.1.1 ANNE
    2.1.1 ANNE 2.1.1 JIM
    2.1.1 JIM 2.2
    0 2.2.1 MARY
    2.2.1 MARY 2.2.1 CHARLES
    2.2.1 CHARLES 2.2.1 ERIC
    2.2.1 ERIC 2.2.1 JOHN
    2.2.1 JOHN


    In column A, I have the codes and in column B there are the corresponding names.

    I need to have the codes in column E and in column F to have all the names corresponding to each code. As you can see, there are more than one instances in some codes.

    Do you think this is possible? Of course index/match in the classic way I'm using it, produces only the first name for each code.

    I've been trying for quite some time to find something to solve it, but I have not been successful. So, I'd appreciate your thoughts on this!!!

    Thanks in advance for any advice.

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    816
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    Hi,

    If you feel comfortable adding 3 additional columns, you can try this...

    C2 = 1
    C3 =IFERROR(IF(A2<>0,A2,IF(LEFT(A1,3)=LEFT(A3,3),"",VALUE(LEFT(A3,3)))),"")
    D2 = 1
    D3 (Ctrl+Shift+Enter NOT just Enter) =IF(IFERROR(IF(A2<>0,A2,IF(LEFT(A1,3)=LEFT(A3,3),"",VALUE(LEFT(A3,3)))),"")="","",ROWS((A$1:A2)))
    E2 = 1
    E3 =IF(F3=F2,E2+1,ROW($A$1))
    F2 =IFERROR(INDEX($C$2:$C$20,SMALL($D$2:$D$20,ROW(A1))),"")
    G2 (Ctrl+Shift+Enter NOT just Enter)
    =IFERROR(INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=F2,ROW($A$1:$A$20),""),E2),1),"")


    A B C D E F G
    1 TYPE NAME Help1 Help2 Help3 CRITERIA RETURN VALUE
    2 1.0.1 JOHN 1 1 1 1
    3 0 1.0.1 2 1 1.0.1 JOHN
    4 1.0.2 MARY 1 1.0.2 MARY
    5 0 1.0.2 4 1 2
    6 2.0.1 GEORGE 2.0 5 1 2.0.1 GEORGE
    7 0 2.0.1 6 1 2.0.2 ADAM
    8 2.0.2 ADAM 2 2.0.2 JOHN
    9 2.0.2 JOHN 2.0.2 8 1 2.1
    10 0 2.0.2 9 1 2.1.1 HELEN
    11 2.1.1 HELEN 2.1 10 2 2.1.1 MARY
    12 2.1.1 MARY 2.1.1 11 3 2.1.1 ANNE
    13 2.1.1 ANNE 2.1.1 12 4 2.1.1 JIM
    14 2.1.1 JIM 2.1.1 13 1 2.2
    15 0 2.1.1 14 1 2.2.1 MARY
    16 2.2.1 MARY 2.2 15 2 2.2.1 CHARLES
    17 2.2.1 CHARLES 2.2.1 16 3 2.2.1 ERIC
    18 2.2.1 ERIC 2.2.1 17 4 2.2.1 JOHN
    19 2.2.1 JOHN 2.2.1 18
    20 2.2.1 19

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    thanks for your reply!!! I'll try it and let you know!!!

  4. #4
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    I'm having much trouble with the iserror function. I use if(iserror(),,) and I cannot make it work! Can you please help?

  5. #5
    Board Regular
    Join Date
    Jan 2015
    Posts
    816
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    Which formula exactly do you mean ? There are five...in which cell?

    And don't forget D3 & G2 needs to Ctrl+Shift+Enter NOT just Enter

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    All the formulas that contain iserror.

    I believe it's a formula that is not recognized in my version of excel.

    If you could give me what needs to be in the cell without the error part, then I can use my if(iserror(),,) to get the result.

    So, could you do that? give me the true part of the if and then I'll add the iserror.

    Thanks in advance for your help, which is greately appreciated!!!

  7. #7
    Board Regular
    Join Date
    Jan 2015
    Posts
    816
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    It's not iserror formula it is IFERROR

    but as you wish...

    C3 =
    IF(A2<>0,A2,IF(LEFT(A1,3)=LEFT(A3,3),"",VALUE(LEFT(A3,3))))

    D3
    =IF((IF(A2<>0,A2,IF(LEFT(A1,3)=LEFT(A3,3),"",VALUE(LEFT(A3,3)))))="","",ROWS((A$1:A2)))

    F2 =INDEX($C$2:$C$20,SMALL($D$2:$D$20,ROW(A1)))

    G2 =INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=F2,ROW($A$1:$A$20),""),E2),1)





  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,433
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    @Son

    How do you know beforehand what the criteria are as they appear in column E of your exhibit?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    yes !!!! it works perfectly!!! thank you so much.

    I haven't quite yet grasped how it works, with the helper columns, but I've managed to make it work for my purposes. Actually, all I needed was the help3 column and the G column with the result. The "criteria" column F is a fixed list. So, I made it work. I am always amazed at what a little "logic" and excel can accomplish!!!

    Thanks again!!!

  10. #10
    Board Regular
    Join Date
    Mar 2010
    Posts
    257
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

    Quote Originally Posted by Aladin Akyurek View Post
    @Son

    How do you know beforehand what the criteria are as they appear in column E of your exhibit?
    Actually, it's a fixed list. So I just needed the F column in my exhibit to be filled with names (or G column in the above solution example).

    Thanks for your input!!!

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
  •