EXCEL function;find string in entire column & return cell referenc

[Audit Compliance Man]

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

 

[Jacob Skaria]

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