Lookup formula to return all instances of match?

[CParker]

Sample data:

Column 1 Column 2
Labrador Dog
Beagle Dog
Siamese Cat
Toucan Bird
Poodle Dog

In a separate spreadsheet, I am trying to create a formula that will search
in column 2 for "Dog" and return all individual instances in column 1 in
separate rows:
Labrador
Beagle
Poodle

Any suggestions?
Thanks!

 

[RagDyer]

Say your list is on Sheet1.
In A1 of Sheet2 enter
dog
Then in A2 of Sheet2, try this *array* formula:

=IF(COUNTIF(Sheet1!B$1:B$5,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1))),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as far as you anticipate the number of
returns you'll get.
Make sure you copy down enough rows to ensure *all* possibilities are
displayed.

 

[CParker]

This is great, but I am still just a little lost...

Say my list is on Sheet 1.

In B6 of Sheet2, I have entered "Dog."
I now want the word "Labrador" to appear in cell A6 of Sheet2, then Beagle
in cell A7, then Poodle in cell A8. I don't understand what to do to change
this from your rows to my columns. Can you help again?

Thanks so much!! I'm so close!

 

[CParker]

Nevermind -- I figured it out! THANKS SO MUCH!!

Say your list is on Sheet1.
In A1 of Sheet2 enter
dog
Then in A2 of Sheet2, try this *array* formula:

=IF(COUNTIF(Sheet1!B$1:B$5,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1))),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as far as you anticipate the number of
returns you'll get.
Make sure you copy down enough rows to ensure *all* possibilities are
displayed.

 

[RagDyeR]

You're welcome - appreciate the feed-back.

 

[Vaza]

Hi there
Please can you post how you were able to achieve this? Or can you email at
(e-mail address removed), as I need to do the same thing

Many thanks

 

[Miss MasH]

CAN YOU please tell what you coded in order to diplay the result in adjacent
cells.
thankyou.

 

[tmzebra]

Hi, did Cparker ever respond to you? I'm trying to do the same thing as well
and not having any luck

 

[Asaf]

Hey,
I'm trying to figure out the same issue.
Could you please send me the answer to (e-mail address removed)
Thanks!

 

[Foddski]

---------------------------------------------------------------------------
These forums only work if people actually post the solution !!!

 

[pivot]

great formula and attempting to use it for something however when I change A1
it does not match the corresponding coloumn