Find multiple hits matching one criterion eg with index/match or vlookup etc - Page 2
Find multiple hits matching one criterion eg with index/match or vlookup etc
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

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

  1. #11
    Board Regular
    Join Date
    Jan 2015
    Posts
    790
    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

     
    You are welcome , thank you for the feedbak

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,362
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

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

    Quote Originally Posted by Son View Post
    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!!!
    In G2 control+shift+enter, not just enter, and copy down:

    =IF(ISNUMBER(MATCH($F2,$A$2:$A$19,0)),INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$F2,ROW($A$2:$A$19)-ROW($A$2)+1),COUNTIFS($F$2:F2,F2))),"")

    Note 1. You don't need helper columns.
    Note 2. This formula is robust against inserting rows in front of the current first row.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #13
    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
    In G2 control+shift+enter, not just enter, and copy down:

    =IF(ISNUMBER(MATCH($F2,$A$2:$A$19,0)),INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$F2,ROW($A$2:$A$19)-ROW($A$2)+1),COUNTIFS($F$2:F2,F2))),"")

    Note 1. You don't need helper columns.
    Note 2. This formula is robust against inserting rows in front of the current first row.

    Hi, thanks so much for posting this alternative way. I understand it's more robust, but I now see that it uses countifs. As I'm still using excel 2003, I believe I can't use countifs. The reason I'm still using excel 2003 is that excel 2010 and on, does not support the vba that it is written in the workbook, and I still haven't found a way to convert it to the 2010 version.

    Anyway, I'm using Admiral's solution with one helper column and I'm having the job done, even if it takes a lot more work.

    Thanks again for your help!

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,362
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

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

    Quote Originally Posted by Son View Post
    Hi, thanks so much for posting this alternative way. I understand it's more robust, but I now see that it uses countifs. As I'm still using excel 2003, I believe I can't use countifs. The reason I'm still using excel 2003 is that excel 2010 and on, does not support the vba that it is written in the workbook, and I still haven't found a way to convert it to the 2010 version.

    Anyway, I'm using Admiral's solution with one helper column and I'm having the job done, even if it takes a lot more work.

    Thanks again for your help!
    Just replace COUNTIFS with COUNTIF.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #15
    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

    hahaha it was... so... simple!!! It works perfectly!!! thank you!!!!

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,362
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

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

      
    Quote Originally Posted by Son View Post
    hahaha it was... so... simple!!! It works perfectly!!! thank you!!!!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  

 

 
DMCA.com