Results 1 to 5 of 5

Thread: cell contains text where text is in table for look up

  1. #1
    Neophyte dtrinhexcel's Avatar
    Join Date
    Dec 2019
    Posts
    4
    Articles
    0
    Excel Version
    10

    Question cell contains text where text is in table for look up



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have a table of code to color description (2 columns) and I have a list of descriptions with each containing a string of text and within the text, it contains a color code. How can I use a lookup to reference the code-to-description table for the right of the string description to give me the color description? Currently, there's a formula that spells out explaining if(isnumber(search("code1",colorname), but there are more than 64 colors to look up so I need a way for it to look thru a table of color code and if it finds a match then populate it w/ the corresponding color description. Thank you for your time!

    example of data (columns):

    code-description table
    code (A) | description (B)
    1 blue | navy blue
    2 wht | pearl white


    list
    item description (A) | target value for color description (B)
    1 couch-blue-30ft | ???
    2 ottoman-wht-5ft | ???

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Try this

    =VLOOKUP(MID(E1,FIND("-",E1)+1,FIND("-",E1,FIND("-",E1)+1)-FIND("-",E1)-1),A:B,2,FALSE)

  3. #3
    Neophyte dtrinhexcel's Avatar
    Join Date
    Dec 2019
    Posts
    4
    Articles
    0
    Excel Version
    10
    Thanks. I tried the formula and got #n/a value. It maybe because the color code shows up sometimes after the 2nd, 3rd, or even 4th dash within the list of material description. How do you control the position of when to start in the text if it should be after the 3rd dash vs. the 4th dash within the "mid" formula? I might be able to count the # of dash to do an if statement.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Does it always occur before the last dash?

  5. #5
    Neophyte dtrinhexcel's Avatar
    Join Date
    Dec 2019
    Posts
    4
    Articles
    0
    Excel Version
    10
    yes, color code is contained before the last dash

Tags for this Thread

Posting Permissions

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