Returning multiple instances of the same lookup value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
It appears to be getting the correct number of responses, but I'm only
getting "#VALUE!" for responses.
 
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 :-)
------------------------------­------------------------------­----------------
 
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
 
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
 
Back
Top