3

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.

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.

  • There are approaching three thousand entries so then I guess that we'll find out just how fast the regular expression implementation is! – sustained Dec 23 '15 at 20:42
  • Google Sheets support strings up to 50000 characters in length, so 3000 words should still be okay unless they are unusually long. But I added another method for comparison, using simple match. Don't know which will be faster. – user79865 Dec 23 '15 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.