Is there a simple way to search within a large text string to see if at least one short text string from a list is present? I do not want to have to test for each of the short strings individually inside an OR() function.
For example, I want to search a fast food restaurant address in cell B1 to see if it contains any of the 10 string fragments: "Burger Kin", "acdonald's", ..., or "aco Bell" tabulated in cells C11:C20.
I've tried using array formulas like {=SEARCH(C11:C20,B1)} and {=MATCH("*"&C11:C20&"*",B1)}, but they only work for the first item in the list. I've also tried incorporating OR() functions in the array formulas, but they do not work with variables (references to locations in the table, rather than the strings themselves).
Bookmarks