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.

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

Thanks again!



On May 17, 6:30*am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 16 May 2011 23:29:15 -0700 (PDT), F J <fjg...@gmail.com> wrote:
> >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.
>
> Enter the words to look up in some range, one word per range.
>
> I NAME'd my range List_of_Fruits, but you can use absolute references also.
>
> Then try this formula which must be **array-entered**:
>
> =IF(OR(COUNTIF(A1,"*" & List_of_Fruits &"*")),"Yes","")
>
> ----------------------------------------
>
> To **array-enter** a formula, after entering
> the formula into the cell or formula bar, hold down
> <ctrl><shift> while hitting <enter>. *If you did this
> correctly, Excel will place braces {...} around the formula.- Hide quotedtext -
>
> - Show quoted text -