Thanks:  0
Likes:  0

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

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

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

 A B C D E F TYPE NAME CRITERIA RETURN VALUE 1.0.1 JOHN 1.0 0 1.0.1 JOHN 1.0.2 MARY 1.0.2 MARY 0 2.0 2.0.1 GEORGE 2.0.1 GEORGE 0 2.0.2 ADAM 2.0.2 ADAM 2.0.2 JOHN 2.0.2 JOHN 2.1 0 2.1.1 HELEN 2.1.1 HELEN 2.1.1 MARY 2.1.1 MARY 2.1.1 ANNE 2.1.1 ANNE 2.1.1 JIM 2.1.1 JIM 2.2 0 2.2.1 MARY 2.2.1 MARY 2.2.1 CHARLES 2.2.1 CHARLES 2.2.1 ERIC 2.2.1 ERIC 2.2.1 JOHN 2.2.1 JOHN

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

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

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

 A B C D E F G 1 TYPE NAME Help1 Help2 Help3 CRITERIA RETURN VALUE 2 1.0.1 JOHN 1 1 1 1 3 0 1.0.1 2 1 1.0.1 JOHN 4 1.0.2 MARY 1 1.0.2 MARY 5 0 1.0.2 4 1 2 6 2.0.1 GEORGE 2.0 5 1 2.0.1 GEORGE 7 0 2.0.1 6 1 2.0.2 ADAM 8 2.0.2 ADAM 2 2.0.2 JOHN 9 2.0.2 JOHN 2.0.2 8 1 2.1 10 0 2.0.2 9 1 2.1.1 HELEN 11 2.1.1 HELEN 2.1 10 2 2.1.1 MARY 12 2.1.1 MARY 2.1.1 11 3 2.1.1 ANNE 13 2.1.1 ANNE 2.1.1 12 4 2.1.1 JIM 14 2.1.1 JIM 2.1.1 13 1 2.2 15 0 2.1.1 14 1 2.2.1 MARY 16 2.2.1 MARY 2.2 15 2 2.2.1 CHARLES 17 2.2.1 CHARLES 2.2.1 16 3 2.2.1 ERIC 18 2.2.1 ERIC 2.2.1 17 4 2.2.1 JOHN 19 2.2.1 JOHN 2.2.1 18 20 2.2.1 19

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

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

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

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

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

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.

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

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)

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

@Son

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

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

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

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

@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).