Parse delimited cells, use results in lookup, concatenate

[porear]

Sorry this is a compound problem but I am not sure how to solve each step with a method producing an output compatible with input types for the next piece.

I need to parse comma delimited numbers (stored as text) in cells like those in col C below. I then need to look up each of the parsed numbers against col A, retrieve the corresponding data from col B, and return all results in column E (separated by carriage returns). All data is text format.

I know I can read in the delimited data with Split, but not sure how to proceed with the lookup from there, as I don't know how (or if it is possible) to then pass that array to a VLOOKUP function. Maybe that is the wrong approach anyway...

Any help is appreciated, thanks in advance.


Excel 2010
<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ID</TD><TD>Desc</TD><TD>Cross Ref</TD><TD style="TEXT-ALIGN: right"></TD><TD>Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>42</TD><TD>Data1</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>54</TD><TD>Data2</TD><TD>1,42</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data4
Data1

</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>777</TD><TD>Data3</TD><TD>42,54,83</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data1
Data2
Data5

</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>1</TD><TD>Data4</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>83</TD><TD>Data5</TD><TD>54</TD><TD style="TEXT-ALIGN: right"></TD><TD>Data2</TD></TR></TBODY></TABLE>

Sheet1​

 

[GTO]

It was already covered.

(Take off my glasses, rub eyes, put glasses back on... "Criminy! How do I miss such things?!)

 

[porear]

No apologies necessary, GTO. Thanks for yet another alternative Rick, it works very well as well.

I will probably need to modify this slightly for some other very similar tasks, so I am fairly sure that I may find more than one of these options suitable for all of my needs. Also not sure yet how large the overall data set will grow, so speed could become an issue before all is done.

Thanks very much again to all, I'm very impressed and pleased with the quality and quickness of responses here.