XLOOKUP not finding result

[RaulSerg]

Hi, what am I missing in the attached that XLOOKUP fails in one instance?

If I solved all the ranges into array constants, it seems to work.

Thx

[MrShorty]

It looks like floating point error. If I enter =(B3-D1) [note the parentheses that are needed to prevent Excel from arbitrarily deciding that this difference should be exactly 0] into a cell and format as scientific, I get 2E-9, indicating a very small difference between the two values.

Most of the time, floating point errors are handled using ROUND() functions. In your example, it appears that all numbers are integers, so perhaps rounding the values in column D to integers would be suitable =ROUND(currentformula,0) in D1 and D2.

The other solution might be to consider what the repeated division/reciprocal is supposed to be doing and try a different operation, since the floating point error is going to be introduced by the division/reciprocal operation.

[RaulSerg]

Thx.
The repeated division is a necessary data cleaning exercise required in the full data set.

I noted that e.g. switching to filter to achieve a similar result does not exhibit the same problem, at least not in this instance.
Not exactly sure though why that is.

[MrShorty]

The main difference I see between a filter operation and a 1/(1/n) operation is that a filter merely extracts the data according to criteria, but performs no operations on the data. 1/(1/n) actually operates on the data. Because 1/n cannot be exactly represented in floating point notation but must be approximated, the overall 1/(1/n) operation may or not be exactly equal to the original value n.

Data cleaning is not something I do much of, so I cannot really speak to best practices. It seems to me that, all else being equal, a cleaning operation that avoids introducing floating point errors will be superior to an operation that does. Since all else is rarely equal, one usually needs to make some kind of decision based on the overall goals of the task and what options one has for accomplishing the task.

If you are unfamiliar with floating point arithmetic, this is still the most complete document I have found (even though it is 30+ years old): https://docs.oracle.com/cd/E19957-01..._goldberg.html There's almost certainly more there than you want to know, but, if you can get through the first few sections, you will be well on your way to understanding why 1/(1/n) <> n (at least sometimes).

[RaulSerg]

Apologies, I wasn't clear with my answer.
I meant I used FILTER instead of XLOOKUP (not instead of 1/1/) and working with the 1/1/-number semeed to be no problem for FILTER.

=FILTER(A3:A4,D1:D2=B3)

I had suspected floating point issues, but there didn't appear one to be to me (I see the issue when I use your formula though =(B3-D1)).
When I solve cell D1 with F9, XLOOKUP works.
However, when I paste cell D1 as a value, there's still a problem despite the number doesn't appear to have an issue.

I agree with the rest of your comments. Thanks for your explanation.