SOLVED

Xlookup and Sequence intermittently not working together

Copper Contributor

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)

2 Replies
best response confirmed by Hans Vogelaar (MVP)
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)

 

@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.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
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)

 

View solution in original post