=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.

Hi
use
=VLOOKUP("*"&C1&"*",'sheet2'!A2:B10,2,0)

--
Regards
Frank Kabel
Frankfurt, Germany

curtis_tx wrote:
=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.