How to fix VLOOKUP #N/A caused by extra spaces
Excel formula
=XLOOKUP(G1,TRIM(A2:A9),C2:C9)Trim the lookup column inside the formula: =XLOOKUP(G1,TRIM(A2:A9),C2:C9). Better still, clean the column once with TRIM.
- Result on the example
- $449.00 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
"P-104 " with a trailing space and "P-104" are different text, so an exact match fails and returns #N/A even though the SKU looks right. TRIM(A2:A9) strips leading and trailing spaces from every SKU before XLOOKUP compares them, so the lookup finds the standing desk.
=XLOOKUP(G1,TRIM(A2:A9),C2:C9)Result449
G1"P-104"The value in G1.
A2:A98 rows × 1 col: "P-101", "P-102 ", "P-103", "P-104 ", …A range: 8 cells in one column.
TRIM(A2:A9){8 rows × 1 col} "P-101", "P-102", "P-103", "P-104", …TRIM: Removes extra spaces, leaving single spaces between words.- text
A2:A98 rows × 1 col: "P-101", "P-102 ", "P-103", "P-104 ", …
- text
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …A range: 8 cells in one column.
XLOOKUP(G1,TRIM(A2:A9),C2:C9)449XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"P-104" - lookup_array
TRIM(A2:A9){8 rows × 1 col} "P-101", "P-102", "P-103", "P-104", … - return_array
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
- lookup_value
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(XLOOKUP(G1,TRIM(A2:A9),C2:C9)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C9,MATCH(G1,TRIM(A2:A9),0)) | $449.00 | Works in Excel 2019 and earlier too. In Excel 2016 and older, confirm it with Ctrl+Shift+Enter. |
| =VLOOKUP(G1&"*",A2:D9,3,FALSE) | $449.00 | A wildcard after the value tolerates trailing spaces in the table, but it would also match a longer SKU such as P-1040. |
| =VLOOKUP(G1,A2:D9,3,FALSE) | #N/ADifferent result | Shown for contrast: the untrimmed lookup. #N/A, because "P-104 " in A5 has a trailing space. |
Common mistakes
- TRIM doesn't remove the non-breaking space (CHAR(160)) that web pages use. Replace it first:
SUBSTITUTE(A2:A9,CHAR(160),""). - If the space is in the lookup value instead, trim that side:
=XLOOKUP(TRIM(G1),A2:A9,C2:C9). - Trimming inside every formula slows big sheets. Fix the source once: a helper column with
=TRIM(A2), then Paste Values over the original.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix VLOOKUP #N/A caused by extra spaces
G2, return the price of the SKU in G1, even though some SKUs in column A have a trailing space.Questions
How do I find the cells with extra spaces?
=LEN(A2)<>LEN(TRIM(A2)) is TRUE for any cell that has them.
Why does =A5=G1 say FALSE when they look the same?
One of them has a hidden character. Compare LEN(A5) with LEN(G1) to find it.
Can VLOOKUP trim the table?
In Excel 2021 or Microsoft 365, =VLOOKUP(G1,TRIM(A2:D9),3,FALSE) works, but TRIM turns the prices into text too. Trimming only the search column, as XLOOKUP and INDEX/MATCH allow, keeps the results as numbers.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.