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

[Son]

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


[TABLE="width: 427"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]TYPE[/TD]
[TD]NAME[/TD]
[TD][/TD]
[TD][/TD]
[TD]CRITERIA[/TD]
[TD]RETURN VALUE[/TD]
[/TR]
[TR]
[TD]1.0.1[/TD]
[TD]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]1.0.1[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]1.0.2[/TD]
[TD]MARY[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.0.2[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2.0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2.0.1[/TD]
[TD]GEORGE[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.0.1[/TD]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2.0.2[/TD]
[TD]ADAM[/TD]
[/TR]
[TR]
[TD]2.0.2[/TD]
[TD]ADAM[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.0.2[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]2.0.2[/TD]
[TD]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2.1.1[/TD]
[TD]HELEN[/TD]
[/TR]
[TR]
[TD]2.1.1[/TD]
[TD]HELEN[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.1.1[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD]2.1.1[/TD]
[TD]MARY[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.1.1[/TD]
[TD]ANNE[/TD]
[/TR]
[TR]
[TD]2.1.1[/TD]
[TD]ANNE[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.1.1[/TD]
[TD]JIM[/TD]
[/TR]
[TR]
[TD]2.1.1[/TD]
[TD]JIM[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2.1[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD]2.2.1[/TD]
[TD]MARY[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2.1[/TD]
[TD]CHARLES[/TD]
[/TR]
[TR]
[TD]2.2.1[/TD]
[TD]CHARLES[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2.1[/TD]
[TD]ERIC[/TD]
[/TR]
[TR]
[TD]2.2.1[/TD]
[TD]ERIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2.1[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]2.2.1[/TD]
[TD]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[admiral100]

You are welcome , thank you for the feedbak

 

[Aladin Akyurek]

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.

 

[Son]

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!

 

[Aladin Akyurek]

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.

 

[Son]

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

 

[Aladin Akyurek]

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

You are welcome.