How do I use VLOOKUP to fill out an hourly calendar schedule?

Question

Rather than typing out the function I am dealing with since it is rather long and arbitrary without the columns it is referencing, I have provided a simplified mock-up of my spreadsheet here. Be sure to make a copy of it and not edit the original directly. If I need to remove this link, I will.

Essentially, I am trying to take a schedule that another party has already created and display it in an hourly calendar view according to the VLOOKUP search key. In my example linked above, I have created a classroom schedule in the Master Schedule tab that includes the room(s), subject, teacher(s), and start and end times, as well as the date. I have rooms scheduled for the current day, as well as the day before since some of the end times from the previous day go past midnight, and therefore I would like to include them in the calendar view for the current day if they run into the early morning. In that same tab, I have a VLOOKUP formula with the room number as the search key. The reason the formula is so long is because I have to account for if there are multiple rooms separated by a comma "," or forward slash "/", and it was drawn from an solution offered to another Stack Overflow question of mine. It gets the job done, but if there is a method that is not as convoluted, I'm all ears.

I would like to translate the data in the Master Schedule tab into the calendar view in the Room Schedule tab. I thought I had it worked out logically with a few IF statements, but I have struggled mightily. I realize open-ended questions like these aren't always appreciated on this platform, yet I am at a loss for how to accomplish what I am going for, especially without having the formulas take a long time to populate the calendar any time there is an update to the master schedule, so any help or a nudge in the right direction is greatly appreciated. I'm happy to clarify anything if the spreadsheet I linked or my objective is not clear.

I will make a new tab in the spreadsheet to reflect what I would like the output to be. Or did you want it as a comment here? — MBearnstein37, Sep 25 '19 at 23:28
If I understand you correctly, you are trying to build the "Room Schedule" from the data on the "Master Schedule". If that can be done with a formula, then @player0 can. For mine, I would write a script. BTW, I don't understand the relevance of VLOOKUP on the Master Schedule, other than possibly to find the current location for a given teacher; but of course, that formula doesn't take the date into account. — Tedinoz, Sep 27 '19 at 00:48
Yes, you are correct. Honestly, I would rather write a script since I feel that it would be faster computationally, but I have never written a script for Google Sheets so I would have no idea where to start. The VLOOKUP formula is in the Master Schedule tab just as a place to store it. I intended to use it in the Room Schedule, but other than that it serves no purpose in Master Schedule. player0 helped with that original formula, so I wanted people to see its output in case they were a bit lost going through it all, like I was initially. — MBearnstein37, Sep 27 '19 at 02:58
I wonder whether Google Sheets is the best way to manage these Schedules. Google Calendar is possibly more suited to your scenario. Also, you have no script skills and this script would be a very complex Sheets script (matching start/end times, possibility that a class began yesterday but carried over into today, etc). You didn't say whether you were using Google Apps, GSuite or Google Education. Google Education has many features and resources available, and even GSuite has control over rooms/resources. The basic Google Apps is not so advanced but it is possible to share a resource. — Tedinoz, Sep 27 '19 at 13:15
Unfortunately, I am just using Google Apps. Having this in Google Calendar would probably make more sense, but the reason I was so dead set on Sheets is because I already had gotten help from player0 regarding how to use VLOOKUP when there are multiple classrooms for one subject/teacher. Also, I wanted to have the option for other people to "reserve" an empty room for an unspecified amount of time by using the check boxes in the second column to mark whether they were actively taken or not. — MBearnstein37, Sep 27 '19 at 15:53
“Also, I wanted to have the option for other people to "reserve" an empty room for an unspecified amount of time” I don’t recall that in your question; but it simply adds to the complexity. Have it your way - but this is one complex script and I am wondering how you expect that it could get written. Do you have a budget? — Tedinoz, Sep 28 '19 at 00:42
I already have that capability in the spreadsheet with the check boxes, and that's why I never expected to get a script. I was fiddling around with some things trying to get the output I needed, but since I had hit a roadblock I figured I'd ask here since there are those who are much more experienced. No budget since this was just a personal project I was working on in order to try to improve the way we do something at my job. — MBearnstein37, Sep 28 '19 at 01:49

score 2 · Accepted Answer · answered Sep 27 '19 at 16:42

2

paste in C2 cell and dra down and then to the right:

=IFERROR(FILTER('Master Schedule'!$F$2:$F&CHAR(10)&'Master Schedule'!$D$2:$D, 
 REGEXMATCH('Master Schedule'!$E$2:$E, "^"&$A2&"$"), 
 HOUR('Master Schedule'!$B$2:$B)<=HOUR(INDEX(SPLIT(C$1, "-"), 1, 1)), 
 HOUR('Master Schedule'!$C$2:$C)>=HOUR(INDEX(SPLIT(C$1, "-"), 1, 1)),
 'Master Schedule'!$A$2:$A=TODAY()))

note: if this would be turned into one-cell arrayformula the performance of the sheet would suffer a lot hence this per-cell solution

answered Sep 27 '19 at 16:42

player0

124,011
12
67
124

Thank you! Since this doesn't utilize the VLOOKUP formula you had helped me with a while back, the output doesn't include classes that have multiple rooms separated with a comma, but this should definitely help me figure out what I was doing wrong before. Thanks again. – MBearnstein37 Sep 28 '19 at 01:58
The function creates a phantom class from 10:00am to 11:00am for "Ms. Jacobs-Spanish"; I expect that this is because row#1 on Room Schedule displays both the start time and end time of each class in a single cell (or at least I found this as a barrier to scripting). Can you foresee a different way of recording start/end times that would make your function more accurate? On a tangent, I suppose this is a different issue to that affecting classes that span midnight. – Tedinoz Sep 29 '19 at 10:31
I've also noticed an issue with classes that end at the middle of the hour, but I will look into that since I like trying to figure this kind of stuff out. I still think handling the classes with multiple rooms will pose a challenge, yet I appreciate the push in the right direction! – MBearnstein37 Sep 29 '19 at 19:45

How do I use VLOOKUP to fill out an hourly calendar schedule?

1 Answers1