Looking for a date between variable intervals

Hello, people! I am in a real pickle with this excel sheet. I have a list of deliveries, with dates. I also have a list of drivers, with different roles that may change with time. I need to find a way to return the rate of the delivery based on the delivery date, the driver, and his/her role at the time of the delivery.

Basically, the thinking should be this: I have a delivery, who is the driver, look for the role does that driver has on the day of the delivery, then return the correct rate. The delivery list is not fixed, so deliveries can be added. The roles of the drivers can change in time.

I've attached an excel example, with some comments in red.

Does anyone have any idea how to achieve this?

Thank you very much!

Try

in E12

=INDEX($B$6:$C$6,MATCH(INDEX($A$2:$Z$100,MATCH($D12,$A$2:$A$100,0),MATCH($A12,INDEX($B$2:$Z$100,MATCH($D12,$A$2:$A$100,0),0),1)),$B$5:$C$5,0))

copy down

John, you're a genius! I think it works! I'll play around with it a bit and come back with a reply.

Glenn, yes, the layout of the first rows is flexible. Actually, I welcome the suggestion.

Thank you both!

It's a very good approach, leelnich. Mixing John's solution with your idea, I came up with a dynamic named range approach. I'm just going to post it in case any other noob like myself needs it:

=INDEX($B$8:$C$8,MATCH(INDEX(rolesanddates,MATCH($D14,drivers,0),MATCH($A14,INDEX(rolesanddates,MATCH($D14,drivers,0),))-1),$B$7:$C$7,0))

where $B$8;$C$8 are the return value, $B$7:$C$7 the lookup values, and the named ranges are as follows:

drivers "=OFFSET(Driver!$A$2,0,0,COUNTA(Driver!$A:$A)-21,1)"
rolesanddates = "=OFFSET(Driver!$B$2,0,0,COUNTA(Driver!$B:$B)-22,COUNTA(Driver!$2:$2)-1)"

Adjust the minuses to fit.

Thank all of you guys for the quick replies. You're great!