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.