Forum Discussion
Solved
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)
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.
