Lookup to Ranges that Dont Exact Match

[xentrix01]

I have two ecxell sheets in cell a1 of sheet 1 I have "Martin" and in b1 i have nothing. In sheet 2 i have cell a1 as Martin Is Here and cal b1 is 500.

How can i use a lookup formula to find martin in sheet two and then return cel b1 contents into sheet 1 b1 cell?

Sorry i havent really explained this well but i hope someone can help.

cheers

Martin

[NBVC]

You can use wildcards

e.g.

=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$B$100,2,0)