Lookup formula to return all instances of match?
CAN YOU please tell what you coded in order to diplay the result in adjacent
cells.
thankyou.
"CParker" wrote:
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!
"RagDyer" wrote:
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.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CParker" wrote in message
...
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!
|