Google-sheets – In Google Spreadsheets, what formula should I use for the “Conditional Formatting” option if I want it highlighted if a word matches the list

google sheets

I know how conditional formatting works, but I don't know how to implement a specific formula. For example, I want the cell to be highlighted if any of the animals below is mentioned. I was thinking of creating a "conditional formatting" rule for each of them, but I think that's not efficient. There has to be a simpler formula.

Aardvark
Abyssinian
Adelie Penguin
Affenpinscher
Afghan Hound
African Bush Elephant
African Civet
African Clawed Frog
African Forest Elephant
African Palm Civet
African Penguin
African Tree Toad
African Wild Dog
Ainu Dog
Airedale Terrier
Akbash
Akita
Alaskan Malamute
Albatross
Aldabra Giant Tortoise
Alligator
Alpine Dachsbracke
American Bulldog
American Cocker Spaniel

EDIT: Here's an example. My goal is to get Column E's cells to get highlighted if any of the words match any item in Column A

Best Answer

Clear formatting from ColumnE, select ColumnE and Format, Conditional formatting..., Format cells if... Custom formula is and:

=countif(A:A,vlookup("* "&index(ArrayFormula(split(E1," ")),1)&" *",A:A,1,0))+countif(A:A,vlookup("* "&index(ArrayFormula(split(E1," ")),2)&" *",A:A,1,0))>0

Then select formatting of choice and Done.

Only works for one or two words in ColumnE and because of Airedale after prepending and appending spaces to the text in ColumnA.

Related Topic