Two way vertical and horizontal lookup

[MHir03]

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))

[#2]

@MHir03 

=IFERROR(INDEX($B$13:$AW$16, XMATCH(B3, $A$13:$A$16), XMATCH(B4, $B$12:$AW$12, 1)), "not found")

[#3]

@MHir03 

In principle, what you are doing is correct.

= XLOOKUP(
    name, 
    employee, 
    XLOOKUP(d,wkEnding,hours,"",1)
  )

so the problem is in the detail.  Maybe looking up a numeric datavalue against a text date.

[#4]

Thank you Hans, this seems to have done it. Why would did you use IFERROR and XMATCH over XLOOKUP? wouldn't the latter be far easier?

I'm expecting you'll see there are limitations to xlookup relevant to my dataset which would not make it work as i wasnt able to implement it so far. I confirm the dates are being handled as dates in excel and not as numbers

[#5]

@MHir03 

The third argument of your XLOOKUP formula should be an array, but it is XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1) which returns a single value, not an array.

[#6]

@Hans Vogelaar 

Well spotted.  I haven't used a direct cell reference for so long that I tend to see them but not register their content.  I simply replaced the reference by a two dimensional array 'hours' and, of course, the formula worked perfectly.

[#7]

Hi both, struggling to see the error in the formula, the third argument is referencing a return array B16:AW16 so what am I doing wrong?

[#8]

@MHir03 

Your original formula has two XLOOKUPS:

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

The first (outer) XLOOKUP has arguments:

1. Lookup value: B3

2. Lookup array: A13:A16

3. Return array: XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1)

The return array argument is an XLOOKUP formula that returns a single value, not an array.