Forum Discussion
XLOOKUP returning #N/A for some values when the values are there
I am trying to use XLOOKUP to find an identifying number from a separate spreadsheet, referenced by a different identifying number. My equation works for the most part, but I have a few #N/A errors (3 out of 11), but I have confirmed that the values were present in the array and that there were no typos.
I cannot share a screenshot, so I will give a simplified version using sheets WS1, WS2, and WS3 with values ID1 and ID2. WS1 is the active sheet I am writing the formula in
ID1 =VLOOKUP(A2,WS2,ID1Column,false (This equation works with no issues)
ID2 = XLOOKUP(ID1Value,WS2LookupArray,WS2ReturnArray)
ID2 equation works for the majority of the time, but returns a few #N/A and I cannot figure out why. I have run trim to eliminate any extra spaces, I formatted all cells the same way, I have no idea why I cannot get the error cells to work.
6 Replies
I'm late to this discussion but leaving this for anyone that like me has the same problem and goes looking for answers like I did yesterday 9/18/2025.
My XLOOKUP was failing. I used trim to get rid of leading & trailing spaces. I could search the 2 sheets using find for a specific word/data in each and find them just fine but XLOOKUP continued to fail. I ended up copying the 2 columns involved into a blank spreadsheet. Again I could use Find and it would see it in the 1st column and again in the 2nd. XLOOKUP still failed. So in a 3rd column I did =EXACT(A1,B1) and it returned a false on each line. After running that the first column widened verically and it became easy to see the data in colum A was at the top of the cell and the data in B was at the bottom. Every cell in Column A had a carriage return from the way it had been pulled from another source. I used Find & Replace All to remove the carriage return and XLOOKUP worked fine. It took me 30 minutes to figure this out so I thought I'd leave this in case someone else ever has the same issue.
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:
jgans The LOOKUP functions are sensitive to the data-type on both ends. They will only find a match if both the looked for cell and the Looked In range contain numeric, or text values. If the data types do not match up, no match is found.
So check your data in the table you are looking up information from, some ID's are text and others are numbers. You want all of them to be text, or all of them numbers. And the cell containing the lookup ID to be of the same data type.
