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

  • Thread starter Audit Compliance Man
  • Start date
A

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top