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.

 

[Ron Rosenfeld]

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.

 

[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!

 

[Ron Rosenfeld]

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

Thanks again!

Glad to help. Thanks for the feedback.