=SUMPRODUCT(--ISNUMBER(SEARCH(substrings,rng)))>0
If you need to test a range to determine if contains one of many substrings (partial matches, specific text, etc.) you can use use a formula that uses the SEARCH function, with help from the ISNUMBER, and wrapped in SUMPRODUCT.
How the formula works
For each substring you are looking for…
When a substring is found, the SEARCH function returns the position of a substring in text. If no match is found, SEARCH returns the #VALUE! error.
The ISNUMBER function returns TRUE for numbers and FALSE for anything else. So, if SEARCH finds a substring, it returns the position as a number, and ISNUMBER returns TRUE. When SEARCH returns the #VALUE! error, ISNUMBER returns FALSE.
Because both rng and substrings have multiple values, the result from SEARCH and ISNUMBER will be multiple values — an array of TRUE / FALSE values. The double negative forces the TRUE / FALSE values to 1 / 0 respectively, and SUMPRODUCT adds up the total result. Any positive number means that the formula found at least one substring in rng.
Case-sensitive option
SEARCH is not a case-sensitive function. If you need to check case as well, replace SEARCH with the FIND function like so:
=SUMPRODUCT(--ISNUMBER(FIND(substrings,rng)))>0
