Forum Discussion
Two way vertical and horizontal lookup
=IFERROR(INDEX($B$13:$AW$16, XMATCH(B3, $A$13:$A$16), XMATCH(B4, $B$12:$AW$12, 1)), "not found")
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.
