View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic Domenic is offline
external usenet poster
 
Posts: 150
Default 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