You can see the value. It is right there in the other sheet, spelled the same way, in the column you pointed at. Excel returns #N/A anyway. By the fourth check you start doubting your own eyes.
Your eyes are fine. Excel is comparing something you cannot see, and I can show you which difference you have in about a minute. What it returns in the cell is the clue.
So this page sorts by what appears in your cell.
#N/A, and the value is definitely there
This is the big one, and Microsoft names the causes precisely.
Documented by Microsoft on its #N/A page: "The most common cause of the #N/A error is with XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, or MATCH functions if a formula can't find a referenced value."
Work through these four in order. The first two catch most of it.
A number that is really text
Documented by Microsoft: "The lookup value and the source data are different data types. For example, you try to have VLOOKUP reference a number, but the source data is stored as text."
⭐ This is the commonest cause and it is completely invisible on screen. 12345 as a number and 12345 as text look identical in a cell. Excel treats them as different things and refuses to match them.
The quickest tell: numbers align right in a cell by default. Text aligns left. If your lookup column is left-aligned when it should hold numbers, that is your answer.
The fastest fix is usually to make both sides the same rather than converting one. Select the column, use Data, then Text to Columns, and click Finish without changing anything.
That forces Excel to re-evaluate every cell as what it looks like.
Spaces you cannot see
Trailing spaces come out of every export, every database, every copy-and-paste. They break exact matching completely.
Documented by Microsoft, which gives the fix inside the formula: use `TRIM` to "remove any leading or trailing spaces". Its own example is `=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)`.
⚠️ Trailing spaces are worse than leading ones, because you cannot see where the text ends. Click into a suspect cell and press End. If the cursor stops past the last letter, you have found it.
FALSE missing from the end
Microsoft is blunt about it: "To find an exact match, set the range_lookup argument to FALSE."
And the warning that goes with it: using TRUE for approximate matching "can not only result in an #N/A error, it can also return erroneous results".
⭐ Read that second part carefully. Approximate match does not just fail. It can return a confidently wrong answer, silently, in a spreadsheet somebody makes decisions from. That is worse than #N/A.
The range moving as you fill down
Your first row works and everything under it fails.
That is because `A2:C100` becomes `A3:C101` on the next row and shifts a little further with every one, until your lookup value falls off the bottom.
Reported by an adviser, a Microsoft moderator, on exactly this: lock the lookup range with absolute references, such as `$A$2:$C$100`, so it does not shift when the formula is filled down.
Press F4 with the range selected in the formula bar and Excel adds the dollar signs for you.
#REF! instead of #N/A
Different problem entirely. Your column index is pointing outside the range you gave it.
Range three columns wide and you asked for column 4? That is #REF!. It commonly appears after somebody deletes a column inside the range.
If your keys have stopped behaving in Excel too, arrow keys moving the whole sheet is a separate fault with three causes.
Ours, and marked as ours: the index counts from the FIRST column of your range, not from column A of the sheet. If your range starts at column D, then D is 1 and E is 2. Getting that wrong is the other half of this error.
It returns a value, but the wrong one
Nothing is broken. You almost certainly left `range_lookup` as TRUE or left it out.
Approximate match returns the closest value below yours, which on unsorted data is effectively random. It looks like a working formula. That is exactly why it is dangerous.
⚠️ Add `,FALSE` to the end and see whether the answer changes. If it turns into #N/A, that value never actually existed and you have been reading a plausible fiction.
It cannot find something to the left
Not a bug. VLOOKUP physically cannot do this and never could.
Reported by an adviser on Microsoft's boards: if the return column is to the left of the lookup column, VLOOKUP will not work in that layout, and `XLOOKUP` or `INDEX/MATCH` are the better options.
`XLOOKUP` looks in either direction and is simpler to read. It needs a reasonably current Excel. `INDEX/MATCH` works in every version ever made.
If you have XLOOKUP available, stop writing new VLOOKUPs. It removes the column-counting, it removes the left-hand limit, and it lets you set the not-found result without wrapping anything.
Hiding the error, once you know what it means
Only after the four causes above. Hiding a real problem is how bad numbers reach a report.
Documented by Microsoft, which gives the pattern: `=IFERROR(FORMULA(),0)`, described as "if your formula evaluates to an error, then display 0, otherwise display the formula's result".
And where Excel is slow rather than wrong, what is really slowing the machine down measures two numbers first.
Ours, and marked as ours: prefer text to a zero. `=IFERROR(VLOOKUP(…),"Not found")` cannot be accidentally added up, and a column of quiet zeros can.
What happened on the thread I read
Reported by an owner who described it better than most: "the lookup value exists in the source data range. The value appears visually identical, but the formula does not return a match. This occurs consistently across multiple rows."
A Microsoft moderator gave the full list: FALSE for exact match, lookup value in the first column, matching data types, `TRIM` and `CLEAN` on the lookup column, absolute references, and XLOOKUP or INDEX/MATCH for a left-hand return.
The moderator followed up weeks later asking whether it worked.
⚠️ No reply ever came. I am telling you because it is the pattern here: the causes are documented, and almost nobody reports back which one was theirs.
Status: written 22 August 2026. Microsoft documents data type mismatches as the commonest #N/A cause and warns that approximate matching can return wrong answers rather than errors.
Why does VLOOKUP say #N/A when the value is right there?
Almost always a data type mismatch. A number stored as text looks identical to a real number on screen and Excel will not match them. Check the alignment: numbers sit right in a cell, text sits left. After that, look for trailing spaces.
Do I always need FALSE at the end of VLOOKUP?
Unless you genuinely want an approximate match on sorted data, yes. Microsoft's own warning is that TRUE "can not only result in an #N/A error, it can also return erroneous results", which means a confident wrong answer rather than a visible failure.
Why does my VLOOKUP work on the first row only?
Your range is shifting as the formula fills down. A2:C100 becomes A3:C101 on the next row. Lock it with dollar signs, as $A$2:$C$100, or select the range in the formula bar and press F4.
Can VLOOKUP look to the left?
No, and it never could. If your answer column sits left of your lookup column, VLOOKUP cannot reach it. XLOOKUP handles either direction, and INDEX/MATCH does the same in older versions of Excel.
The Short Version
- A number stored as text looks identical on screen and will never match a real number.
- Numbers align right by default, text aligns left. That is your fastest test.
- Microsoft's own TRIM example is =VLOOKUP(D2,TRIM(A2:B7),2,FALSE).
- Leaving out FALSE can return a wrong answer rather than an error, which is worse.
- A range that shifts as you fill down is why row one works and the rest fail.
- VLOOKUP cannot look left. XLOOKUP and INDEX/MATCH can.
Where to Next
Look at the alignment in your lookup column before you change anything. Numbers sitting on the left of their cells is a thirty second answer to a problem people spend afternoons on.
If your arrow keys have also stopped behaving, arrow keys moving the whole sheet is a different Excel fault with three different causes.
And if Excel itself is slow rather than wrong, what is really slowing the machine down measures instead of guessing.
If a cause I have not listed turned out to be yours, post it below. On this topic the fixes are documented and the real cases almost never get reported back.
Isaac Smith is the founder and editor of PC Glance, a website that covers computers, laptops, and technology. He is a tech enthusiast and a computer geek who loves to share his insights and help his readers make smart choices when buying tech gadgets or laptops. He is always curious and updated about the latest tech trends.