Find multiple hits matching one criterion eg with index/match or vlookup etc - Page 2
Thanks:  0
Likes:  0

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

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

You are welcome , thank you for the feedbak

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

Originally Posted by Son
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).

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.

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

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.

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

Originally Posted by Son
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.

Just replace COUNTIFS with COUNTIF.

5. 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. Re: Find multiple hits matching one criterion eg with index/match or vlookup etc

Originally Posted by Son
hahaha it was... so... simple!!! It works perfectly!!! thank you!!!!
You are welcome.

User Tag List

Posting Permissions

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