Fix VLOOKUP #N/A Caused by a Hidden Space in Google Sheets

1. Build the hidden-space key

The practice workbook lists three products: PR one-oh-one costs twelve dollars, PR one-oh-two costs eight, and PR one-oh-three costs twenty-five. To make the hidden-space error repeatable, enter the formula shown on screen in E two: quoted PR dash one-oh-two joined to character thirty-two. Google Sheets may trim a space typed directly into a cell, so this formula generates one ordinary trailing space reliably.

Checkpoint: Products in A2:B4; E2: ="PR-102"&CHAR(32); Price is column 2.

2. Write an exact-match lookup

In an empty result cell, enter VLOOKUP with E two, absolute range A two through B four, index two, and FALSE. The exact formula is on screen and in the guide. FALSE is important because omitting the last argument can use approximate matching. The expected first result is number not available; that is useful evidence that the visually similar key is not actually equal.

Checkpoint: =VLOOKUP(E2,$A$2:$B$4,2,FALSE); FALSE means exact; Expected: #N/A.

3. Prove the hidden character

In another empty cell, enter LEN of E two. It returns seven, although PR one-oh-two has only six visible characters. Then enter LEN of TRIM E two. That returns six. This pair proves one removable ordinary space exists in the request. Do not assume every VLOOKUP error has this cause; a wrong range, missing SKU, or number stored as text needs a different diagnosis.

Checkpoint: =LEN(E2) returns 7; =LEN(TRIM(E2)) returns 6; One extra space.

4. Clean the lookup key

Wrap E two in TRIM inside the VLOOKUP search key: equals VLOOKUP, open parenthesis, TRIM of E two, comma, the same absolute two-column range, comma two, comma FALSE, close parenthesis. The clean key now matches PR one-oh-two in the first column, so the returned price is eight. The source product table is not changed by this formula.

Checkpoint: =VLOOKUP(TRIM(E2),$A$2:$B$4,2,FALSE); Expected: 8; Source table untouched.

5. Do not hide a bad lookup

IFNA can replace the error with a friendly message, but it does not tell you whether a key is wrong or a product is missing. First inspect the range, exact-match argument, and key length. TRIM handles ordinary spaces but not every invisible Unicode character. If the corrected lookup still fails, inspect the exact characters and whether the SKU is text in both locations before changing the business result.

Checkpoint: Diagnose before IFNA; TRIM has limits; Check text types.

Check the finished result

The final answer is not just the absence of an error. PR one-oh-two must map to eight dollars, while the other product prices remain twelve and twenty-five. The length test explains why the original exact match failed. Compare your formulas and results with the answer file; if you get a different product, check the first column and the index rather than accepting an approximate match.

Expected answer: A2:B4 contains PR-101 $12, PR-102 $8, and PR-103 $25. Put ="PR-102"&CHAR(32) in E2; it evaluates to PR-102 plus one trailing ordinary space. LEN(E2) returns 7; LEN(TRIM(E2)) returns 6. =VLOOKUP(E2,$A$2:$B$4,2,FALSE) gives #N/A, while =VLOOKUP(TRIM(E2),$A$2:$B$4,2,FALSE) returns 8. The source SKU in A3 remains unchanged.

If it still fails

If the exact-match lookup still returns #N/A, compare LEN(E2) with LEN(TRIM(E2)), confirm PR-102 is in the first column of A2:B4, and keep FALSE as the final argument. TRIM will not repair every hidden Unicode character.

Official references

• Google Sheets official help
• Google Sheets official help

Checked 16 October 2026. On-screen explanatory panels are labelled illustrative diagrams; any separately identified actual capture is a genuine English app view.