1

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.

player0
  • 124,011
  • 12
  • 67
  • 124
GKMPinkTaffy
  • 15
  • 5
  • @player0 The strangest thing is happening. If I paste in a long list of company names into column A, (about 2,500) the conditional formatting stops working. Does this happen for you? – GKMPinkTaffy Jan 12 '23 at 19:05
  • didnt tested it on such large sample but be aware that textjoin is limited to 50k characters and there is no workaround to extend this limit – player0 Jan 12 '23 at 19:50
  • @player0 Oh, good to know, appreciate your help very much. – GKMPinkTaffy Jan 13 '23 at 20:43

1 Answers1

0

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124