VLOOKUP returns #N/A — but the value is right there

How to fix it

The value almost certainly looks identical on screen but doesn't match underneath — usually because one side is a real number and the other is that number stored as text, or because of invisible trailing spaces. Once you know which side has the problem, you have a few options:

• Convert text to numbers: Select the column with the text-formatted numbers. Go to Data > Text to Columns, click through to Finish without changing anything. Excel re-reads the column and converts the values to real numbers.
• Use VALUE() in the formula: Wrap your lookup value in VALUE(), like =VLOOKUP(VALUE(A2), ...). This converts the text to a number at lookup time without changing the underlying data.
• Strip trailing spaces with TRIM(): If spaces are the issue, wrap the lookup value: =VLOOKUP(TRIM(A2), ...). You can also paste a TRIM formula alongside the lookup column, copy-paste-as-values, then delete the original column.

How to tell if your column has text-formatted numbers

Click a cell you suspect. Look at the top-left corner of the cell — if there's a small green triangle, Excel itself is flagging the value as a number stored as text. You can also watch which side of the cell the value leans toward: real numbers lean right by default, text leans left. If your "numbers" are all left-aligned, that's the sign.

Why this happens at all

VLOOKUP doesn't compare values the way your eyes do. When you look at a column and see 12345, your brain reads it as the number twelve-thousand-three-hundred-forty-five. Excel might be storing that cell as the word "12345" — characters that happen to look like digits, not an actual number it can do math with. To VLOOKUP, the number 12345 and the word "12345" are completely different things that will never match each other, even though they look identical on screen.

This mismatch almost always enters a spreadsheet when data comes from outside Excel — an export from another program, a copy-paste from a website, a downloaded report. The other system doesn't encode numbers the way Excel expects, and Excel quietly stores them as text without telling you.

The other silent culprit: invisible spaces

The second most common reason is trailing spaces — blank characters at the end of a value that you can't see. If one column has Widget A and the other has Widget A   (with two spaces after it), they look identical in the cell but VLOOKUP sees them as different strings. This also sneaks in through copy-paste or exports.

Why VLOOKUP works on some rows but not others

When only certain rows return #N/A, the two columns were built from different sources. Some rows were typed in directly (producing real numbers), while others came from an import or paste (producing text). The rows that matched are the typed ones; the rows that failed are the imported ones. The inconsistency is in the data, not the formula.

Can I find the cause without checking every cell manually?

Yes — upload your file to our fix formula errors tool. It scans your spreadsheet and flags cells where numbers are stored as text, where values have leading or trailing spaces, and where a column has mixed types that would cause lookup failures. You get a plain-language report of exactly which cells are the problem, so you don't have to click through hundreds of rows yourself.

XLOOKUP and INDEX/MATCH hit the same root causes — text vs. number mismatches and trailing spaces affect all lookup formulas the same way.