Index Match with IF combination

[smmbita9]

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!

[#2]

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