Dec 27 2018 03:58 PM
Hello!
I am trying to build a semi-automated master schedule using the Index Match and IF functions but have not been successful in merging the two functions.
I have two schedules, one for doctors and one for nurses(Found in the excel sheet Index Match IF Function Help under tabs "MD" and "Staff") . I was able to use =INDEX(A2:D6,2,2) to pull the specific start time of each Doctor from the "doctor schedule". I was also able to use =IF(B12=720,"Kathy","Veronica") to populate nurses name associated with a specific clock in/start time(practice functions found in tab "Copy of Index Example"), but have not been successful in merging the two functions.
For example: If Doctor 1 begins at 7:20AM on Monday and Nurse A begins at 7:20AM on Monday then insert the name of Nurse A. If not then insert name of next nurse that starts at 7:20 OR if none fill with red.
Is this even possible? Am I thinking about this all wrong?
Thank you in advance!
Dec 27 2018 10:29 PM - edited Dec 27 2018 10:34 PM
Hi,
The structure of your schedules is hard to understand and it's not consistent!
So I have created simple schedules to show you how the formula works.
Please find this formula in the Doctors/Nurses table:
=INDEX($A$10:$A$14,INDEX(MATCH(TRUE,INDEX($B$2:$H$6,MATCH($A18,$A$2:$A$6,0),MATCH(B$17,$B$1:$H$1,0))=B$10:B$14,0),))
Please notice how the d1 in Doctors/Nurses table has n2, that's because of his start time matches the n2 start time, same thing for the d2.
I've attached the workbook.
Hope that helps