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

Where did you enter the formula? You must have entered it within one of the
referenced ranges. If your data really is where you said it was and you
enter the formula in E1 as I suggested there is no way you'll get a circular
reference. You can enter the formula anywhere *except* within the range
B1:C20.

While the other suggestions will work, this version is the most robust.


--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
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