Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show result of a SUM in a VLOOKUP | Excel Worksheet Functions | |||
Vlookup result change | Excel Worksheet Functions | |||
vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
VLOOKUP - Can't see result | Excel Worksheet Functions | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions |