+ Reply to Thread
Results 1 to 4 of 4

vLookup multiple values separated by ";" in single cell / return result in single cell

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Europe
    MS-Off Ver
    Excel 2013/365
    Posts
    24

    vLookup multiple values separated by ";" in single cell / return result in single cell

    Hi,

    I have a workbook that contains a list of IDs in a single column, there can be multiple IDs in a single cell in which case they are separated by a semicolon.
    There's no real upper limit on how many IDs can be in a single cell (outside of Excel's 32,767 character limit I suppose) and there may be 100s of rows of IDs and some of them may just be single IDs per row.

    Example:

    ProductIdList
    OFR.00001;OFR.00002;OFR.00003
    OFR.00001

    In a separate Excel Addin I have a table named ProductIDRef that lists the IDs and the Display Name associated with them

    Offer ID Display Name
    OFR.00001 Product 1
    OFR.00002 Product 2
    OFR.00003 Product 3

    I'm looking for a vLookup or Function to look up the IDs' Display Name and put the results in a column next to the ProductIdList like this:

    ProductIdList ProductNameList
    OFR.00001;OFR.00002;OFR.00003 Product 1;Product 2;Product 3
    OFR.00001 Product 1

    I've attached an example workbook where the table that normally sits in a separate addin is in Sheet2.

    To reference that table in the addin I normally use the following but this only works for cases where there is only one ID per row (e.g. in "A2").

    Please Login or Register  to view this content.
    Any ideas?

    Cheers.
    Attached Files Attached Files
    Last edited by Tunesmith; 09-07-2015 at 12:02 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: vLookup multiple values separated by ";" in single cell / return result in single cell

    Not looked at your workbook but you would use Split on each cell to create an array and loop through the elements of the array to return the relevant values. You can concatenate the entries and output them to the worksheet cell.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: vLookup multiple values separated by ";" in single cell / return result in single cell

    I made a function that can do this:

    Please Login or Register  to view this content.
    And coincidentally, I built pretty much what TMS was talking about. Glad I was on the right track.

    Using this, I put B2:

    =ZLOOKUP(A2,ProductIDRef[Offer ID])
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    08-02-2012
    Location
    Europe
    MS-Off Ver
    Excel 2013/365
    Posts
    24

    Re: vLookup multiple values separated by ";" in single cell / return result in single cell

    That's perfect, thank you for the help both of you. I wasn't familiar with Split.

+ 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. Multiple vlookup "lookup values" in a single cell?
    By catccc in forum Excel General
    Replies: 8
    Last Post: 10-19-2015, 05:10 PM
  2. [SOLVED] codes for copy multiple cell and paste it in single cell " like concatenate"
    By ash3angel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2015, 12:52 PM
  3. Replies: 3
    Last Post: 04-03-2014, 08:14 PM
  4. vlookup multiple values in single cell separated by spaces
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 12-15-2013, 10:23 PM
  5. Two name comma separated in single cell to "email address"
    By jf44 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-06-2013, 10:51 PM
  6. Joining 2 "VLOOKUP" Formulas to get a single result?
    By JES65FLA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-29-2011, 12:50 PM
  7. Options marked with an "X" listed in a single cell separated by commas
    By jpecor2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-13-2011, 02:06 PM

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