Vlookup but with multiple results
Fantastic !
"T. Valko" wrote:
Try this array formula entered in E1:
=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX ($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)) ,COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")
Copy down as needed then copy across until you get a *full column* of blanks
meaning all data has been extracted.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"ORLANDO VAZQUEZ" wrote in message
...
Mike,
I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it out.
The reason is that I want each corresponding set of results to appear on
the
line it corresponds to.
"Mike H" wrote:
Hi,
Try this. Drag down to find multiple results. It produce NUM errors when
it
doesn't find a resuly and you can wrap the whole thing in a n error trap
to
elminate this.
=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($ C$1:$C$10)),ROW()))
'This is an array formula which must be entered with CTRL+Shift+Enter and
NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array
Mike
"ORLANDO VAZQUEZ" wrote:
Thank you for your support.
Can someone please help me with the following array? type of question.
What formula can I use to return a list of results when there is more
than
one result?
If I use VLOOKup it returns only the first instance of the result.
For example: Looking up cell a1 value of 22 should result in the
following
list:
Dog
Biscuit
Steak
A B C D
22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle
|