Or some others....

Note this is an exact reference, use FALSE. This will return the value
(text) in the first cell it finds with "smith":
=VLOOKUP("*smith*",A1:A100,1,FALSE)

Try MATCH instead if you are looking for a reference. This will return
the row number of that cell:
=MATCH("*smith*",A1:A100,0)