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
|