There appears to be an error in excel formula when combining Xlookup with Make array formula:

e.g.

This is a simplified example. I have used y values of constant increments to make the formula easier to highlight the error however this isn’t necessarily the case. In reality, there may be a different way to introduce y values.

Despite the maths being correct i.e. 0.19*3 = 0.570 I get a #N/A error. We would have expected C.

But then strangely it does works on the next line i.e. 0.19*4 = 0.760 and returns D as desired.

I would like to avoid any assumptions e.g. rounding or using the next closest value as the values are exact (Except in my previous question where I make a mistake with the data).

Data to mimic:

A   B
0.190   A
0.380   B
0.570   C
0.760   D
0.950   E
1.140   F
1.330   G
1.520   H
1.710   I
1.900   J

Formula:

=MAKEARRAY(3,10,LAMBDA(x,y,XLOOKUP((y*0.19),Table1[A],Table1[B])))

Even more confusingly if I specify y exactly:

=MAKEARRAY(3,10,LAMBDA(x,y,XLOOKUP((0.570),Table1[A],Table1[B])))

it works so it should always be an exact match.

So there is some issue with saying y*0.19

Rounding and using XLOOKUP With a non-exact match may get the right answer but I don’t think its acceptable for the question here because the values are always and exact match and I want to understand what the problem then is.

apologies for not being clearer in the previous question.