Excel how to create reference and list the names?

Question

So I have a small table with three columns.

• First column is a identifier (ID)
• Second column is a reference identifier (List of ID's)
• Third column is the name which identifies the identifier
• Fourth column should be a calculated column which basically lists the names of the reference ID.

My question is, what should be formula for the fourth column to achieve the result below automatically? When the first three columns are given.

question from:https://stackoverflow.com/questions/65841315/excel-how-to-create-reference-and-list-the-names

Answer 1

If you have Excel365 then could achieve in this way. For older version VBA macro would be best approach.

=TEXTJOIN(", ",TRUE,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(B2,", ","</s><s>")&"</s></t>","//s"),$A$2:$A$4,$C$2:$C$4))