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]

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),"")


[TABLE="width: 608"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TYPE[/TD]
[TD]NAME[/TD]
[TD]Help1[/TD]
[TD]Help2[/TD]
[TD]Help3[/TD]
[TD]CRITERIA[/TD]
[TD]RETURN VALUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.0.1[/TD]
[TD]JOHN[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1.0.1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.0.1[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.0.2[/TD]
[TD]MARY[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1.0.2[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1.0.2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2.0.1[/TD]
[TD]GEORGE[/TD]
[TD]2.0[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2.0.1[/TD]
[TD]GEORGE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2.0.1[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2.0.2[/TD]
[TD]ADAM[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2.0.2[/TD]
[TD]ADAM[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2.0.2[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2.0.2[/TD]
[TD]JOHN[/TD]
[TD]2.0.2[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]2.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2.0.2[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]2.1.1[/TD]
[TD]HELEN[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2.1.1[/TD]
[TD]HELEN[/TD]
[TD]2.1[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]2.1.1[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2.1.1[/TD]
[TD]MARY[/TD]
[TD]2.1.1[/TD]
[TD]11[/TD]
[TD]3[/TD]
[TD]2.1.1[/TD]
[TD]ANNE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2.1.1[/TD]
[TD]ANNE[/TD]
[TD]2.1.1[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]2.1.1[/TD]
[TD]JIM[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2.1.1[/TD]
[TD]JIM[/TD]
[TD]2.1.1[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]2.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2.1.1[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD]2.2.1[/TD]
[TD]MARY[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2.2.1[/TD]
[TD]MARY[/TD]
[TD]2.2[/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD]2.2.1[/TD]
[TD]CHARLES[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]2.2.1[/TD]
[TD]CHARLES[/TD]
[TD]2.2.1[/TD]
[TD]16[/TD]
[TD]3[/TD]
[TD]2.2.1[/TD]
[TD]ERIC[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]2.2.1[/TD]
[TD]ERIC[/TD]
[TD]2.2.1[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]2.2.1[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]2.2.1[/TD]
[TD]JOHN[/TD]
[TD]2.2.1[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.2.1[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Son]

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

 

[Son]

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

 

[admiral100]

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

 

[Son]

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!!!

 

[admiral100]

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)

 

[Aladin Akyurek]

@Son

How do you know beforehand what the criteria are as they appear in column E of your exhibit?

 

[Son]

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!!!

 

[Son]

@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!!!