Very Complex Excel Formula.

[zeekaj]

Hey All,

First time posting, as I am just staring at the screen trying to figure out a very complex formula string. If there are any wizards out there that are up for a challenge I would be forever grateful!

I'll start off with what I'm trying to do. I have data from an online form in an excel sheet. Part of that data is a schedule of student classes including: class name, day(s) of the week, start time, end time. I want to have excel look through that data and return a value that I can then apply a conditional format to, to shade the cell, creating a more graphical version of the data.

The raw data:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: normal; }.xl66 { border: 0.5pt solid windowtext; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; }.xl69 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 1007"]
<tbody>[TR]
[TD="class: xl65, width: 142"]Submission Date[/TD]
[TD="class: xl65, width: 78"]First Name[/TD]
[TD="class: xl65, width: 71"]Last Name[/TD]
[TD="class: xl65, width: 120"]Class[/TD]
[TD="class: xl65, width: 71"]Day(s)[/TD]
[TD="class: xl65, width: 78"]Start Time[/TD]
[TD="class: xl65, width: 78"]End Time[/TD]
[TD="class: xl65, width: 128"]Class 2[/TD]
[TD="class: xl65, width: 71"]Day(s) 2[/TD]
[TD="class: xl65, width: 92"]Start Time 2[/TD]
[TD="class: xl65, width: 78"]End Time 2[/TD]
[/TR]
[TR]
[TD="class: xl66"]2015-07-22 11:37:45[/TD]
[TD="class: xl67"]Adam[/TD]
[TD="class: xl67"]Test[/TD]
[TD="class: xl67"]Chemistry[/TD]
[TD="class: xl67"]Monday
Wednesday
Friday[/TD]
[TD="class: xl68, align: right"]8:00[/TD]
[TD="class: xl69, align: right"]9:20 AM[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Tuesday
Thursday[/TD]
[TD="class: xl69, align: right"]8:30 AM[/TD]
[TD="class: xl69, align: right"]10:00 AM[/TD]
[/TR]
</tbody>[/TABLE]

The Graphical Sheet:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl65, width: 65"]TIME[/TD]
[TD="class: xl65, width: 65"]MONDAY[/TD]
[TD="class: xl65, width: 65"]TUESDAY[/TD]
[TD="class: xl65, width: 65"]WEDNESDAY[/TD]
[TD="class: xl65, width: 65"]THURSDAY[/TD]
[TD="class: xl65, width: 65"]FRIDAY[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:00 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:15 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:30 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:45 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:00 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:15 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:30 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:45 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

This is proving very tricky. My plan was to create a formula to sit in each cell of the graphical sheet. This would reference the data sheet. The formula needs to check the data to see first, if there is a match to the date (i.e. Monday), then if the start time is >= the time of the cell, and if the end time is <= the time of the cell. I've actually figured most of that part out with the below formula.

=IF(AND(ISNUMBER(SEARCH("Monday",$E$2)),IF(AND($F$2<=TIME(8,0,0),$G$2>=TIME(8,0,0)),TRUE,FALSE)),1,0)

I have to manually change the time numbers for each cell, but I can live with that.

PROBLEM #1: The one issue with this is, it is only looking in specific cells that I've defined, and therefore only looking for one class. I need it to look at all the classes, however the data that it's looking for is not in contiguous columns...

If I can figure out how to fix this problem, then I could at least shade the cells where classes are present in the graphical sheet.

PROBLEM #2: The ideal formula would not only look at all the data and calculate if the conditions are met, but would then display the class that met the defined criteria.

I'll finish up by providing what the graphical table would look like if I could figure out the correct formula.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl65, width: 65"]TIME[/TD]
[TD="class: xl65, width: 65"]MONDAY[/TD]
[TD="class: xl65, width: 65"]TUESDAY[/TD]
[TD="class: xl65, width: 65"]WEDNESDAY[/TD]
[TD="class: xl65, width: 65"]THURSDAY[/TD]
[TD="class: xl65, width: 65"]FRIDAY[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:00 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:15 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:30 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8:45 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:00 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:15 AM[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"]Chemistry[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:30 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9:45 AM[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Math[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas would earn my undying gratitude and awe at your excel wizarding awesomeness!!

 

[ndsutherland]

This should fill in your graphical table

=IFERROR(CHOOSE(IF(ISNUMBER(MATCH(O$1,$E:$E,0)),1,2),IF(INDEX($G:$G,MATCH($N2,$F:$F,1))>$N2,INDEX($D:$D,MATCH($N2,$F:$F,1)),""),IF(INDEX($K:$K,MATCH($N2,$J:$J,1))>$N2,INDEX($H:$H,MATCH($N2,$J:$J,1)),"")),"")

Where O$1 is the day of the week and $N2 is the time

This array formula is shorter but much slower if you use entire columns

=IFERROR(CHOOSE(IF(ISNUMBER(MATCH(O$1,$E:$E,0)),1,2),INDEX($D:$D,MATCH($N2,IF($G:$G>$N2,$F:$F),1)),INDEX($H:$H,MATCH($N2,IF($K:$K>$N2,$J:$J),1))),"")
confirmed with CTRL+SHIFT+ENTER (not just ENTER)

 

[zeekaj]

This should fill in your graphical table

=IFERROR(CHOOSE(IF(ISNUMBER(MATCH(O$1,$E:$E,0)),1,2),IF(INDEX($G:$G,MATCH($N2,$F:$F,1))>$N2,INDEX($D:$D,MATCH($N2,$F:$F,1)),""),IF(INDEX($K:$K,MATCH($N2,$J:$J,1))>$N2,INDEX($H:$H,MATCH($N2,$J:$J,1)),"")),"")

Where O$1 is the day of the week and $N2 is the time

Thanks for taking a look at this ndsutherland! I'm trying to parse out and understand your formula. I'm sure I'm just misunderstanding something. I tried to paste the formula into the sheet, putting the graphical table next to the data starting with "TIME" at N1. I've copied and pasted what came up, and added number and letter headers to make sure it's clear.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: normal; }.xl66 { border: 0.5pt solid windowtext; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; }.xl69 { border: 0.5pt solid windowtext; }.xl70 { border: 0.5pt solid windowtext; }</style> <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: normal; }.xl66 { border: 0.5pt solid windowtext; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; }.xl69 { border: 0.5pt solid windowtext; }.xl70 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 1431"]
<colgroup><col style="width:142pt" width="142"> <col style="width:78pt" width="78"> <col style="width:71pt" width="71"> <col style="width:120pt" width="120"> <col style="width:71pt" width="71"> <col style="width:78pt" span="2" width="78"> <col style="width:128pt" width="128"> <col style="width:71pt" width="71"> <col style="width:92pt" width="92"> <col style="width:78pt" width="78"> <col style="width:53pt" span="8" width="53"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 142"][/TD]
[TD="class: xl65, width: 142"]A
[/TD]
[TD="class: xl65, width: 78"]B
[/TD]
[TD="class: xl65, width: 71"]C
[/TD]
[TD="class: xl65, width: 120"]D
[/TD]
[TD="class: xl65, width: 71"]E
[/TD]
[TD="class: xl65, width: 78"]F
[/TD]
[TD="class: xl65, width: 78"]G
[/TD]
[TD="class: xl65, width: 128"]H
[/TD]
[TD="class: xl65, width: 71"]I
[/TD]
[TD="class: xl65, width: 92"]J
[/TD]
[TD="class: xl65, width: 78"]K
[/TD]
[TD="class: xl67, width: 53, align: center"]L
[/TD]
[TD="class: xl67, width: 53, align: center"]M
[/TD]
[TD="class: xl67, width: 53, align: center"]N
[/TD]
[TD="class: xl67, width: 53, align: center"]O
[/TD]
[TD="class: xl67, width: 53, align: center"]P
[/TD]
[TD="class: xl67, width: 53, align: center"]Q
[/TD]
[TD="class: xl67, width: 53, align: center"]R
[/TD]
[TD="class: xl67, width: 53, align: center"]S
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 142"]1
[/TD]
[TD="class: xl65, width: 142"]Submission Date
[/TD]
[TD="class: xl65, width: 78"]First Name[/TD]
[TD="class: xl65, width: 71"]Last Name[/TD]
[TD="class: xl65, width: 120"]Class[/TD]
[TD="class: xl65, width: 71"]Day(s)[/TD]
[TD="class: xl65, width: 78"]Start Time[/TD]
[TD="class: xl65, width: 78"]End Time[/TD]
[TD="class: xl65, width: 128"]Class 2[/TD]
[TD="class: xl65, width: 71"]Day(s) 2[/TD]
[TD="class: xl65, width: 92"]Start Time 2[/TD]
[TD="class: xl65, width: 78"]End Time 2[/TD]
[TD="class: xl67, width: 53"] [/TD]
[TD="class: xl67, width: 53"] [/TD]
[TD="class: xl67, width: 53"]TIME[/TD]
[TD="class: xl67, width: 53"]MONDAY[/TD]
[TD="class: xl67, width: 53"]TUESDAY[/TD]
[TD="class: xl67, width: 53"]WEDNESDAY[/TD]
[TD="class: xl67, width: 53"]THURSDAY[/TD]
[TD="class: xl67, width: 53"]FRIDAY[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]2
[/TD]
[TD="class: xl66"]2015-07-22 11:37:45[/TD]
[TD="class: xl67"]Adam[/TD]
[TD="class: xl67"]Test[/TD]
[TD="class: xl67"]Chemistry[/TD]
[TD="class: xl67"]Monday
Wednesday
Friday[/TD]
[TD="class: xl68, align: right"]8:00[/TD]
[TD="class: xl69, align: right"]9:20 AM[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Wednesday
Friday[/TD]
[TD="class: xl69, align: right"]9:30 AM[/TD]
[TD="class: xl69, align: right"]11:10 AM[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]8:00 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]3
[/TD]
[TD="class: xl67"]
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]8:15 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]4
[/TD]
[TD="class: xl67"]
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]8:30 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]5
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]8:45 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]6
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]9:00 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]7
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]9:15 AM[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[TD="class: xl67"]test3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]8
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]9:30 AM[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]9
[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl70, align: right"]9:45 AM[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[TD="class: xl67"]Math[/TD]
[/TR]
</tbody>[/TABLE]

I added in some text to try and figure out what the formula was spitting out where you put just a space as values. Edited formula below:
=IFERROR(CHOOSE(IF(ISNUMBER(MATCH(O$1,$E:$E,0)),1,2),IF(INDEX($G:$G,MATCH($N2,$F:$F,1))>$N2,INDEX($D:$D,MATCH($N2,$F:$F,1)),"test1"),IF(INDEX($K:$K,MATCH($N2,$J:$J,1))>$N2,INDEX($H:$H,MATCH($N2,$J:$J,1)),"test2")),"test3")

I understand that "test3" is the result of an error somewhere in the formula which is not resulting in a match, which I think works fine, however there are time where it should display correctly. Then I can't figure out why it is actually coming up with Math for 9:30am and 9:45am on all days, which is incorrect.

Any thoughts?..... (thanks so much!)

 

[ndsutherland]

Will additional classes generate additional rows or additional columns?

 

[zeekaj]

It would continue to add additional columns in the same structure. So in this example L1 would be Class 3, M1 = Day(s)3, N1 = Start Time 3, etc...

Students would be additional rows, but each student would generate their own unique graphical table.

 

[ndsutherland]

Allright, I moved "Time" over to V1

=IFERROR(CHOOSE(MAX((CHOOSE({1,2,3,4},$G$2,$K$2,$O$2,$S$2)>$V2)*(CHOOSE({1,2,3,4},$F$2,$J$2,$N$2,$R$2)<=$V2)*ISNUMBER(SEARCH(W$1,CHOOSE({1,2,3,4},$E$2,$I$2,$M$2,$Q$2)))*{1,2,3,4}),$D$2,$H$2,$L$2,$P$2),"")
Confirmed with CTRL+SHIFT+ENTER

you will have to augment each array to the maximum number of classes with their corresponding cells, but you can leave the formula at the max regardless of the number of classes the student has. In other words, you can use the same formula for a student with 9 classes as a student with 3 classes.

Once you have the chart filled in, conditional formatting is easy, just make a rule for each subject. =A1="Math" etc.

 

[ndsutherland]

This one is much simpler (sorry I didn't think of this first)

=IFERROR(INDEX($D$2:$R$2,,MATCH(1,--(($F$2:$T$2<=$V2)*($G$2:$U$2>$V2)*ISNUMBER(SEARCH(W$1,$E$2:$S$2))),0)),"") confirmed with CTRL+SHIFT+ENTER

notice the ranges are offset. You can expand them to include more classes, but they must all be the same size and maintain the same offset.

 

[zeekaj]

ndsutherland, you are a complete GENIUS!!!! Excel God!

Thanks so much, I can't believe this actually works, and I can (sort of) understand what it's doing. I was even able to successfully modify it to include the extra classes.

You're the best. Your pointy hat is in the mail!