Conditional Formatting formula for where a word matches in a list?

0
votes

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

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

1
google-sheetsgoogle-sheets-formulags-conditional-formatting

1 Answers

1
votes

I think this will solve your problem. In F2, put this. It creates the join needed for the OR needed in the regexmatch.

=transpose(join("|",indirect("A2:A"&counta(A:A))))

In G2 put the following which returns a 1 (true) or 0 (false) for a match from the list in column A.

=arrayformula(if(E2:E<>"",(sign(REGEXMATCH(E2:E,""&F2&""))),""))

In conditional format, put the custom formula:

=AND(E2<>"",G2=1)

Apply to range E2:E. This is a copy of my test spreadsheet:

https://docs.google.com/spreadsheets/d/1AwQBRtB_8QMbKxQLGzwE0y3RTlO6CyM6sGcUmCc5iQ8/edit?usp=sharing

Sorry it took so long to work it out.