Excel search function with a list of things - array behaves weird?

user_14059289 0
Viewed 286

I have a list of substrings and need to check if the text string in my cell contains any of these substrings. Exactly like here: https://exceljet.net/formula/cell-contains-one-of-many-things

However, the ISNUMBER(SEARCH(MySubstrings,A3)) behaves strangely. Instead of checking A3 against a list of substrings like ISNUMBER(SEARCH({dog, cat, egg},A3)) it automatically populates three subsequent cells to show results for checking A3 against every item on my list, one by one.

search without curly brackets

And when I enter the same thing as an array formula, it only checks for occurrence of the first substring from my list, in this case "dog".

search with curly brackets

Why does this not work? Thanks for your hints!

excel-2010 excel-formula
1 Answers

You're missing the SUMPRODUCT function as in the exceljet example. The argument passed to SUMPRODUCT is an array (in your example, a 1-by-3 array of TRUE or FALSE), which SUMPRODUCT turns into a single value (0 or 1):

=SUMPRODUCT(--ISNUMBER(SEARCH({"dog","cat","egg"},A3)))>0

An alternative formulation would be to wrap ISNUMBER with the OR function (still no need to enter as an array):

=OR(ISNUMBER(SEARCH({"dog","cat","egg"},A3)))
user_94634890
Related