How to find next scheduled date of employee

[ghmm]

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)

I think I just have to use XLOOKUP but I'm struggling wrapping my head around the operations I need to do

See attached sample workbook

[AliGW]

Please manually add a few expected results to show what you want.

[Toonies]

Hi this may help you on your way
i'm not 100% sure that it fits your needs

I've tried to add an attachment but unable to at the moment

so do the following

1st you have to use real dates in your Schedule sheet
you can customise the format for them to display ddd, mm-dd-yyyy

okay place this Array formula in C2 and copy down

Important: Press Ctrl+Shift+Enter whenever you need to enter an array formula

this displays the 1st Scheduled day of your employees from your scheduled sheet

=IFERROR(AGGREGATE(15,6,Schedule!$B$1:$H$1/(INDEX(Schedule!$B$2:$H$6,MATCH($A2,Schedule!$A$2:$A$6,),)>=1),COLUMNS($C2:C2)),"")

Create 3 named ranges

1. Date_Header

=Schedule!$B$1:$H$1

2. Name_List

=Schedule!$A$2:$A$6

3. tbl

=Schedule!$B$2:$H$6

inset the following Formula in D2 and copy down

=IFERROR(INDEX(tbl,MATCH($A2,Name_List,0),MATCH($C2,Date_Header,0)),"")

this then displays the employees shift time for that date

others may come up with a better solution to your problem

Enjoy

Toonies

[Glenn Kennedy]

You mentioned XLOOKUP. Does that mean that you have O365? If so, please amend your profile accordingly. 16 implies (to most of us) Excel 2016, a totally different beast.

Formula reply withdrawn!!

[Glenn Kennedy]

1. Change the dates in schedule to real dates.

2. To return the next DATE:
=IFERROR(INDEX(Schedule!$B$1:$H$1,MATCH(1,(Schedule!$B$1:$H$1>=TODAY())*(INDEX(Schedule!$B$2:$H$6,MATCH(A2,Schedule!$A$2:$A$6,0),)<>"Off"),0)),"")

3. To return the next TIME:
=IFERROR(INDEX(Schedule!$B$2:$H$6,MATCH(A2,Schedule!$A$2:$A$6,0),MATCH(C2,Schedule!$B$1:$H$1,0)),"")

Obviously, if you want the search to EXCLUDE today, remove the bit in red from the fromula at 2).

[ghmm]

Thank you very much, your solution expanded my knowledge and I understand more about MATCH and INDEX uses

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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

[ghmm]

Already added it a few days ago but wasn't sure how to mark solution. Will do!