Assuming your data in Sheet2 spans to 1000 rows.. try the below

The below will return the row
=MATCH("*" & A1 & "*",Sheet2!K1:K1000,0)

Then you can use INDEX() to return the value from B
=INDEX(sHEET2!b1:b1000,MATCH("*" & A1 & "*",Sheet2!K1:K1000,0))

If this post helps click Yes
---------------
Jacob Skaria


"Audit Compliance Man" wrote:

I need a function to search an entire column on another sheet (K:K) for a
text string that returns the first cell reference (K34) that contains the
string. Then I can use VLOOKUP to get a second value contained in, say, B34