CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Nikhil Joseph
Nikhil Joseph

Reputation: 51

Array Formula for text search

I have used this array formula to find true/false. The formula is only considering the text in the first cell

{=isnumber(search(G2:G7,A1))}

Upvotes: 1

Views: 845

Answers (1)

user4039065
user4039065

Reputation:

You need to provide an extra layer of processing so that the formula does not halt at the first result of the ISNUMBER function. A SUM function wrapper should be sufficient.

=SUM(--ISNUMBER(SEARCH(G2:G7, A1)))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Any non-zero result will mean that at least one of the terms in G2:G7 is within A1.

The CSE could be avoided by substituting a SUMPRODUCT function for the SUM.

=SUMPRODUCT(--ISNUMBER(SEARCH(G2:G7, A1)))

The SEARCH function is not case-sensitive; the FIND function is case-sensitive.

Upvotes: 2

Related Questions