|
Lookup formula to return all instances of match?
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! |
Lookup formula to return all instances of match?
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! |
Lookup formula to return all instances of match?
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! |
Lookup formula to return all instances of match?
Nevermind -- I figured it out! THANKS SO MUCH!!
"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! |
Lookup formula to return all instances of match?
You're welcome - appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "CParker" wrote in message ... Nevermind -- I figured it out! THANKS SO MUCH!! "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! |
Lookup formula to return all instances of match?
|
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! |
Lookup formula to return all instances of match?
Hey,
I'm trying to figure out the same issue. Could you please send me the answer to Thanks! "Vaza" wrote: Hi there Please can you post how you were able to achieve this? Or can you email at , as I need to do the same thing Many thanks "CParker" wrote: Nevermind -- I figured it out! THANKS SO MUCH!! "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! |
Lookup formula to return all instances of match?
--------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- These forums only work if people actually post the solution !!! |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com