+ Reply to Thread
Results 1 to 10 of 10

Search a string of text for values from a list, and return adjacent value

  1. 04-01-2021, 04:13 PM #1
    Carpp
    Carpp is offline
    Registered User
    Join Date
    04-01-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    Office 2016
    Posts
    5

    Search a string of text for values from a list, and return adjacent value

    Hello!

    I'm looking for a formula that will search a cell containing a string of words for a specific phrase from a list, and then return the value adjacent to the phrase from the list that is found. If no phrase is found then return ""

    the 'Test List' column are the phrases im looking for. I'm searching for these phrases in the column in sheet2. If the phrase is found, return the value adjacent to it (Sheet1 D column)

    Thoughts?

    Thanks
    Attached Files Attached Files
    Last edited by Carpp; 04-02-2021 at 06:26 PM.
    Reply With QuoteRegister To Reply
  2. 04-01-2021, 05:49 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,853

    Re: Search a string of text for values from a list, and return adjacent value

    Welcome to the forum.

    Try this:

    =IFERROR(LOOKUP(1000,SEARCH(Sheet1!$C$4:$C$12,A1),Sheet1!$D$4:$D$12),"")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-02-2021, 12:16 PM #3
    Carpp
    Carpp is offline
    Registered User
    Join Date
    04-01-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Search a string of text for values from a list, and return adjacent value

    It worked, thanks! - The "1000" is referencing Sheet1 Column C and instructing the formula on how many cells to search through?
    Reply With QuoteRegister To Reply
  4. 04-02-2021, 12:19 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,853

    Re: Search a string of text for values from a list, and return adjacent value

    No, it's just a suitably high number!

    I can't explain exactly how it works, sorry, but that 1000 will always work with text.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  5. 04-02-2021, 02:57 PM #5
    Carpp
    Carpp is offline
    Registered User
    Join Date
    04-01-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Search a string of text for values from a list, and return adjacent value

    Ok thanks I will do that. One more question - in reference to the original question, I've repalced the "Test List" with numerical values and would like the same functionality in returning the adjacent cell. The current formula returns 0. Any thoughts? Is there a way to specify it has to contain that specific number to return the desired value?
    Reply With QuoteRegister To Reply
  6. 04-02-2021, 04:48 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,853

    Re: Search a string of text for values from a list, and return adjacent value

    Provide a sample workbook and I’ll have a look. Can’t tell from your description.

    I would always recommend that you provide realistic sample data at the outset: we offer solutions based in the data types you share.
    Reply With QuoteRegister To Reply
  7. 04-02-2021, 06:29 PM #7
    Carpp
    Carpp is offline
    Registered User
    Join Date
    04-01-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Search a string of text for values from a list, and return adjacent value

    Thanks! - Book2 attached. Same question as initial post just with different search values to return adjacent cell.

    "I'm looking for a formula that will search a cell containing a string of words for a specific phrase from a list, and then return the value adjacent to the phrase from the list that is found. If no phrase is found then return ""

    the 'Search Value' column are the phrases im looking for. I'm searching for these phrases in the column in sheet2. If the phrase is found, return the value adjacent to it (Sheet1 D column)"

    I appreciate all your help!
    Reply With QuoteRegister To Reply
  8. 04-03-2021, 02:53 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,853

    Re: Search a string of text for values from a list, and return adjacent value

    Just put my formula into the new workbook and it works the same.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-03-2021, 09:19 AM #9
    Carpp
    Carpp is offline
    Registered User
    Join Date
    04-01-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Search a string of text for values from a list, and return adjacent value

    Looks like making it an array did the trick, solved!

    Thanks for your help!
    Reply With QuoteRegister To Reply
  10. 04-03-2021, 09:24 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,853

    Re: Search a string of text for values from a list, and return adjacent value

    Yes, indeed - sorry, I should have mentioned that, but didn't spot that your version is 2016.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] search for string, return adjacent value
    By georgiabrown in forum Excel General
    Replies: 7
    Last Post: 07-12-2018, 09:01 AM
  2. [SOLVED] Search for a text string and return given text string to adjacent cell
    By hecgroups in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2015, 04:50 AM
  3. Search for a text string and return adjacent cell value for each match
    By ral8088 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 09:55 AM
  4. Simple search for text from list, return adjacent text - match / vlookup / index?
    By aceparts in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 01:09 PM
  5. Search a range for instances of a text string, return neighboring cell values each time
    By excelroofing in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-03-2012, 03:09 AM
  6. Excel 2007 : How to search text string within a column and return search value in adjacent column
    By johannes121 in forum Excel General
    Replies: 1
    Last Post: 07-26-2011, 06:48 AM
  7. Search text string for range of text values - return match
    By crugg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2010, 09:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1