Avatar of bobinorlando
bobinorlando
 asked on

Find words from a list in text of another column and return matching word if found

Experts,

I have a list of words in a column e.g. names of fruit in A2:A8

Fruits
Oranges
Apples
Pears
Bananas
Kiwi
Cherries
Strawberries

And I have a column of rows of text that may or may not contain one of the words in the list. e.g. Comments in Col. C

Comments
My favorite fruit is apples.
Oranges are a good source of vitamin C.
Ripe pears taste good but so do bananas.
My favorite pet is a dog.


If one of the words in Fruits is found in the text, I want to enter the matching word in a cell in Col B, else leave it blank.

I'll take the first occurrence of a match.

What is a good way to do this?

Thanks in advance!
Microsoft Excel

Avatar of undefined
Last Comment
barry houdini

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
barry houdini

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bobinorlando

ASKER
I see this works in your example file.
However, I'm having trouble applying it to my actual data (which is not the sample data I provided).

Can you plug some pseudo code into the formula to document what data goes where from my sample? e.g.

=IFERROR(LOOKUP(really big number,SEARCH(wordstofind,CellwithText),wordstofind),"iferrorprintthis")
bobinorlando

ASKER
Never mind. I've got it now. Thanks!
barry houdini

Hello bobinorlando,

Excellent! -yes, this is a perfect generic version

=IFERROR(LOOKUP(really big number,SEARCH(wordstofind,CellwithText),wordstofind),"iferrorprintthis")

regards, barry
Your help has saved me hundreds of hours of internet surfing.
fblack61