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.