See if a cell contains a string not from a list of strings in another column

Question

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B.

Column A contains cells with multiple words per cell, comma separated:

sarah, john, esther sarah, john yara, sarah

Column B contains one word per cell like:

sarah john esther

I want to know when a word in the first column is not in the second column. In our example, that would be yara.

Ideally that cell would go red, or another cell in the same row would show a warning.

Answer 1

You can do this by applying to range A1:A the conditional formatting with custom formula

=counta(unique(transpose(split(A1&", "&JOIN(", ",B$1:B),", "))))>counta(unique(B$1:B))

Explanation.

• To the right of the inequality sign, counta(unique(B$1:B)) counts the number of distinct words in column B.
• On the left, A1&", "&JOIN(", ",B$1:B) concatenates A1 with the content of column B, comma-and-space separated.
• Then split(...,", ") separates all of this into a row of words
• Then counta(unique(transpose(...))) counts the number of distinct words among these.
• Formatting is applied if the second count is greater than the first.