=LOOKUP(Sheet2!A2:A10,"*"&C1&"*",Sheet2!B2:B10)
This resides in Sheet1, C1 is a cell that I type the search 'target' into,
so it changes as needed. I get a #VALUE! error.