+ Reply to Thread
Results 1 to 9 of 9

How to compare partial text in two columns and return value from adjacent cell

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    How to compare partial text in two columns and return value from adjacent cell

    Hi,

    I have a string of text in a column that I would like to compare with text from another worksheet, and return a value from an adjacent cell in the second worksheet.

    So for instance:
    -In Sheet1, cell A1 contains text "Bob built a house in Georgia"
    -I would like to compare A1 to a column from a second worksheet and find a cell that matches text with the string found Sheet1A1, ie Sheet2A4 contains "Georgia,"
    -If Sheet2A1 matches partial text found in Sheet1A1, I would like to return the value found in a cell adjacent to Sheet2A1 ie Sheet2B4 or "1988".

    Sorry for probably not writing this as clear as it can be. Any help would be appreciated!
    Attached Files Attached Files
    Last edited by Ghys772; 05-06-2014 at 11:29 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: How to compare partial text in two columns and return value from adjacent cell

    Is the state always the last word in the text string?

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to compare partial text in two columns and return value from adjacent cell

    No. It could be "built Bob Georgia a house"

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: How to compare partial text in two columns and return value from adjacent cell

    On sheet1, in the cell B1, try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

    Please Login or Register  to view this content.
    and then drag down.

    For detail see the attached sheet.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    05-06-2014
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to compare partial text in two columns and return value from adjacent cell

    Hi sktneer,

    Thank you so much for your response! I tried applying the formula but it doesn't seem to work. This may be my fault, as the example I provided expected to return numerical values from Sheet2B, when the actual worksheet I am trying to format contains text values.

    I have updated my original question with a new version of the sample file that does not contain any numerical values. Very much appreciate your help!

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: How to compare partial text in two columns and return value from adjacent cell

    The formula will return value irrespective of the format. And since the formula is an array formula so you need to confirm it with Ctrl + Shift + Enter.
    Select the cell which has the formula, press F2 (function key) and then hold down the Ctrl + Shift and then press Enter.

    For detail see the attached sheet.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-06-2014
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to compare partial text in two columns and return value from adjacent cell

    Thanks sktneer, this time it worked- thank you so much!!

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: How to compare partial text in two columns and return value from adjacent cell

    You're welcome. Thanks for the feedback.
    Since you are new to the forum so just to tell you that, if that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

  9. #9
    Registered User
    Join Date
    05-06-2014
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How to compare partial text in two columns and return value from adjacent cell

    Done- thank you for the forum tips!

+ 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. Replies: 6
    Last Post: 02-28-2013, 11:27 AM
  2. Replies: 3
    Last Post: 02-15-2012, 01:18 AM
  3. Replies: 2
    Last Post: 02-27-2011, 11:52 PM
  4. Replies: 20
    Last Post: 01-02-2011, 10:00 PM
  5. Compare values in two columns and return text from adjacent cell
    By MrBorders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-04-2009, 04:54 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