Forum Discussion
XLOOKUP returning #N/A for some values when the values are there
Excel gives you the clues if you know where to look for them.
One approach to determining why a lookup function like XLOOKUP is missing is to simplify and create a basic formula to compare contents of two cells:
In the first screen capture, =F1=G1 results in FALSE despite the contents appearing to be the same. In the next screen capture, each cell is evaluated with F9 to show you how Excel sees things.
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:
- What does your formula look like?
