Concatenate vlookup results

[jdgel817]

How do I accomplish this with a formula? I need the vlookup results to be concatenated into a single cell. Thanks.

[TABLE="width: 387"]
<tbody>[TR]
[TD="colspan: 2"]Initial Table[/TD]
[TD][/TD]
[TD="colspan: 2"]Lookup Results[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Class[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Class1[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD]Class1[/TD]
[TD]Bob, Lily, Alice[/TD]
[/TR]
[TR]
[TD]Class2[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]Class2[/TD]
[TD]Jane, David, Sara[/TD]
[/TR]
[TR]
[TD]Class3[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Class3[/TD]
[TD]John, Lucy[/TD]
[/TR]
[TR]
[TD]Class2[/TD]
[TD]David[/TD]
[TD][/TD]
[TD]Class4[/TD]
[TD]Sally, James[/TD]
[/TR]
[TR]
[TD]Class4[/TD]
[TD]Sally[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class1[/TD]
[TD]Lily[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class1[/TD]
[TD]Alice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class2[/TD]
[TD]Sara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class4[/TD]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class3[/TD]
[TD]Lucy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

You will need Excel2016 for this formula to work with the new function TEXTJOIN.

Book1
	A	B	C	D	E
1	Initial Table			Lookup Results
2	Class	Name		Class	Name
3	Class1	Bob		Class1	Bob, Lily, Alice
4	Class2	Jane		Class2	Jane, David, Sara
5	Class3	John		Class3	John, Lucy
6	Class2	David		Class4	Sally, James
7	Class4	Sally
8	Class1	Lily
9	Class1	Alice
10	Class2	Sara
11	Class4	James
12	Class3	Lucy
Sheet57

Cell Formulas
Range	Formula
E3	{=TEXTJOIN(", ",,IF($A$3:$A$12=D3,$B$3:$B$12,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.