Google-sheets – See if a cell contains a string not from a list of strings in another column

conditional formattinggoogle sheets

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.

Best Answer

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.
Related Topic