conditional format for text that contains MULTIPLE words

[KPK185]

hey, so I searched for an answer to this but couldn't find anything that would work.

I have a column full of product names and a column full of colors words like "red' "white" "blue" etc.

What I want to do is highlight the cell in the product name column if it contains ANY of the words in the "color" column. so if a product name contains the word "red" OR "white" OR "blue" I want it to highlight.

I see a lot of posts saying to set the "color" column to a range with a name like "color" and then use the formula =MATCH(A1,color,0) where A1 would be the first cell in my product name list. But this won't work for me, because the product names are long and contain many other words, so searching for an exact match of the entire product name in the color column will never return a match.

Any idea on how to do this?

Thanks!

[Fotis1991]

It would be useful if you'll upload a small sample workbook.

[KPK185]

attached a sample spreadsheet to the original post.

thanks!

[bentleybob]

It's a tad crude, but ...

[Fotis1991]

In Conditional formatting rules.

=NOT(ISERROR(LOOKUP(2^15,SEARCH($B$2:$B$12,$A2),$B$2:$B$12)))

[daddylonglegs]

You could use this formula applied to column A

=COUNT(SEARCH(B$2:B$12,A1))

but note that might match partial words, e.g. it matches "Stone" to "cobblestone". So to prevent that try

=COUNT(SEARCH(" "&B$2:B$12&" "," "&A1&" "))

[Kevin UK]

Hi KPK185

See the attached file. The Color List is a named range called "ColorList", so you can add to it if required.

[KPK185]

you guys are the best.

thanks so much!

[ADAMNVDR]

How i can download the file.

[bentleybob]

You should be able to click on the file. Even though this thread was from 2013, the uploads are still there (I just checked). If you still can't, send me your email address and I'll send you a copy directly.