Lookup formula to return all instances of match?

  • Thread starter Thread starter CParker
  • Start date Start date
C

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!
 
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.
 
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!
 
Nevermind -- I figured it out! THANKS SO MUCH!!

RagDyer said:
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.
Click to expand...
 
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
 
CAN YOU please tell what you coded in order to diplay the result in adjacent
cells.
thankyou.
 
Hi, did Cparker ever respond to you? I'm trying to do the same thing as well
and not having any luck
 
Hey,
I'm trying to figure out the same issue.
Could you please send me the answer to (e-mail address removed)
Thanks!
 
---------------------------------------------------------------------------
These forums only work if people actually post the solution !!!
 
great formula and attempting to use it for something however when I change A1
it does not match the corresponding coloumn
 
Back
Top