Results 1 to 6 of 6

Thread: VLOOKUP - if column A contains text string then return new value to column B

  1. #1

    VLOOKUP - if column A contains text string then return new value to column B



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

    Hi all, spent nearly a day on this one and was hoping someone could help me out.

    I am currently doing some data management for a company and need to get a specific function working with Vlookup which should save a few days work.

    I currently have all the file names in a single column e.g.

    800GR_FOP.psd
    800GR_TOP.psd

    I need to be able to isolate the _FOP and _TOP part so that when it looks at my Vlookup table, it returns a value such as 'FOP Image' into a new column and 'TOP Image" into the same column (but horizontal to its original file name.

    There are a few variations, so maybe an OR function will come in handy e.g. _TOP, _BOP - what I am having trouble with is isolating multiple isolated parts of strings in order to return the desired value.

    Thanks

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    assuming data starts in A2, try this in B2 and drag down

    =MID($A2,FIND("_",$A2),FIND(".",$A2)-FIND("_",$A2))

  3. #3
    I tip my hat to you sir... Now I can Vlookup to return the desired value and presto! Thank you so much!

  4. #4
    So I need help on the next step of this if that is all good

    DATA (Column 1-3) Table (Column 4-5)

    File ID Type Name Type Name
    800GR_top.psd _top ? _top TOP Image
    800GR_fop.psd _fop ? _fop FOP Image
    800GR_BOP.psd _BOP ? _BOP BOP Image
    800GR_top.psd _top ?
    800GR_BOP.psd _BOP ?
    800GR_Prawn.psd _Prawn ?

    I need a formula which identifies the type in data (now possible thanks to NoS) and return a value in the Name Column. The values are in the table I have put next to it... so if I pull the formula down, any type e.g such as _Top, populates the cell next to it with TOP Image and so forth

    Any further help would be great Thanks,
    Ferg

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Did you try the VLOOKUP you identified?

    e.g.

    =VLOOKUP(B2,$D$2:$E$4,2,FALSE) copied down

    where B2 contains the first lookup value of _top and D2:E4 contains the lookup table.


  6. #6
    You guys are awesome... really appreciate the help with this one! Thanks so much - learning as I go! Hopefully will be able to contribute in the future!

    Cheers

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
  •