Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the No match error
Excel & Google SheetsWhat No match means
A lookup or comparison that should succeed silently fails when one or both values have leading or trailing whitespace. The characters '"Apple"' and '"Apple "' are different strings — the trailing space makes them unequal, so VLOOKUP returns #N/A and IF returns FALSE even though the values look identical on screen.
Common causes
- Data pasted from a web page or external system included invisible trailing spaces that are not visible in the cell but affect string comparison.
- A formula that concatenates values added an accidental space (e.g., =A1&" "&B1 used as a lookup key).
- A CSV import preserved the spaces that existed between delimiters and values in the source file.
- Leading spaces exist because the source system left-padded values with spaces for alignment (common in legacy database exports).
- Non-breaking spaces (character 160) were imported from HTML content — TRIM does not remove these, requiring SUBSTITUTE or CLEAN.
Example fix
Broken
=VLOOKUP(A2,$D:$E,2,0)Fixed
=VLOOKUP(TRIM(A2),$D:$E,2,0)TRIM(A2) strips leading and trailing spaces from the lookup value before comparing it to the table. If the table column D also has spaces, use TRIM on the table range too, but that requires an array formula or cleaning the table data.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Wrap the lookup value and/or the lookup column in TRIM() to remove all leading and trailing spaces: =VLOOKUP(TRIM(A2),TRIM(D:D),2,0). Note: wrapping a range in TRIM inside VLOOKUP requires an array entry (Ctrl+Shift+Enter in Excel) or ARRAYFORMULA in Sheets.
- 2Clean the source data directly: in a helper column, use =TRIM(A2) and paste-special the results as values back over column A, then delete the helper.
- 3To remove non-breaking spaces (char 160) that TRIM misses: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
- 4Use LEN() to diagnose: if =LEN(A2) returns a higher number than you expect from the visible characters, there are hidden spaces or characters.
- 5In the lookup formula, add TRIM only to the lookup value argument if the table is already clean: =VLOOKUP(TRIM(A2),$D:$E,2,0).
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every No match and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
TRIM did not fix it — the LEN is still too long. What else could there be?
The extra characters may be non-breaking spaces (CHAR(160)), line breaks (CHAR(10) or CHAR(13)), or other invisible Unicode characters. Use =SUBSTITUTE(TRIM(A2),CHAR(160),"") to remove non-breaking spaces, or =CLEAN(TRIM(A2)) to strip line breaks.
Is there a way to find all cells with trailing spaces in a sheet?
Use Find & Replace (Ctrl+H), search for ' ' (a single space) with 'Find All'. This does not distinguish leading from trailing, but highlights cells with any spaces. For precision, use a helper column with =LEN(A2)<>LEN(TRIM(A2)) and filter for TRUE.
Why does TRIM not work inside an array formula for the lookup range?
In Excel, =VLOOKUP(TRIM(A2),TRIM(D:D),2,0) requires Ctrl+Shift+Enter to enter as an array formula; otherwise TRIM only acts on the first cell of D:D. In Google Sheets, wrap in ARRAYFORMULA: =VLOOKUP(TRIM(A2),ARRAYFORMULA(TRIM(D:D)),2,0).