Google Sheets – Filter and Match Cell Based on Date

You can use this formula.

Formula

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A + 7))

Screenshot

Example

I've created an example file for you: new date from entered date

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.