Forum Discussion
XLOOKUP returning #N/A for some values when the values are there
I am having the exact same issue and I have verified using the =Cell=Cell that they are in fact indentical. This returns True. What else could it be?
As Patrick2788 wrote, it behooves you to show us the formulas -- and the data as well.
Even better (since cell=cell has different results), attach an Excel file that demonstrates the problem. Click on "browse files to attach" at the bottom of the reply applet.
That said, I suspect that one or both cells are calculated, and their internal binary values differ infinitesimally.
The remedy might be: be sure that both cells are explicitly rounded to the same precision.
The difference might be invisible because Excel formats only up to 15 significant digits.
And the equal operator ( "=" ) compares values that are rounded to 15 significant digits internally just for the purpose of the comparison.
In contrast, lookup and match functions compare the full binary value, which is not limited 15 significant digits, contrary to most online documentation including MSFT's.
Example: