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.

Adelie Penguin
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
Alaskan Malamute
Aldabra Giant Tortoise
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

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.