+ Reply to Thread
Results 1 to 4 of 4

search text within column and return another column value

  1. #1
    Registered User
    Join Date
    03-18-2012
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    42

    search text within column and return another column value

    hi,

    i'm stuck with search function and have tried various formulas throughout this forum and can't come up with a solution

    my issue:

    i have two sheets (sheets 1 and 2) i like to search the text in A1 to a column in sheet 2. if it can find the text in the column then return the results in column A of sheet 2.

    example i'm searching 10Apple in column B of sheet 2. 10Apple text is located with the text of B1 so then it will return the results from A1. the results to return would be BIN10

    sheet 1:
    column A
    A1. 10Apple
    A2. 20orange
    A3. 30banana
    A4. 55plum
    A5. 70grapes

    sheet 2:
    column A Column B
    A1. BIN10 B1. 10AppleGrocery
    A2. BIN50 B2. 10store99
    A3. BIN20 B3. 20orangestore
    A4. BIN20 B4. 20storeB
    A5. BIN50 B5. 30bananastoreC
    A6. BIN10 B6. 55plumstoreC
    A7. BIN50 B7. 70grapesstoreE
    A8. BIN20 B8. 80canada
    A9. BIN10 B9. 90USA

    i'm hoping someone can help me out with this as the search text is contained within the text.

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: search text within column and return another column value

    I put this formula in col B of Sheet 1 and copied it down and it seems to work

    =INDEX(Sheet2!A:A,MATCH(Sheet1!A1&"*",Sheet2!B:B,0))
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    03-18-2012
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: search text within column and return another column value

    awesome thanks that worked.

  4. #4
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: search text within column and return another column value

    glad I could help....

    If this has been completed to your satisfaction, please set the thread to Solved...If you feel I deserve an increase in my reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be me...just a thought...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to search for text within a column and return a value in a separate column
    By hulayogi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2013, 10:34 PM
  2. Replies: 2
    Last Post: 12-14-2012, 11:45 PM
  3. Help:Macro to search a text in each column and return value
    By rama_126 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 03:15 PM
  4. [SOLVED] Return the lowest value in one column based on a text search in a different column.
    By Juliana33 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-11-2012, 09:58 AM
  5. Replies: 1
    Last Post: 09-01-2011, 12:05 AM
  6. Replies: 1
    Last Post: 07-26-2011, 06:48 AM
  7. How to search text x 2 in same column and return 2nd result
    By Maxwell9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2010, 11:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1