In B1:
Code:
=IFERROR(INDEX(Sheet1!$B$1:$B$8,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$8)),0),0)),"")
A 255 character limit on MATCH with wildcards was breached.
If the smaller strings you're looking for ever exceed 255 characters and it fails, comeback for another (longer) solution.