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