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>

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>
[TD="align: center"]MON[/TD]
[TD="align: center"]TUE[/TD]
[TD="align: center"]WED[/TD]
[TD="align: center"]THU[/TD]
[TD="align: center"]STUDENT[/TD]

</tbody>

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

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>
[TD="align: center"]MON[/TD]
[TD="align: center"]TUE[/TD]
[TD="align: center"]WED[/TD]
[TD="align: center"]THU[/TD]

</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
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
Desired Results
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: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]MON[/TD]
[TD="align: center"]TUE[/TD]
[TD="align: center"]WED[/TD]
[TD="align: center"]THU[/TD]
[TD="align: center"]STUDENT[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"]MON[/TD]
[TD="align: center"]TUE[/TD]
[TD="align: center"]WED[/TD]
[TD="align: center"]THU[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(A$2:A$4=$A9,$E$2:$E$4,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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.