Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:K0
    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:K0), 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:

Formula:
=VLOOKUP("text string"Sheet2!K:B20
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).
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
function to find value and return cell reference rcc Excel Discussion (Misc queries) 6 June 27th 12 02:55 AM
Find max value in one column and return the value of corrosponding cell in different column [email protected] Excel Worksheet Functions 5 October 16th 07 12:33 PM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
use same cell w/in a function in a entire column. collegeboy28 Excel Discussion (Misc queries) 2 November 22nd 05 03:46 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"