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: =SEQUE...
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.
