Match and return multiple results in one cell

[jeffingle]

I have a spreadsheet of students assigned to a specific teacher on a specific day for tutoring and I would like to create a single sheet that list all teachers individually in a row and then shows the students assigned to them for each day. I have tried VLOOKUP, MATCH, INDEX with no luck.

Data I am pulling from.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

MON	TUE	WED	THU	STUDENT
Mr. Smith	Mr. Smith	Library	Mrs. Jones	John Doe
Mrs. Jones	Mrs. Jones	Library	LIbrary	Jane Doe
Mr. Smith	Mr. Doe	Mrs. Jones	Comp Lab	John Smith

<colgroup><col style="width: 120px"><col width="120"><col width="120"><col width="120"><col width="120"></colgroup><tbody>
</tbody>

Desired Results
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

	MON	TUE	WED	THU
Mr. Smith	John Doe John Smith	John Doe
Mrs. Jones	Jane Doe	Jane Doe	John Smith	John Doe
Mr. Doe		John Smith
Library			John Doe Jane Doe	Jane Doe
Comp. Lab				John Smith

<colgroup><col style="width: 120px"><col width="120"><col width="120"><col width="120"><col width="120"></colgroup><tbody>
</tbody>

Thanks in advance for the help.

 

[Eric W]

If you have a newer version of Excel with the TEXTJOIN function, it's relatively easy:

	A	B	C	D	E
1	MON	TUE	WED	THU	STUDENT
2	Mr. Smith	Mr. Smith	Library	Mrs. Jones	John Doe
3	Mrs. Jones	Mrs. Jones	Library	LIbrary	Jane Doe
4	Mr. Smith	Mr. Doe	Mrs. Jones	Comp. Lab	John Smith
5
6
7	Desired Results
8		MON	TUE	WED	THU
9	Mr. Smith	John Doe, John Smith	John Doe
10	Mrs. Jones	Jane Doe	Jane Doe	John Smith	John Doe
11	Mr. Doe		John Smith
12	Library			John Doe, Jane Doe	Jane Doe
13	Comp. Lab				John Smith

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula B9 {=TEXTJOIN(", ",TRUE,IF(A$2:A$4=$A9,$E$2:$E$4,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in B9, change the ranges to match your sheet, then confirm by pressing Contol+Shift+Enter. Then drag down and to the right as needed.

If you don't have TEXTJOIN, you'll need to use VBA in some fashion.

Let me know how this works for you.