Mark cells that contain words from a FILTER list

user_10419791 0
Viewed 58

I have a list of words in a basket which I want to pre-select through a FILTER list and color code the words in the basket which appear in the FILTER sheet. The challenge is, that the words in the basket have not the same word, but rather contain it. Here an example >

FILTER:

+--------+
| Apple  |
+--------+
| Banana |
+--------+

Basket:

+--------------+---+
| Apple//Cake  | x |
+--------------+---+
| Water        |   |
+--------------+---+
| Coke bottle  |   |
+--------------+---+
| Banana split | x |
+--------------+---+
google-sheets-formula gs-conditional-formatting google-sheets arrays regex
2 Answers

use:

=(A2<>"")*(INDEX(REGEXMATCH(LOWER(A2), 
 TEXTJOIN("|", 1, LOWER(INDIRECT("FILTER!A:A"))))))

0

user_56326290

You can use the following formula as a rule in Conditional formatting 

=REGEXMATCH(A2,""&JOIN("|",INDIRECT("'FILTER'!A1:A3"))&"")

enter image description here

user_15277800
Related