Highlight/reject cells that contain any of the values from another sheet

Question

I have a sheet/tab that contains nothing but a single column where each row is a word, let's call it wordlist.

In another sheet/tab, I would like to make it so that for one of the columns, if the value matches ANY of the values from wordlist, that the cell is highlighted (or the input is rejected).

It is quite easy to do the opposite of this but I can't figure this one out. I have tried looking up how to do it and I have been messing around with custom formulas for a while now but I can't seem to figure it out.

Answer 1

Suppose that the wordlist is in Column A of Sheet1, and you want to apply validation to the range B2:D10 of the current sheet.

Method 1: match

Use custom formula

=iserror(match(B2, Sheet1!$A$1:$A$6, 0))

for data validation.

• B2 is the upper left corner of the range to which the rule is applied.
• The third parameter 0 requires exact match
• The formula returns True only if match returns an error, which means no match is found.

Data validation accepts the values for which the formula evaluates to True. Same can be done with conditional formatting.

Method 2: regexmatch

Form a regular expression from the list and validate against that. The string

 "^(" & join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A))) & ")$"

concatenates all values in that column into a regular expression that will match any of them. (For example, "^(first|second|third)$").

Then you can use regexmatch by this string to validate or conditionally format the data anywhere else. For example:

=not(regexmatch(B2, "^("&join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A)))&")$"))

which means the content must not match the regular expression.