Search code examples
regexgoogle-sheetsgoogle-sheets-formulags-conditional-formattingtextjoin

How can I color cells based on list with conditional formatting in Google Sheets


So I'm trying to make a few different lists with different colors and then I want google sheets color automatically whatever is on the list when it's shown elsewhere. I've been trying a few different ways and found a solution that kind of works but isn't really scalable.

I'm bad at explaining things, so hopefully this example does a greater job on that than I did Here's example sheet


Solution

  • yellow list:

    =REGEXMATCH(G3, TEXTJOIN("|", 1, $B:$B))
    

    green list:

    =REGEXMATCH(G3, TEXTJOIN("|", 1, $C:$C))
    

    blue list:

    =REGEXMATCH(G3, TEXTJOIN("|", 1, $D:$D))
    

    red list:

    =REGEXMATCH(G3, TEXTJOIN("|", 1, $E:$E))
    

    0


    0


    UPDATE

    catfish fix:

    =REGEXMATCH(LOWER(G3), LOWER("^"&TEXTJOIN("$|^", 1, $B:$B)&"$"))