Lookup Function with Multiple Criteria Including Values Between Two Dates

Hello,

I'm having issues finding a lookup function that will return a value from a table based on two search criteria - the date and the employee name. In table 1 I have the employee name and a date. I want to return the group number for this employee from table 2 based on the employee's name and date. The trick is that the date in table 1 has to be between the start and end date in table 2.

Any suggestions would be greatly appreciated. I'm totally stumped. I have attached an image of the problem.


[Excel Example.png

Hi Jacc,

Thanks for the response. I have searched other threads but I haven't been able to find one that explains how to use a lookup feature when you need to match the date in one table to a date range in another table.

I have posted a workbook.