Transform comma separated ID's into comma separated Names in Excel 2017/10 using lookup table

Question

I'm trying to match comma separated ID Data to the LOOKUP table and return the NAMEs of the IDs also as comma separated.

Note that if there is no such ID it should just ignore it as in S4.
The IDs can vary up to 10 characters in length and contain symbols
The Name can be up to 256 characters in length
The amount of comma separated ID's in the DATA column cells can be up to 100.

I'm basically trying to transform the ID's into Names

The solution can be in either VBA or an Excel formula, whichever is easier. I not very good at either, but the formula is the best I have come up with, although you can see it has problems with missing ID's and only checks 3 ID's.

Any help would be most appreciated to achieve the required output result.

Example table in the Code Snippet below

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LOOKUP ID</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LOOKUP NAME</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ID DATA</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">Formula NAME OUTPUT</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Required NAME OUTPUT</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ABCDE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2,S6,S7,S4,S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">ABCDE,ADE,AB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ABCDE,ADE,AB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">AB</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2,S4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ADE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G9,S6,G2,S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">GPES,ADE,AB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">GPES,ADE,D,AB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">!TE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2,G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">D,GPES,GPES</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D,GPES</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GPES</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2,G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">ABCDE,GPES,GPES</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ABCDE,GPES</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">&ALL</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ALL</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">&ALL</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ALL</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">TRIM(<font color="Red">LEFT(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20</font>)</font>),$A$2:$B$8,2,FALSE</font>)&","&VLOOKUP(<font color="Blue">TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20,20</font>)</font>),$A$2:$B$8,2,FALSE</font>)&","&VLOOKUP(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20</font>)</font>),$A$2:$B$8,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />

Wrap each vlookup in an IFERROR(...,"") – Scott Craner Jun 10 '20 at 20:29 — Scott Craner, Jun 10 '20 at 20:29

Ron Rosenfeld · Accepted Answer · 2020-06-11T11:39:50.037

In the formulas below, I used a Table with structured references for the lookup table, but you can convert that to regular addresses if you prefer.

If you have Excel O365 with the SEQUENCE function, you can use:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(Table3[NAME],MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),99)),Table3[ID],0)),""))

If you have Excel 2016 with the TEXTJOIN function, you can use:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(Table3[NAME],MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99)),Table3[ID],0)),""))

where seq is a Named Formula that refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,99))

If you have an earlier version of Excel lacking the TEXTJOIN function, a VBA solution would be best, although you could do it with multiple helper columns.

For those with earlier versions of Excel, here is a VBA routine that should work efficiently.

Since there could be up to 100 matches in the ID DATA column, I thought it might be faster to read the lookup table into a dictionary, instead of looping through the arrays each time. (Previous testing has shown that for single matches, looping a VBA array is faster than Worksheetfunction.Match and Range.Find methods, but I have not tested this for multiple matches.)

Option Explicit

'Go to `tools-->references` in the menu above and
'Set a reference to Microsoft Scripting Runtime for Dictionary

'Since there can be up to 100 items in the list,
'probably faster to read the table into a dictionary
'than to loop through the list for each item

Function getNames(ID_DATA As String, lookup_Table As Range) As String
    Dim D As Dictionary
    Dim I As Long
    Dim S As String
    Dim V As Variant, tbl As Variant

    'delimiter in the output string
    Const sDelim As String = ", "

'read table into dictionary for lookup
tbl = lookup_Table
Set D = New Dictionary
    D.CompareMode = TextCompare
For I = 1 To UBound(tbl)
    'will throw error if there are duplicate ID's
    'but these should be corrected anyway
    D.Add Key:=tbl(I, 1), Item:=tbl(I, 2)
Next I

'create the output string
S = ""
For Each V In Split(ID_DATA, ",")
    S = S & IIf(D(V) = "", "", sDelim & D(V))
Next V

getNames = Mid(S, Len(sDelim) + 1)

End Function

Sorry Typo in the heading. I use 2007 or 2010. All these examples are for much higher versions. — BradS, Jun 11 '20 at 07:51
Sorry my VBA knowledge is very limited. I have copied the function to VBA and my table is in Sheet1 as shown above. How do I get the function to work on the table in sheet1 to create the required output? — BradS, Jun 11 '20 at 18:20
@BradleySieve You use this function as you would any other function -- supplying the appropriate arguments. I used names for the arguments that should make it evident what to use there. — Ron Rosenfeld, Jun 11 '20 at 19:20
For example, in the example above, you might enter in `E2:` `=getNames(D2,$A$2:$B$7)` — Ron Rosenfeld, Jun 11 '20 at 19:32
You learn something new every day!. I never knew you could call a VBA function from within a cell on the sheet. It works fantastic! I have spent so long on this I didn't think it would be possible, but you have done it. Thank you so much. — BradS, Jun 11 '20 at 19:40
if ID is a number, it would not match. `=TEXTJOIN(", ",TRUE,IFERROR(INDEX([NAME],MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),99)),TEXT([ID],"0"),0)),""))` will be ok. — Jannchie, Nov 19 '21 at 02:40
@Jannchie Yes, that would work for numeric entries entered as numbers. (If they were entered as text, or if the column was formatted as text before entering the digits, there'd be no need). Thanks for adding that caveat. — Ron Rosenfeld, Nov 19 '21 at 02:55

score 0 · Answer 2 · answered Jun 10 '20 at 21:31

Try the following (assuming your expected results in E2 are wrong):

Formula in E2:

=TEXTJOIN(",",1,IF(A$2:A$8=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(D2,"&","&amp;"),",","</s><s>")&"</s></t>","//s")),B$2:B$8,""))

^{Note1: This formula needs to be entered through CtrlShiftEnter}

^{Note2: Since & is a special character in XPATH we need to "escape" it with a second SUBSTITUTE.}

^{Note3: If you are interested in FILTERXML, you might find this interesting?}

Transform comma separated ID's into comma separated Names in Excel 2017/10 using lookup table

2 Answers2