A lookup function to return a persons function which will vary depending on dates

I need help trying to get Excel to look up personnel's function which may change depending on what activity they are tasked to work on. Activities are time driven and may last a period of months before they may revert to their original function or onto a new function. Each function then attracts a different rate.

For instance person A can be an R3 between 01/01/2020 and on the 03/02/2020 they may operate as an RA and return to being an R3 the following month. I've tried using variations of VLookup (not sufficient to cf function AND dates) and XLookup (returned #SPLL error) as well as INDEX and MATCH but they're giving me a result that is definitely NOT the correct answer.

In my personnel reference table I have:

Column A - Name
Column B - Start Date of Function
Column C - End Date of Function
Column D - Function
Column E - Typical Hours

I've attached an example of the personnel function list - this is just an example, obviously I cannot share the actual data. I have a list of 155 personnel with multiple entries where the person would be operating as a different function - there are 7 individual functions each attracting a different hourly rate.

Personnel Function.JPG


In a new table I'm trying to compile (based on time sheets) lists all of the works and personnel to date (currently at over 10,000 lines) and I need to allocate their function, I really don't want to manually go through each time-sheet tracking the change in function but as different functions attract different rates it is imperative this is correct and VLOOKUP only returns their original function.

Column A - Date
Column B - Name
Column C - Function - this is where I want to identify what the person was doing during the course of the Works.
There are more columns tracking Unit Rates, Total Costs


If you could help me that would save me a lot of google time and hair pulling.