Matching Leave Requests to a Rota

Hi,

I have a sheet that lists requests for leave: employee name (A), leave type (B), start date (D), end date (E).
And another sheet with the rota on that has names (A) and dates in ascending order across columns (Row 1).

I'm trying to get the rota to assign the leave type into the cell that aligns with the employee name and date.

The problem I'm having is the name appears multiple times on the leave request sheet and that I need it to assign the leave type to every date that falls within the range.

I thought I'd cracked it but unfortunately the formula trips over as I copy it across because it's not specific enough.

Truly at a loss and any ideas would be greatly appreciated!

Thanks,
Chloe

It would help if you attached a sample Excel workbook, and you can do this by following the guidelines in the yellow banner at the top of the screen.

Hope this helps.

Pete