Excel Formula Help!!! Combining IFERROR, INDEX, MATCH & SUMPRODUCT

[Bstatum]

Hello excel community.

I needing some formula help. I am working on a class scheduling project that uses the Excel Student Schedule template to read specific cell data from a master list and then paste that data into the corresponding worksheet.

I have set up my worksheet similar to how the Excel template works.

My formula combines an IFERROR, INDEX, MATCH & SUMPRODUCT function, but unlike the Excel template I am wanting to paste information across multiple worksheets and not just a single sheet.

Current function : =IFERROR(INDEX(ClassList,MATCH(SUMPRODUCT((ClassList[ROOM]=MondayDanksHeaders[[#Headers],[MAIN AUD]])*(ROUNDDOWN($B5,10)>=ROUNDDOWN(ClassList[START TIME],10))*($B5<=ClassList[END TIME]),ClassList[UNIQUE]),ClassList[UNIQUE],0),2),0)

I am having difficulty working out how get the function to recognize the specific worksheet to paste the data into. At the moment any data that matches gets pasted in the “Monday Danks” sheet, but I need the formula to also read the ‘Class List’ ‘Day’ column that matches the ‘Class Schedule’ sheets.

If there is would be a better way to run the worksheet (ex. Through VBA). I would appreciate the help.

Please see my samples below. My apologies if the samples arent great. This forum is quiet difficult to post on!

“Master Data List” – Row 11 as example
[TABLE="width: 1649"]
<colgroup><col><col><col span="4"><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 11"]CLASS LIST[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TYPE[/TD]
[TD]SUBJECT[/TD]
[TD]YEAR[/TD]
[TD]STREAM[/TD]
[TD]SEMESTER[/TD]
[TD]CODE[/TD]
[TD]DAY[/TD]
[TD]LOCATION[/TD]
[TD]ROOM[/TD]
[TD]START TIME[/TD]
[TD]END TIME[/TD]
[TD]UNIQUE[/TD]
[/TR]
[TR]
[TD]Core [/TD]
[TD]Leading a Church Department[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CA1P1[/TD]
[TD]Thursday Danks[/TD]
[TD]Danks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Corinthian Correspondence[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]Tutorial 1[/TD]
[TD]Friday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Corinthian Correspondence[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]Tutorial 2[/TD]
[TD]Friday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Core [/TD]
[TD]Leading a Church Department[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CA2P1[/TD]
[TD]Wednesday Danks[/TD]
[TD]Danks[/TD]
[TD]LIBRARY 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Core [/TD]
[TD]Corinthian Correspondence[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CA2P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Corinthian Correspondence[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]Tutorial 3[/TD]
[TD]Friday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Preaching[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]Tutorial 1[/TD]
[TD]Wednesday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Training Skills[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]Tutorial 3[/TD]
[TD]Wednesday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Core [/TD]
[TD]Communication in Ministry [/TD]
[TD]Certificate[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CC2P3[/TD]
[TD]Monday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD]10:15 AM[/TD]
[TD]11:15 AM[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Lecture[/TD]
[TD]Preaching[/TD]
[TD]Advanced[/TD]
[TD]Pastoral [/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]Friday Doody[/TD]
[TD]Doody[/TD]
[TD]MAIN AUD [/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Core [/TD]
[TD]Personal Leadership [/TD]
[TD]Certificate[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CC2P3[/TD]
[TD]Monday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD]11:15 AM[/TD]
[TD]12:45 PM[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Tutorial[/TD]
[TD]Bible Study Methods[/TD]
[TD]Certificate[/TD]
[TD]Pastoral [/TD]
[TD][/TD]
[TD]CC1P2[/TD]
[TD]Wednesday Danks[/TD]
[TD]Danks[/TD]
[TD]ARK[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

First “Day” Sheet example titled “Monday Danks” – Row 13 to follow example above
[TABLE="width: 1655"]
<colgroup><col><col span="6"><col><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 8"]CLASS SCHEDULE[/TD]
[TD]SCHEDULE START[/TD]
[TD]TIME INTERVAL[/TD]
[TD="align: left"]

Class List

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]8:00 AM[/TD]
[TD]15 MIN[/TD]
[/TR]
[TR]
[TD]TIME[/TD]
[TD]MAIN AUD[/TD]
[TD]CUBBYHOUSE[/TD]
[TD]ZOO[/TD]
[TD]PARENTS[/TD]
[TD]VOLTAGE[/TD]
[TD]ARK[/TD]
[TD]2ND AUD[/TD]
[TD]MUSIC [/TD]
[TD]LIBRARY 1[/TD]
[TD]LIBRARY 2[/TD]
[/TR]
[TR]
[TD]8:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8:15 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8:30 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8:45 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Communication in Ministry [/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]Music Theory for Vocals[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9:15 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Communication in Ministry [/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]Music Theory for Vocals[/TD]
[TD]Communication in Ministry [/TD]
[/TR]
[TR]
[TD]9:30 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Communication in Ministry [/TD]
[TD][/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Music Theory for Vocals[/TD]
[TD]Communication in Ministry [/TD]
[/TR]
[TR]
[TD]9:45 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Communication in Ministry [/TD]
[TD][/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Music Theory for Vocals[/TD]
[TD]Communication in Ministry [/TD]
[/TR]
[TR]
[TD]10:00 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD][/TD]
[TD][/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Music Theory for Vocals[/TD]
[TD]Communication in Ministry [/TD]
[/TR]
[TR]
[TD]10:15 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Communication in Ministry [/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]Communication in Ministry [/TD]
[/TR]
[TR]
[TD]10:30 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Communication in Ministry [/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10:45 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Communication in Ministry [/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11:00 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Communication in Ministry [/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11:15 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Leading a Church Department[/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]Personal Leadership [/TD]
[/TR]
[TR]
[TD]11:30 AM[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Musicians Workshop[/TD]
[TD]Vocal Workshop[/TD]
[TD]Personal Leadership [/TD]
[TD]Personal Leadership [/TD]
[TD]Songwriting Workshop 2[/TD]
[TD]0[/TD]
[TD]Public Speaking & Presentation[/TD]
[TD]Personal Leadership [/TD]
[/TR]
</tbody>[/TABLE]

If you’re interested in helping I can send the full project for a better understanding.
Thank you

 

[Bstatum]

To clarify the my second example on the excel it only posts the Subject name in the first time cell (ex. Communication in Ministry" Subject starting at 10:15am and ending 11:15am. but only posting the name in the 10:15 row.