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

"lo3t3ch" wrote in message
...
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?

Have you considered a pivot table?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"lo3t3ch" wrote in message
...
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?

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

"Biff" wrote:

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

"lo3t3ch" wrote in message
...
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?

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 :-)
------------------------------*------------------------------*----------------


"lo3t3ch" wrote in message
...
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!

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

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

"Biff" wrote:

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

"lo3t3ch" wrote in message
...
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,

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

"lo3t3ch" wrote:

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?

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

"Biff" wrote:

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

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

"Biff" wrote:

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

"lo3t3ch" wrote in message
...
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?