COUNTIF formula?

[F J]

Hi, I’m using Excel 2002 and I have a question about using a formula
to determine if certain words are found within a cell. For example, I
want to determine if several words are found in each cell in column A
and, if so, I want to put the word “Yes” in the corresponding cell in
column B. If the word isn’t found then I just want that cell in
column B to remain blank. So, in the following example, I want to see
if the following words:

Oranges
Apples
Lemons

are found in any of the cells in column A. The result would look like
this:

Column A Column B
Apples, Oranges, Pears Yes
Apples, Oranges Yes
Apples Yes
Limes, Pears
Oranges Yes
Pears
Oranges, Lemons, Limes Yes
Lemons, Limes Yes
Limes
Apples, Limes Yes

I know I could use a formula like this:

=IF(OR(COUNTIF(A1,"*Oranges*"),COUNTIF(A1,"*Apples*"),COUNTIF(A1,"*Lemons*")),"Yes","")

to do this in this example, but the real spreadsheets I am working
with sometimes have 50 or more words to look up in thousands of rows.
Is there any way to do this using a combination of COUNTIF and VLOOKUP
or MATCH or something like that? So far my attempts to do so have
failed. Thanks in advance for any information.

 

[F J]

Hi, Ron, thank you for your response. I tried your formula worked
great. This will save me so much time and work.

Thanks again!