Determine Manager on Duty

[punkrocksock]

I need a formula that will tell me which manager is on duty given a certain time stamp. I want it to apply the time stamp to the correct department and then evaluate from a schedule which range it falls within and display the name corresponding to that range. I've attached a sample workbook to help illustrate what I am looking for.

Thanks in advance.

[Special-K]

Rearrange your table so the start times are in numerical order, so column H reads 12:00 AM, 8:00 AM, 4:00 PM and column J reads Mark David Sue
Do the same with the other departments

in C2
=INDEX(INDIRECT("H"&MATCH(B2,G2:G14,0)+1&":J"&MATCH(B2,G2:G14,0)+3),MATCH(A2,INDIRECT("H"&MATCH(B2,G2:G14,0)+1&":H"&MATCH(B2,G2:G14,0)+3),1),3)