Vlookup but with multiple results
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
|