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").

Range("B2").FormulaR1C1 = "=VLOOKUP(C[-1],AddinName.xlam!ProductIDRef[#Data],2,0)"

Any ideas?

Cheers.

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

I made a function that can do this:

Public Function ZLOOKUP(rngInput As Range, rngCompare As Range) As String

Dim varStr
Dim tmp         As String
Dim it          As Variant
Dim rngCompCell As Range

For Each varStr In Split(rngInput, ";")
    For Each rngCompCell In rngCompare
        If rngCompCell = varStr Then
            If tmp = "" Then
                tmp = rngCompCell.Offset(0, 1)
            Else
                tmp = tmp & ";" & rngCompCell.Offset(0, 1)
            End If
        Exit For
        End If
    Next
Next

ZLOOKUP = tmp

End Function

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])