Apply conditional formatting to any cell in Col A that matches the value of any cell in the range B3-CodePudding

In google sheets, I would like to apply conditional formatting to any cell in Col A that matches the value of any cell in the range B3:B11 See sample image.

I tried "custom formula is": =A3=B3:B11 over the range A3:A11, but that created unexpected results

CodePudding user response：

If you have both text or numbers you can use this:

=SUM(--ARRAYFORMULA(OR(A1=$B$3:$B$11)*(A1<>"")))

CodePudding user response：

you can try this for exact word match(skips partial matches)

=REGEXMATCH(A1, "(?i)"&TEXTJOIN("|", 1, ARRAYFORMULA(REGEXREPLACE(B:B,"(. )","\\b$1\\b"))))

CodePudding user response：

try on range A3:A:

=REGEXMATCH(A3&""; TEXTJOIN("|"; 1; B$3:B))