Data validation rules are triggered when a user adds or changes a cell value.
The search logic is “contains” by default because of how SEARCH works.
If not found, SEARCH returns an error.
After ISNUMBER evaluates the results from SEARCH we have an array of TRUE and FALSE values.
As long as all items are zero, SUMPRODUCT returns zero and validation succeeds.
If SUMPRODUCT returns a positive number, an invalid value has been found.
The formula returns FALSE and validation fails.
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
