conditional formatting -- testing for multiple words

[The Moose]

I have a spreadsheet with a list of products. Column D contains the
product names.

I want to format the rows to stand out if a particular word is in the
product name. I've got this to work:
=FIND("Chef",$D1)

What I'd really like to do is have the formula look for a variety of
different products; e.g., "Chef", "Apple", "Lodge" ... etc.

Is there any way to do that??

Thanks.

Barb

 

[Biff]

Hi!

FIND is case sensitive so Chef and chef do not match.

Try this:

=OR(ISNUMBER(FIND({"Chef","Apple","Lodge"},D1)))

If you don't need it to be case sensitive replace FIND with SEARCH.

Biff

 

[Dave Peterson]

I would put my list of words on another worksheet (hidden???) and give that
range a nice name--say myList.

Then I'd use format|conditional formatting with a formula like:
=MATCH(D1,myList,0)

 

[Dave Peterson]

I don't think you can use arrays like this in conditional formatting.

 

[Biff]

Ooops!

Yep, you're right. No array constants.

Biff

 

[Biff]

The way that I read the OP is that there is more than just a single word in
the cells. I could be wrong!

Biff

 

[Dave Peterson]

I try not to read the original question--just read the responses and then I try
to divine the original question from those answers.

Maybe using a formula like:
=SUM(COUNTIF(D1,"*"&MyList&"*"))
would work.

Still with that list of words on a separate sheet.

 

[The Moose]

Thanks, guy.

I, too, found out that the arrays didn't work.

I haven't had a chance to try the other yet. I'm trying to do too many
things at once -- keep getting sidetracked -- character flaw, I guess
:GRIN:

I WILL post back after trying the other suggestions to let you all know
what worked.

Thanks again.

Barb

 

[The Moose]

Thank you, thank you, thank you!!!

It WORKS :LOL:

=SUM(COUNTIF(B1,"*"&mylist&"*")) -- highlights just the first colum.

What I wanted was the whole row to highlight so that it REALLY stands
out. A little tweaking of your formula, and I got EXACTLY what I
wanted:
=SUM(COUNTIF($B1,"*"&mylist&"*"))

Thanks so much.

Barb