+ Reply to Thread
Results 1 to 3 of 3

Help with look up formula for a value containing in a string of text

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2016
    Posts
    18

    Help with look up formula for a value containing in a string of text

    Hi All,

    I am trying to look up in a column with a long string of text in each cell if it contains a specific value.
    If the value contains a match within the string, then return the string of text.

    The formula I tried to use was =VLOOKUP("*"&B2&"*",$C:$C,1,FALSE)
    It worked for one of my values, but not the other.
    In the sample data, I was able to find the value for MAP Creator, but not Promotions Allocator.


    Attached is sample data.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Help with look up formula for a value containing in a string of text

    i think the wildcard works for just 255 characters. it would work if your cell in A2 was:
    ence Query Tool|Auto Pulsing|Centralized Billing|Database Tools and Dashboards|CSM|DAM|E-Adjustment|FP&A|Kantar BI|LIFT|MAS500|MMBO|National TIM|OneTIM|Promotions Allocator|Spotlight Lead Portal|Spotlight Microstrategy|Spotlight Sharepoint|Spotlight-Storm

    try:
    =LOOKUP(99^99,SEARCH(B2,$C$2:$C$3),$C$2:$C$3)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Help with look up formula for a value containing in a string of text

    Put this on D2 and ENTERED as Array Formula then copied down:

    =IFERROR(INDEX(C$2:C$3,MATCH(FALSE,ISERROR(SEARCH(B2,C$2:C$3)),0)),"")
    Attached Files Attached Files

+ 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: 2
    Last Post: 05-27-2016, 12:30 AM
  2. Replies: 2
    Last Post: 11-18-2014, 06:26 AM
  3. Formula to find and return text string within a cell full of text strings
    By LightingPop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2013, 11:45 PM
  4. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 PM
  5. [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
  6. [SOLVED] Highlight a portion of string text (the string text is a value of a formula)
    By RaymondH in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2012, 06:50 AM

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