Forum Discussion
Xlookup and Sequence intermittently not working together
I have an xlookup formula using a dynamic array as a reference, but it intermittently does not return the right value.
in Cell A1, I have the dynamic array created using sequence:
=SEQUENCE(31,1,0.85,0.01)
In cell B1, I have the Xlookup formula:
=XLOOKUP(0.94,A1#,A1#,0,0)
So, expected return is 0.94, but actual return is 0 (i.e. not found).
Same happens with 0.95, 0.96, 0.97, 0.98, 0.99
But:
- If I use 0.92, 0.93, the xlookup works OK
- If I use 1.0 or 1.01, the xlookup works OK
- if I use A1# as the lookup value, the whole array returns correctly
if I enter the following formula in C1:
=IF(A10=0.94,1,0)
I get 1
Therefore, the sequence formula is returning the correct value, but the xlookup (and vlookup and match) cannot find the value.
Match:
=MATCH(0.94,A1#,0)
returns #N/A
Vlookup:
=VLOOKUP(0.94,A1#,1,FALSE)
returns #N/A
I'm seeing this in excel 16.63.1 (22071301) for mac, but also at work on excel for windows (version tbc)
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)
4 Replies
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.
Thanks for this solution. I had the same problem using XLOOKUP against a SEQUENCE of times. ROUNDing both the lookup value and the sequence to 5 digits fixed it perfectly.
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)Riny_van_Eekelen Thank you! This worked perfectly.
i had to use round on the lookup value in the actual sheet also, but with both, it worked.
