Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 21st 07, 02:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 49
Default 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


  #2   Report Post  
Old March 21st 07, 03:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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

  #3   Report Post  
Old March 21st 07, 05:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Show result of a SUM in a VLOOKUP Sarah (OGI) Excel Worksheet Functions 5 March 23rd 07 06:50 PM
Vlookup result change DP7 Excel Worksheet Functions 1 March 6th 07 05:28 PM
vlookup shows result one cell above the expected result Marie Excel Worksheet Functions 7 November 14th 06 03:52 AM
VLOOKUP - Can't see result plunk25 Excel Worksheet Functions 1 June 29th 06 04:58 PM
vlookup based on random result returns incorrect result rickat Excel Worksheet Functions 1 December 6th 05 02:16 PM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017