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

Son

Active Member
Joined
Mar 19, 2010
Messages
284
Hi, I've been trying to find mutliple hits matching one criterion (column E) so as to produce the following results in column F:


ABCDEF
TYPENAMECRITERIARETURN VALUE
1.0.1JOHN1.0
0 1.0.1JOHN
1.0.2MARY1.0.2MARY
0 2.0
2.0.1GEORGE2.0.1GEORGE
0 2.0.2ADAM
2.0.2ADAM2.0.2JOHN
2.0.2JOHN2.1
0 2.1.1HELEN
2.1.1HELEN2.1.1MARY
2.1.1MARY2.1.1ANNE
2.1.1ANNE2.1.1JIM
2.1.1JIM2.2
0 2.2.1MARY
2.2.1MARY2.2.1CHARLES
2.2.1CHARLES2.2.1ERIC
2.2.1ERIC2.2.1JOHN
2.2.1JOHN

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>


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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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),"")


ABCDEFG
1TYPENAMEHelp1Help2Help3CRITERIARETURN VALUE
21.0.1JOHN1111
301.0.1211.0.1JOHN
41.0.2MARY11.0.2MARY
501.0.2412
62.0.1GEORGE2.0512.0.1GEORGE
702.0.1612.0.2ADAM
82.0.2ADAM22.0.2JOHN
92.0.2JOHN2.0.2812.1
1002.0.2912.1.1HELEN
112.1.1HELEN2.11022.1.1MARY
122.1.1MARY2.1.11132.1.1ANNE
132.1.1ANNE2.1.11242.1.1JIM
142.1.1JIM2.1.11312.2
1502.1.11412.2.1MARY
162.2.1MARY2.21522.2.1CHARLES
172.2.1CHARLES2.2.11632.2.1ERIC
182.2.1ERIC2.2.11742.2.1JOHN
192.2.1JOHN2.2.118
202.2.119

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I'm having much trouble with the iserror function. I use if(iserror(),,) and I cannot make it work! Can you please help?
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0
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)




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

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top