Find Text in Range

Frank Kabel on the Daily Dose of Excel by Dick Kusleika, http://www.dicks-blog.com

Lookup text is in cell A1, Range to search is in cells A1:A10

=COUNTIF($A$1:$A$10,"*"&A1&"*")