Returning multiple instances of the same lookup value

[Guest]

How can I have a lookup (or other function) find all values in column A that
match my lookup value? Simplified Example:
A B
1 cat
1 dog
2 bird
3 hamster
3 fish

I would like to look up "1" and have it return both "cat" and "dog", not
just "cat".
Is this possible?

 

[Biff]

Hi!

One way:

C1 = lookup value

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

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

Copy down until you get blanks.

Biff

 

[Bernard Liengme]

Have you considered a pivot table?

 

[Guest]

It appears to be getting the correct number of responses, but I'm only
getting "#VALUE!" for responses.

 

[Ken Wright]

Do you need it to do something that Data / Filter / Autofilter will not do?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Biff]

Hi!

You probably didn't enter the formula as an array.

Type the formula then hold down the CTRL key and the SHIFT key then hit
ENTER.

Biff

 

[Guest]

Hi,

Suppose your data is in the range A1:B7. In A10, you enter the digit 1 or
what ever you are looking for. In B10 enter the following array formula
(Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Copy this formula down.

Regards,

Ashish Mathur

 

[Guest]

Thanks. That was the problem.
I never would have figured that out.