# How to fix VLOOKUP #N/A caused by extra spaces

Trim the lookup column inside the formula: =XLOOKUP(G1,TRIM(A2:A9),C2:C9). Better still, clean the column once with TRIM.

**Excel:** `=XLOOKUP(G1,TRIM(A2:A9),C2:C9)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(XLOOKUP(G1,TRIM(A2:A9),C2:C9))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-104 |
| P-101 | Desk lamp | 39.99 | 140 |  | Price |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

Formula in G2: `=XLOOKUP(G1,TRIM(A2:A9),C2:C9)`

Result: $449.00

## 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.

## Other ways

- `=INDEX(C2:C9,MATCH(G1,TRIM(A2:A9),0))`: Works in Excel 2019 and earlier too. In Excel 2016 and older, confirm it with Ctrl+Shift+Enter.
- `=VLOOKUP(G1&"*",A2:D9,3,FALSE)`: 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)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/vlookup-na-extra-spaces