How to why vlookup returns #n/a — hidden leading and trailing spaces
- Step 1Confirm whitespace is the cause — In a spare cell, type
=LEN(A2)-LEN(TRIM(A2))against the lookup value. A result above zero means A2 has trailing/leading spaces. If it returns zero but the lookup still fails, the value likely contains an edge CHAR 160 (Excel'sTRIM()ignores it) — the trimmer still catches that. - Step 2Save the file holding the values — Save the workbook (or the relevant sheet) as
.xlsxor.csv. If your lookup value and lookup table live in two different files, you'll trim each one. - Step 3Drop it on the trimmer — Drag the file in. Every cell on the first sheet is trimmed of leading/trailing whitespace — both your key column and the table you look into — in a single pass. Nothing is uploaded.
- Step 4Download the cleaned file — An
.xlsxreturns as<name>-processed.xlsx; a CSV stays CSV. The output is values-only. - Step 5Repeat for the second file if needed — Cross-file
VLOOKUP? Trim the other workbook the same way so both the key and the table are edge-clean. - Step 6Re-run your lookup — Open the cleaned file(s) and recalculate. The
#N/Ashould resolve. If a stubborn one remains, it is almost certainly an internal space or a genuine value difference — see the edge cases.
Why exact-match formulas fail on invisible whitespace
How common lookup/aggregate functions behave when the key carries hidden edge whitespace, and whether this trimmer addresses it.
| Function | Failure symptom | Fixed by trimming edges? |
|---|---|---|
VLOOKUP(... , 0) / exact | Returns #N/A though the value is visibly present | Yes |
XLOOKUP | Falls through to the if_not_found value or #N/A | Yes |
INDEX/MATCH(... , 0) | MATCH returns #N/A, so INDEX errors | Yes |
SUMIF / SUMIFS | Sums to 0 for a criterion that exists | Yes |
COUNTIF | Counts 0 for a value that is clearly in the range | Yes |
| Any of the above with an *internal* space | Still fails after trimming | No — internal spaces are preserved |
Diagnose before you trim
Quick Excel formulas to identify the kind of hidden character so you know whether this tool is the right fix.
| Formula | What a non-zero / TRUE result means | Right fix |
|---|---|---|
=LEN(A2)-LEN(TRIM(A2)) | A2 has regular leading/trailing spaces | This trimmer |
=CODE(LEFT(A2)) returns 160 | A2 starts with a non-breaking space (CHAR 160) | This trimmer (removes edge CHAR 160) |
=CODE(RIGHT(A2)) returns 9 | A2 ends with a tab | This trimmer |
=ISNUMBER(SEARCH(" ",A2)) is TRUE | A2 contains an internal double space | csv-find-replace {2,} → |
=EXACT(A2,B2) is FALSE but they look equal | Hidden character or case difference | Trim, then check case |
Tier limits
Per-file Excel-family limits. A typical lookup table fits the free tier comfortably.
| Tier | Max file size | Max rows | Files at once |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Diagnose-then-fix recipes for the most common 'VLOOKUP says #N/A but the value is right there' situations.
Trailing space on the lookup value
The cell you pass to VLOOKUP has a trailing space; the table is clean. Exact match fails. LEN diagnoses it; the trimmer fixes it.
Lookup value: "SKU-100·" (· = trailing space)
Table key: "SKU-100"
=VLOOKUP("SKU-100·", table, 2, 0) → #N/A
=LEN(A2)-LEN(TRIM(A2)) → 1 (one trailing space)
After trimming both cells become "SKU-100" → match foundCHAR 160 that Excel's TRIM won't remove
The value was pasted from a web table and ends in a non-breaking space. You already tried =VLOOKUP(TRIM(A2),...) and it still returned #N/A — because Excel's TRIM ignores CHAR 160. This tool removes the edge CHAR 160.
Value: "London·" (· = CHAR 160, U+00A0) =CODE(RIGHT(A2,1)) → 160 (it's a non-breaking space) =VLOOKUP(TRIM(A2), table, 2, 0) → still #N/A (Excel TRIM keeps CHAR 160) Whitespace Trimmer → "London" → VLOOKUP matches
Cross-file VLOOKUP — trim both workbooks
Your formula pulls from another workbook. The key column there has leading spaces. Trim each file, then point the formula at the cleaned copies.
values.xlsx key column: " A-12" (leading space) prices.xlsx key column: "A-12" Step 1 trim values.xlsx → "A-12" Step 2 trim prices.xlsx → "A-12" Step 3 re-run cross-file VLOOKUP → match
Internal space — NOT a trimming fix
If MATCH still fails after trimming, check for an internal space. The trimmer leaves these alone by design; use find/replace to collapse them.
Value: "Wid get" Table: "Widget" Trim result: "Wid get" (unchanged — space is internal, not at an edge) Fix: csv-find-replace, find " " → replace "" (or fix the typo at source)
SUMIF returning 0 for a criterion that exists
Same hidden-space cause, different function. SUMIF compares the criterion exactly; a trailing space on either side zeroes the result. Trim the whole range.
=SUMIF(region, "West", sales) → 0 Reason: region cells are "West·" (trailing space); criterion is "West" After trimming the region column → SUMIF returns the real total
Edge cases and what actually happens
Excel TRIM() didn't fix it but the trimmer did
ExpectedExcel's worksheet TRIM() removes only the regular space (CHAR 32). If your #N/A value carried a non-breaking space (CHAR 160) — common after pasting from web pages — =VLOOKUP(TRIM(A2),...) still fails. This tool removes edge CHAR 160, so the lookup that defeated Excel's TRIM now matches.
Internal space still breaks the match
By designWid get versus Widget is an internal-space difference; trimming the edges does not touch it. Either fix the typo at source or collapse spaces with csv-find-replace. The trimmer deliberately preserves internal spacing.
Lookup still #N/A after trimming both files
Check the valueOnce edges are clean, a remaining #N/A means a real difference: a case mismatch in a case-sensitive context, an internal space, a different code point that looks identical (e.g. a Cyrillic 'а'), or the value genuinely isn't in the table. Use =EXACT(A2,B2) to compare two specific cells exactly.
Zero-width space (U+200B) survives
PreservedJavaScript trim does not treat U+200B as whitespace, so an edge zero-width space is left in place and can still break a match. These are rare; strip them with csv-find-replace targeting \u200b.
Approximate-match VLOOKUP (4th arg TRUE)
Different issueIf you used VLOOKUP(...,TRUE) (approximate match) the #N/A/wrong-result cause may be an unsorted lookup column, not whitespace. Trim still helps with exact-match keys, but switch to VLOOKUP(...,FALSE) or XLOOKUP for exact matching.
Multi-sheet workbook — only the first sheet is trimmed
First sheet onlyIf your lookup value is on Sheet1 and the table is on Sheet2 of the same .xlsx, dropping the workbook trims Sheet1 only. Put both ranges on one sheet, or save each sheet as its own file and trim each, then bring them back together.
Numbers stored as text vs real numbers
Different issueA lookup can fail because the key is "100" (text) on one side and 100 (number) on the other — a type mismatch, not whitespace. Trimming will not convert types. Standardise the column type in Excel (Text-to-Columns) if that is the cause.
Formula cells become values
ExpectedIf your key column is itself a formula, the trimmer exports its displayed result (then trims it). The output holds values, not formulas. Re-point your VLOOKUP at the cleaned static values, or do the trim in Excel if you must keep the formulas.
File over the free-tier limit
BlockedA lookup table above 5 MB / 10,000 rows is blocked on the free tier. Upgrade, or split the table and trim each part. Most lookup tables are well under the free ceiling.
Case difference disguised as a match
Check caseExcel's VLOOKUP is case-insensitive, so case is rarely the #N/A cause in Excel — but if you exported to a case-sensitive system, Acme vs acme matters. Trimming won't fold case; use csv-case-converter if needed.
Frequently asked questions
How do I confirm whitespace is the cause before trimming?
In an empty cell enter =LEN(A2)-LEN(TRIM(A2)). A result greater than zero means A2 has leading or trailing spaces. If it returns zero but the lookup still fails, check =CODE(RIGHT(A2,1)) — a 160 means a non-breaking space (which Excel's TRIM() ignores but this tool removes).
I already wrapped my lookup in TRIM() and it still says #N/A — why?
Almost certainly a non-breaking space (CHAR 160). Excel's TRIM() removes only the regular space character and leaves CHAR 160 in place, so VLOOKUP(TRIM(A2),...) keeps failing. This tool's trim removes edge CHAR 160, which is exactly the case Excel's TRIM can't handle.
Does it fix the lookup table or just the lookup value?
Both — it trims every cell on the sheet, so your key column and the table you look into are cleaned in the same pass. For a cross-file VLOOKUP, trim each file so both sides are edge-clean.
Will it fix XLOOKUP, INDEX/MATCH, SUMIF and COUNTIF too?
Yes. All of these do exact text comparison and fail on the same hidden edge whitespace. Trimming the range resolves the #N/A from XLOOKUP/INDEX/MATCH and the silently-zero results from SUMIF/COUNTIF.
What about non-breaking spaces (Alt+0160)?
Removed at the edges. Unlike Excel's TRIM(), this tool treats the non-breaking space (CHAR 160 / U+00A0) as trimmable whitespace and strips it from the start and end of each cell. An internal CHAR 160 is preserved.
My VLOOKUP still fails after trimming — now what?
Edges are clean, so it's a real difference: an internal space (Wid get), a number-stored-as-text type mismatch, a look-alike Unicode character, or the value genuinely isn't in the table. Use =EXACT(A2,B2) to compare two cells exactly and pinpoint it.
Does it handle an internal double space like 'Acme Corp'?
No — it trims edges only and preserves internal spacing. For internal runs, use csv-find-replace with the regex {2,} replaced by a single space, then re-run the lookup.
Do I need to add helper columns like the old TRIM trick?
No. The whole point of this tool is to skip the helper-column-then-paste-special workflow. It trims every cell directly and hands back a clean file you can use immediately.
Is my data uploaded to do the trimming?
No. Everything runs in your browser via SheetJS. The lookup table and values never leave your machine. Only an anonymous run counter is recorded if you're signed in.
It only trimmed one sheet of my workbook — is that a bug?
No, it's by design: the XLSX path processes the first worksheet and returns a single-sheet file. Keep your lookup value and table on the same sheet, or trim each sheet as a separate file.
Why does Excel's VLOOKUP ignore case but my exported lookup doesn't?
Excel's VLOOKUP is case-insensitive, so case rarely causes #N/A inside Excel. But if you moved the data to a case-sensitive system (a database, a script), Acme vs acme matters. Trimming won't fold case — use csv-case-converter for that.
How big a lookup table can I trim for free?
Free tier: 5 MB / 10,000 rows. Pro: 50 MB / 100,000 rows. Pro-media: 200 MB / 500,000. Developer: 500 MB / unlimited. Most lookup tables are far under the free limit.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.