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

Change cell background color based on text in another column

What would the syntax be to conditionally format - if any cell in Column A contains a value that matches any value in Column B then display a blue background?

Looks like this would be done using Conditional Formatting under Format, where this custom formula works.

=OR(A1:A1000="Text Sample 1",A1:A1000="Text Sample 2")

Instead of listing a bunch of values in this formula, is there syntax that can represent values listed in another column (Column B)?

Solution

  • use this custom formula:

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

    0

    for exact finds use:

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

    0