Explanation
Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.
The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result.
Logically, any result greater than zero means that at least one value exists in the range. So, the final step is to evaluate the SUMPRODUCT result to see if its greater than zero. Any result greater than zero returns TRUE, and any result equal to zero returns FALSE.
With hard-coded values
You can also hard code the search values into the formula, using what is known as an " array constant “. For example, if you want to look for 3 values: red, cyan, and magenta inside the range H2:H8, you can use:
=SUMPRODUCT(--(H2:H8={"red","cyan","magenta"}))>0
In the above example {“red”,“cyan”,“magenta”} is the array constant, which is one way to supply multiple values in a single argument.
Partial matches or substrings
The formula above tests for equivalency only and will not find partial matches or substrings in the range. If you need to look for substrings, you can use this formula instead .
Explanation
The COUNTIF function counts cells that meet supplied criteria, and returns a count of occurrences found. If no cells meet criteria, COUNTIF returns zero.
The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range.
Any positive result means the value was found. By comparing the result with the greater than operator (>) and zero, we force a final result of TRUE or FALSE.
With IF
You can nest this formula inside the IF function as the logical test. For example, to return a final result of “Yes” or “No”, you can use IF like this:
=IF(COUNTIF(range,"*"&value&"*"),"Yes","No")
