Two way lookup against a name and date

I want a formula to lookup a date, establish whats it falls onto using the week ending date, match that against the employee name and return the intersecting value.

For example, I want to see James' potential hours for 8/1/21, which should return 32 (B16). If the date was 04/04/21 then it should return 25.6 for James

The dates in my dataset are by week ending e.g. Sunday 10/1/21 so a date lookup would need to know that 8/1/21 falls under that week and returns the intersecting value against the employee name

I have tried the following xlookup to no avail

=XLOOKUP(B3,A13:A16,XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1))

worksheet name : Potential hours

Cell B5 formula

Formula:

Please Login or Register  to view this content.

you could also use INDEX-MATCH-MATCH, like this:

Please Login or Register  to view this content.