Forum Discussion
Two way vertical and horizontal lookup
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 Jam...
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
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
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.
- 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?
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.
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.
