Verify if a string entered in a cell appears in one of the cells in the column next to it, case sens-CodePudding

In my Google Sheet, I have a list of company names in column A. I need to enter a list of company names in column B, and highlight any cells in column B if the name in that cell does not appear in column A. This needs to be case sensitive.

For example: if Burger King is in column A, and BURGER KING is entered in Column B, that cell in column B should turn yellow. If Burger King is entered in column A, and Burger King is entered in column B, that cell in column B should remain white. If McDonald's is entered in column B, but does not appear in any cell in column A, that cell in column B should also turn yellow.

I tried using conditional formatting on column B with a custom formula of =countif(ArrayFormula(find(A:A, B2)),1)=0 and it appeared to be working at one point but is not currently working.

CodePudding user response：

try:

=NOT(REGEXMATCH(B1, TEXTJOIN("|", 1, A:A)))*(B1<>"")