For some reason this returns a circular reference....

"T. Valko" wrote:

Try this array formula** entered in cell E1:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$20,A$1),INDEX(C$1: C$20,SMALL(IF(B$1:B$20=A$1,ROW(C$1:C$20)),ROWS(E$1 :E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down until you get blanks meaning all the relative 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
...

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