How do I lookup employee's next scheduled date and time?

[mattshu67]

I have one sheet that contains a list of employees and training modules they need to complete. On another sheet, I have a list of the employees and their schedule for the week.

I'd like to make a column on the sheet with training modules that looks up that employee's schedule and gets their next scheduled date and the shift they're working (if they're not working the current day)

Forgive me for not posting an image or an interactable spreadsheet as I thought this was probably simple enough to yield a simple answer that my brain can't wrap around

Schedule sheet:

Employee	Sat, 06/26	Sun, 06/27	Mon, 06/28	Tue, 06/29	Wed, 06/30	Thu, 07/01	Fri, 07/02
VAN-MICHAEL JUD	11:00am-8:00pm	11:00am-8:00pm	OFF	OFF	11:00am-8:00pm	11:00am-8:00pm	OFF
RICHARD BRI	OFF	OFF	4:00am-1:00pm	OFF	OFF	4:00am-1:00pm	4:00am-1:00pm
GREGORY JAC	7:00am-4:00pm	OFF	OFF	7:00am-4:00pm	OFF	7:00am-4:00pm	OFF
EUGENE SEL	OFF	10:00pm-7:00am	11:00pm-7:00am	10:00pm-7:00am	10:00pm-7:00am	10:00pm-7:00am	OFF
BENJAMIN LEV	1:00pm-10:00pm	1:00pm-10:00pm	OFF	OFF	OFF	1:00pm-10:00pm	1:00pm-10:00pm

Training Module sheet:

Employee	Item Name	Next Scheduled Date
BENJAMIN LEV	Controlled Substances Refresher
EUGENE SEL	AP-09 Intervention
GREGORY JAC	Manager Responsibilities and Policy Refresh
RICHARD BRI	AP-09 Intervention
VAN-MICHAEL JUD	AP-09 Authorized Employees

 

[Momentman]

So what would the answers be for those Employees in the table? so we know what the formula should return

 

[mattshu67]

So what would the answers be for those Employees in the table? so we know what the formula should return

It would be great if I could get the day and time they work in the C column under 'Next Scheduled Date' if that's what you're asking

 

[Momentman]

It would be great if I could get the day and time they work in the C column under 'Next Scheduled Date' if that's what you're asking

I get that part. I was only trying to be sure what criteria will feature or not. if I take Benjamin Lev for example, since today is 30th June and he is OFF, the answer should be 1st July and 1-10pm, is that correct?

 

[mikerickson]

Cross-posted

(How do I post a simple link that doesn't expand into a (logoed) advertizement for the other web site. <noparse> Link and <Url> both auto convert to a [media] tag. </noparse>)

 

[mattshu67]

I get that part. I was only trying to be sure what criteria will feature or not. if I take Benjamin Lev for example, since today is 30th June and he is OFF, the answer should be 1st July and 1-10pm, is that correct?

Yes please! Someone suggested using a MINIFS() function but I'm having trouble figuring out which references go where

 

[mattshu67]

Also didn't realize cross-posting was against the rules--sorry, just a beginner desperate to understand

 

[mikerickson]

Cross posting isn't against the rules, but when one does, including a link to the other post is courteous. Some sites require that degree of courtesy, others just recommend it.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to find next scheduled date of employee
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mattshu67]

Cross post with possible solutions: How to find next scheduled date of employee

Thanks everybody!