Why VLOOKUP Returns #N/A (and 4 Ways to Fix It)
A VLOOKUP that shows #N/A even though the value is in the list usually has one of four causes. Find and fix each one on a small order sheet: a missing FALSE, a table range that stops too early, hidden spaces, and numbers stored as text.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| Code | Product | Price |
|---|---|---|
| 1005 | Monitor Stand | $45 |
| 1002 | Office Chair | $149 |
| 1006 | Wall Clock | $22 |
| 1001 | Desk Lamp | $35 |
| 1004 | Stapler | $12 |
| 1003 | Notebook | $4 |
| 1007 | Desk Fan | $29 |
Paste at cell E1:
| Order | Code | Price |
|---|---|---|
| ORD-1 | 1001 | |
| ORD-2 | 1007 | |
| ORD-3 | '1002 | |
| ORD-4 | '1003 |
Step by step
- VLOOKUP shows #N/A. A VLOOKUP that shows #N/A when the code is clearly in the list usually has one of 4 causes. Here we find and fix each one on a small order sheet.
- The product list. The product list in A1:C8 has a code (column A), a product (column B) and a price (column C). The desk fan in row 8 was added this week.
- The order list. The order list in E1:G5 has 4 orders, each with a product code in column F. We want each order's price in column G.
- The first try. In G2, a common first try is =VLOOKUP(F2,$A$2:$C$7,3). It has the code in F2, the table $A$2:$C$7, and column 3 for the price. Fill it down to G5. Order 1 shows #N/A, even though code 1001, the desk lamp, is in the list. Order 2 shows $4, which is the wrong price.
G2
=VLOOKUP(F2,$A$2:$C$7,3) - Exact match. Cause 1: no exact match. If you leave out the last part, VLOOKUP does an approximate match and assumes the first column is sorted. This list isn't sorted, so add ,FALSE to the end. Fill it down. Order 1 now shows $35 for the desk lamp. But order 2 now shows #N/A.
G2
=VLOOKUP(F2,$A$2:$C$7,3,FALSE) - The range. Cause 2: the table range is too short. Order 2 wants the desk fan in row 8, but $A$2:$C$7 stops at row 7. Change the 7 to an 8, so the range $A$2:$C$8 covers every row. Fill it down. Order 2 now shows $29 for the desk fan.
G2
=VLOOKUP(F2,$A$2:$C$8,3,FALSE) - Hidden spaces. Cause 3: hidden spaces. The code for order 3 has a space at the end. You can't see it, but Excel counts it, so the code no longer matches. Wrap F2 in TRIM, which removes extra spaces. Fill it down, and now every order shows #N/A. TRIM always gives back text, and VLOOKUP won't match the text "1001" to the number 1001 in column A.
G2
=VLOOKUP(TRIM(F2),$A$2:$C$8,3,FALSE) - Text numbers. Cause 4: numbers stored as text. The last 2 codes sit on the left of their cells. With default alignment, that means Excel sees them as text. Wrap everything in VALUE, which turns text into a real number. Fill it down one last time. The last 2 orders now show $149 for the chair and $4 for the notebook, and no #N/A is left.
G2
=VLOOKUP(VALUE(TRIM(F2)),$A$2:$C$8,3,FALSE) - Mark the formulas. Give G2:G5 a light green fill (#E2EFDA), so the team knows these cells hold formulas and doesn't type over them.
- 4 causes of #N/A. When VLOOKUP shows #N/A, check 4 things. Add FALSE for an exact match. Make the table range cover every row. Use TRIM on hidden spaces. Turn text into numbers with VALUE.
Common mistakes
- Using VALUE on codes with letters. VALUE only works on text that looks like a number, so a code like P103 gives #VALUE!. When codes contain letters, use TRIM on its own.
- Spaces in the product list. TRIM(F2) only cleans the value you look up. If the codes in column A have spaces, clean that column too. Use =VALUE(TRIM(A2)) in a helper column for number codes, or TRIM on its own for codes with letters, then paste the results back as values.
- A range that stops when new rows are added. $A$2:$C$8 will miss the next product added in row 9. Turn the product list into an Excel table (Ctrl+T) and point VLOOKUP at the table, so new rows are included automatically.
- Leaving out the $ signs. With A2:C8 instead of $A$2:$C$8, the range moves down 1 row each time you fill down, and the top rows drop out. Lock the range with $ signs before you fill.
When a business analyst uses this
In a requirements log, you might pull each requirement's owner and status from a stakeholder list exported from another system. Exports often add spaces at the end or store IDs as text. So a column of #N/A before a sign-off review is usually one of these 4 causes, not missing owners.
Questions people ask
Why is VLOOKUP returning #N/A when the value exists?
Usually the 2 values aren't exactly the same. One may have a hidden space, or one may be a number and the other text. TRIM removes the spaces and VALUE turns text into a number.
Why does my VLOOKUP keep returning #N/A after I fill it down?
Check the table range. Without $ signs, like A2:C8 instead of $A$2:$C$8, the range moves down 1 row each time you fill, and codes near the top drop out of it.
Why is VLOOKUP not returning a value?
#N/A means the code wasn't found in the first column of the table range. Check that you ended the formula with FALSE, that the range covers every row, and that there are no hidden spaces or numbers stored as text.
