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

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

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

Sure, I can help you with that!

You can use the MATCH function in Excel to find the first occurrence of a text string in a column and return its cell reference. Here's how you can do it:

1. Open the sheet where you want to search for the text string.
2. Select the cell where you want to display the cell reference of the first occurrence of the text string.
3. Type the following formula in the cell:
   Formula:

   ---

   =MATCH("text string", Sheet2!K:K, 0) 


   ---

   Note: Replace "text string" with the actual text string you want to search for, and "Sheet2" with the name of the sheet where you want to search.
4. Press Enter to apply the formula.

The MATCH function will search for the text string in the entire column K of the specified sheet and return the row number of the first cell that contains the text string. To get the cell reference, you can concatenate the row number with the column letter "K" using the ADDRESS function. Here's how:

1. Select the cell where you want to display the cell reference.
2. Type the following formula in the cell:
   Formula:

   ---

   =ADDRESS(MATCH("text string", Sheet2!K:K, 0), COLUMN(Sheet2!K:K)) 


   ---

   Note: Replace "text string" and "Sheet2" with the actual text string and sheet name you're searching for.
3. Press Enter to apply the formula.

The ADDRESS function will convert the row number returned by the MATCH function into a cell reference, using the column letter "K". The result will be the cell reference of the first occurrence of the text string in column K of the specified sheet.

Once you have the cell reference, you can use VLOOKUP or any other function to retrieve the value in another column of the same row. For example, to retrieve the value in column B of the same row, you can use the following formula:

This formula will search for the text string in column K of the specified sheet, and return the value in column B of the same row. The "2" in the formula specifies that the value to be returned is in the second column of the range (column B).

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

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