ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   vlookup - more than one result (https://www.excelbanter.com/excel-worksheet-functions/135792-vlookup-more-than-one-result.html)

Heine

vlookup - more than one result
 
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

vlookup - more than one result
 
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!

In article . com,
"Heine" wrote:

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


Heine

vlookup - more than one result
 
On Mar 21, 3:40 pm, Domenic wrote:
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!

In article . com,



"Heine" wrote:
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- Hide quoted text -


- Show quoted text -


Thanks a lot - I will try that out

Best regards
Heine



All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com