Forum Discussion
Xlookup and Sequence intermittently not working together
Blythyvxr The problem lies in creating the sequence with small (decimal) numbers. Other can explain it better that I. Internally, Excel "creates" a slightly different value. When you notice this happen, use ROUND to force the accuracy you need. Use this for the sequence and it will work.
=ROUND(SEQUENCE(31,1,0.85,0.01),2)
This could be related to how Excel handles dynamic arrays and recalculation. Since SEQUENCE produces a spilled array, XLOOKUP may behave unexpectedly if the lookup array or return array isn’t referencing the spill range correctly.
I’d check whether you’re using the spill operator (#) where appropriate, for example A1#, and also verify that the lookup and return arrays have matching dimensions. If the issue is intermittent, checking calculation mode and forcing a full recalculation may also help.
If you share the exact XLOOKUP formula and a small example of the expected vs. actual result, it would be easier to pinpoint the cause.