Sep 05 2022 04:48 PM
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 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)
Sep 05 2022 09:17 PM
Solution@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)
Sep 06 2022 02:22 AM
@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.
Sep 05 2022 09:17 PM
Solution@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)