Sumproduct, Combine if multiple results

[traggs25]

My formula is on google sheets but I think that this is more of a formula question and doesn't really matter if it is Sheets or Excel. Hope this is the correct place to post.

I have a sheet that ALMOST does exactly what I need.

In the sheets "SS", "Sci", "ELA", "Math" I have data about individual teachers with the rooms and periods they teach. I use the sheet "Scrap" to pull off all the information from those 4 sheets into one and retype the periods into individual days. Lastly, on the "Rooms" sheet I want to see which rooms are used during each period AND what teacher uses that room at that time.

https://docs.google.com/spreadsheets...it?usp=sharing

This is a small sample of what I end up with which works GREAT!!!
At the moment, what happens is that if there is 1 match then it returns a name but >1 match returns a number.

Is there a way to add on to this formula in order to make it so that when there is >1 match it will concatenate the names together (maybe with "/" between the names).

(for example on the sheet "Rooms" instead of G24 saying "2" I'd want it to say "Jim/Sam". I want to be able to drag the formula across and down to the entire area. And it could have several names concatenated together)

Thank you very much for your help. This forum always seems to come through!
Traggs

[AliGW]

Please attach the workbook directly here.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[traggs25]

I shared the link. I put it on google docs so that it was easily accessible.

[AliGW]

Not all forum members will follow external links. Please attach it here, as requested in post #2.

[traggs25]

Should be all set now.

[Bo_Ry]

Please try at G24 in Google sheet

=ArrayFormula(TEXTJOIN("/",true,IF(MMULT((Scrap!$B$3:$B$1000=$A34)*(Scrap!$G$3:$L$1000=H$1),TRANSPOSE(COLUMN(Scrap!$G$3:$L$1000)^0)),Scrap!$A$3:$A$1000,"")))

[traggs25]

AMAZING!!!! It Worked!!!

I can not thank you enough! I have been waiting like a year posting in forums.

Now I just need to learn how it works! LOL

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.