Lookup value is an order number like 100234, sitting in column A on both sheets. =VLOOKUP(A2,Orders!A:D,3,FALSE) returns N/A for about a third of the rows and works perfectly for the rest. If I copy the value from one sheet and paste it into the other, the formula works. So the values look identical and clearly are not. What am I looking at?
Wrap it while you diagnose so the sheet stays usable: =IFERROR(VLOOKUP(...),"check") then filter for check and see what those rows have in common. Do not leave IFERROR in the final version though — it hides new failures forever, which is how a quietly broken report survives two years.
Guilty of exactly that on another sheet.
For the text-number version of this specifically, the fastest one-off fix is: select the column, Data, Text to Columns, leave every default alone, press Finish. It converts them in place in about two seconds. Ugly, and it does not survive the next import, but it unblocks you while you sort the import out properly.
Also confirm the lookup column really is the first column of your range. VLOOKUP only searches the leftmost column of the array, so if somebody inserted a column into Orders your A:D no longer starts where you think it does. This is why I moved everything to XLOOKUP — separate lookup and return arrays, no column counting, and it does not silently break when a column is inserted.
The column counting fragility is the real argument. INDEX MATCH solved it decades ago and XLOOKUP just made it readable.
Long shot, but check for a stray leading apostrophe. It does not display in the cell, it forces the value to text, and it survives most cleanup attempts. You can only see it in the formula bar.
@xlookup_lena · 12mo ago · 3 replies
Almost always one of three things, in this order:
=LEN(A2)on both sides. If one says 6 and the other says 7, that is your answer, andTRIMfixes it.=ISNUMBER(A2)on both. Text 100234 and numeric 100234 are not equal as far as VLOOKUP is concerned even though they look identical on screen.=SUBSTITUTE(A2,CHAR(160),"")first, then TRIM.The fact that copying the value across fixes it points hard at the second or third one, and a third of rows failing suggests part of your data arrived from a different source than the rest.
Reply
Report
@xlookup_lena · 12mo ago
Classic. Fix it at import rather than with a helper column if you can — Power Query lets you set the column type once and it holds on every refresh.
Reply
Report
@pomodoro_pat · 12mo ago
ISNUMBER was FALSE on exactly the rows that failed. Half the file came from a CSV export.
Reply
Report