0

There appears to be an error in excel formula when combining XLOOKUP with MAKEARRAY formula:

e.g.

enter image description here

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])))

enter image description here

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.

i.e. Why doesn't y*0.19 when y = 3 match to the 0.570 in column A, given that it does match is 0.57 is specified exactly (without using y).

apologies for not being clearer in the previous question.

Update 1: Using True y*0.19=Array

enter image description here

Improve this question
14
  • 1
    This also works =MAKEARRAY(5,10,LAMBDA(x,y,XLOOKUP(TRUE,y*0.19=Table1[A],Table1[B]))) but I know it's not getting us much nearer to the root cause. Commented May 9, 2024 at 13:54
  • 1
    Unfortunately this doesn't seem to work on the original question: Please see update 2. I have highlighted the changes in pink and you can see it still returns #N/A stackoverflow.com/questions/78450550/… Commented May 9, 2024 at 14:25
  • 1
    Well we've established that the xlookup works with (say) y=7 so the logical conclusion is that the long formula is going wrong for some other reason. What I suggest is that you use =let(x,1,y,7.....rest of your long formula) and step through it using Evaluate Formula to see where it's getting the #N/A from. Having said that, if it's possible to replace the .19 by 19 and do everything with integers, that would be a much better way to go,. Commented May 9, 2024 at 15:34
  • 1
    @TomSharpe Unfortunately Im not sure we have established the Xlookup works. coincidently When y = 7 it doesn't work in the above image (update 1). So I'm not sure at this stage we can conclude the rest of the larger formula was wrong. Commented May 9, 2024 at 15:48
  • 2
    Excel is storing 3*0.19 as 0.57000000000000006 so an exact match with 0.57 will fail. This is a consequence of the documented Floating Point Error problem Commented May 9, 2024 at 19:47

1 Answer 1

Reset to default
3

Please see original question for background to this. The problem was that Xlookup gave #N/A for some values of y*0.19 where y is an integer although matching values were present. The initial workaround

Xlookup(true,y*.19=[Lookup table column],[Return table column])

worked on Excel 365 but not as it turned out in Excel online.

The original test data was posted in this Excel online sheet.

These are my results in Excel 365:

enter image description here

or (not sure if I had it aligned with the right row)

enter image description here

The lookup part of the formula (e.g. for y=7) works in my Excel 365 but gives #N/A in the online sheet:

=XLOOKUP(TRUE,7*0.19= Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]], Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]])

So now you would be looking at other standard methods which usually involve min absolute difference or similar.

Try this instead of Xlookup:

=@SORTBY(Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]],ABS(7*0.19- Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]]), 1)

Here is the result of substituting the ABS based formula for the two different lookups into the main formula:

=MAKEARRAY(10,11,LAMBDA(x,y,

LET(
lookup1,@SORTBY(Table1[[Rairborne(δ) (kg/s)]:[Rairborne(δ) (kg/s)]],ABS(y*0.19- Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]]), 1),
lookup2,@SORTBY(Table1[[γ(δ)]:[γ(δ)]],ABS(y*0.19- Table1[[Particle Diameter δ (μm)]:[Particle Diameter δ (μm)]]),1 ),

IF(x*7.8 <= InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60,

lookup1 *
    (1 - EXP(-lookup2 * x*7.8)) /
    lookup2,

lookup1 /
    lookup2 *
    (1 - EXP(-lookup2 * (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60))) *
    EXP(-lookup2 * (x*7.8 - (InputParameters[[Spray Duration (minutes)]:[Spray Duration (minutes)]] * 60)))
))))

This works in Excel 365 and Excel online. Result in online sheet:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You should be able to access OP's online copy at onedrive.live.com/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.