Text Search in an Array

[Guest]

The SEARCH and FIND functions in Excel allow me to find text strings within a
single cell. Is there a function that would allow me to search for a text
fragment in an array of cells? The text fragment may be embedded in a longer
string (i.e., it would likely not be an exact match), so I don’t think that
the LOOKUP functions could be employed.

 

[Dave Peterson]

You can use wildcards with your =match() formula:

=isnumber(match("*" & "somestring" & "*", a1:A999, 0))

or

=countif(a1:x99,"*" & "somestring" & "*")

If you're working with strings, you could use "*somestring*". But if you're
holding that value in a cell, you can just replace "somestring" with that cell's
address:

=countif(a1:x99,"*" & z99 & "*")