+ Reply to Thread
Results 1 to 6 of 6

Return value from a table when value is part of a text string

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    Downers Grove
    MS-Off Ver
    Excel 2007
    Posts
    4

    Return value from a table when value is part of a text string

    I have a list of product descriptions in Column B (starting at B3) and each of these product descriptions contains a text string. I've created a table of the text stings I'm searching for in Column F. I would like to create a formula that will search the list in Column B for the text string in the table I've created in Column F and then if found, populate Column C with that text string found in Column F.

    I've attached a sample of the workbook I'm working in. I've come up with the array formula {=IF(ISNUMBER(SEARCH($F$3:$F$11, B3)), $F$3:$F$11, "")} but it doesn't work for every row in column B. It only works if the test string I'm searching for is in the first cell of the table in column F.

    Can anyone help me out with making this formula work?
    Attached Files Attached Files

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

    Re: Return value from a table when value is part of a text string

    Try this Array formula in C3.

    Please Login or Register  to view this content.
    and then drag down.
    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.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Return value from a table when value is part of a text string

    Try this one

    =IFERROR(LOOKUP(2^15,SEARCH($F$3:$F$11,B3),$F$3:$F$11),"")

    B
    C
    2
    Product Description
    University Name
    3
    CANCELLED CPA WK04 UNIV OF TENNESSEE REG 01/20/2014
    UNIV OF TENNESSEE
    4
    CANCELLED CPA WK23 UNIV OF TENNESSEE FIN 06/02/2014
    UNIV OF TENNESSEE
    5
    CANCELLED/CHANGED CPA WK40 SULLIVAN UNIV ONLY ARFB-B 07/01/2013
    SULLIVAN UNIV
    6
    CHANGED CPA WK35 UNIV OF MISSOURI FIN 08/26/2013
    UNIV OF MISSOURI
    7
    CPA WK02 AUBURN UNIV ONLY FIN 01/06/2014
    AUBURN UNIV
    8
    CPA WK02 KGSM AUD 01/06/2014
    KGSM
    9
    CPA WK02 KGSM FIN 01/06/2014
    KGSM
    10
    CPA WK02 KGSM REG 01/06/2014
    KGSM
    11
    CPA WK02 LAKELAND AUD 01/06/2014
    LAKELAND
    12
    CPA WK02 LAKELAND FIN 01/06/2014
    LAKELAND
    13
    CPA WK02 LAKELAND REG 01/06/2014
    LAKELAND
    14
    CPA WK02 SULLIVAN UNIV ONLY R 01/06/2014
    SULLIVAN UNIV
    15
    CPA WK02 UNIV OF AL-TUSCALOOSA ONLY AUD 01/06/2014
    UNIV OF AL-TUSCALOOSA
    16
    CPA WK04 KGSM BUS 01/20/2014
    KGSM
    17
    CPA WK06 AUBURN UNIV ONLY AUD 02/03/2014
    AUBURN UNIV
    18
    CPA WK07 UNIV OF MISSOURI FIN 02/10/2014
    UNIV OF MISSOURI
    19
    CPA WK08 LAKELAND BUS 02/17/2014
    LAKELAND
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    05-14-2013
    Location
    Downers Grove
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Return value from a table when value is part of a text string

    Wow!! Thanks sktneer!! That worked! Can I trouble you to explain the formula - I'm a bit lost and would love to learn how this works?

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    Downers Grove
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Return value from a table when value is part of a text string

    Thanks AlKey!! Your solution worked great also! Thanks so much. You and sktneer both came up with different formulas that worked. I'm having a little bit of trouble understanding your formula - can you please explain it to me? I haven't used an Iserror function before and I don't understand the 2^15?

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Return value from a table when value is part of a text string

    Hi Shorrocks and thank you for the feedback!

    here is an explanation of 2^15 by Bernie Deitrick

    http://answers.microsoft.com/en-us/o...c-68b599b31bf5


    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

+ 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] Can I remove the first part of a string of text and keep the second part?
    By Whoop92 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 02-12-2014, 07:44 PM
  2. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  3. Replies: 5
    Last Post: 04-08-2012, 01:06 AM
  4. Mid function to return part of String
    By stoney1977 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2008, 07:25 PM
  5. function to return part of a string
    By sarabella in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2007, 03:00 PM

Tags for this Thread

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