vlookup - more than one result

[Heine]

Hello everybody,

Does anybody know if it is possible to create a lookup formula, which
returns all values if more than one?

For instance:

1 Tom
1 Dick
1 Harry

Then I want it to return all three names when looking up 1.



Best regards
Heine

 

[Domenic]

Assuming that A2:A10 contains the number, and B2:B10 contains the
corresponding name, let D2 contain the number of interest, such as 1,
then try...

E2:

=COUNTIF(A2:A10,D2)

F2, copied down:

=IF(ROWS($F$2:F2)<=$E$2,INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$
2:$A$10)-ROW($A$2)+1),ROWS($F$2:F2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Heine]

Assuming that A2:A10 contains the number, and B2:B10 contains the
corresponding name, let D2 contain the number of interest, such as 1,
then try...

E2:

=COUNTIF(A2:A10,D2)

F2, copied down:

=IF(ROWS($F$2:F2)<=$E$2,INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$
2:$A$10)-ROW($A$2)+1),ROWS($F$2:F2))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!








- Show quoted text -

Thanks a lot - I will try that out

Best regards
Heine