Your VLOOKUP looked perfect, you pressed Enter, and Excel answered with #N/A. Don’t worry: this is the most common lookup error, and it is almost always caused by one of five simple problems.
#N/A means “Not Available”. Excel is telling you it searched for your value and could not find a match. The formula itself is usually fine; the problem is in the data.
Quick answer: check that the value really exists in the first column of your range, remove extra spaces with TRIM, make sure numbers aren’t stored as text, use FALSE (exact match) as the last VLOOKUP argument, and lock your range with $ signs. If a missing value is expected, wrap the formula in IFNA to show a friendly message instead.
In this guide we’ll go through each cause, show you how to spot it, and fix it step by step.
Why VLOOKUP and XLOOKUP return #N/A
We’ll use this small example throughout. You have a product list in A2:C6 and want to look up the price of a product code typed in E2.
| Code (A) | Product (B) | Price (C) |
|---|---|---|
| P100 | Keyboard | 15 |
| P101 | Mouse | 8 |
| P102 | Monitor | 120 |
| P103 | USB Cable | 3 |
| P104 | Headset | 25 |
The formula in F2 is:
=VLOOKUP(E2, A2:C6, 3, FALSE)These are the five reasons this formula returns #N/A, from most to least common:
| Cause | How to spot it |
|---|---|
| 1. The value really doesn’t exist | Search for it with Ctrl + F; nothing is found |
| 2. Extra spaces | The values look identical but don’t match; LEN shows different lengths |
| 3. Numbers stored as text | Numbers are left-aligned or have a small green triangle |
| 4. Approximate match on unsorted data | The last VLOOKUP argument is TRUE or missing |
| 5. The range moved when copied | It works in the first row, then fails lower down |
There is also a sixth cause that only affects VLOOKUP: the value you search for must be in the first column of the range. VLOOKUP cannot look to the left.
How to fix each cause
Fix 1: Check the value really exists
Press Ctrl + F, type the value from E2, and search. If Excel can’t find it either, the #N/A is correct: the item is not in your list. Check for typos such as the letter O instead of the number 0 (“P1O1” vs “P101”).
Fix 2: Remove extra spaces with TRIM
Data copied from websites, emails or other systems often carries hidden spaces, so “P101 ” does not match “P101”. To check, compare the lengths:
=LEN(E2)If LEN shows 5 for a 4-character code, there is an extra space. Fix it inside the formula:
=VLOOKUP(TRIM(E2), A2:C6, 3, FALSE)If the spaces are in the lookup column itself, add a helper column with =TRIM(A2), copy it, and paste it back as values (Paste Special → Values).
Fix 3: Convert numbers stored as text
To Excel, the number 1001 and the text “1001” are different values, so they never match. This often happens with IDs imported from other software.
- Select the column with the text numbers.
- Click the small yellow warning icon that appears.
- Choose Convert to Number.
Or convert inside the formula. If E2 is text and the list holds real numbers:
=VLOOKUP(VALUE(E2), A2:C6, 3, FALSE)If E2 is a number and the list holds text:
=VLOOKUP(E2&"", A2:C6, 3, FALSE)Fix 4: Use FALSE for an exact match
The last VLOOKUP argument decides the match type. If you leave it out, Excel assumes TRUE (approximate match), which only works on data sorted from smallest to largest. For codes, names and IDs, always use FALSE:
=VLOOKUP(E2, A2:C6, 3, FALSE)XLOOKUP uses exact match by default, which is one reason it causes fewer surprises.
Fix 5: Lock the range with $ signs
When you drag the formula down, A2:C6 shifts to A3:C7, then A4:C8, and the top rows drop out of the search. Lock the range by pressing F4 after selecting it:
=VLOOKUP(E2, $A$2:$C$6, 3, FALSE)Even better, convert the list to a table (Ctrl + T) and use its name, so the range grows automatically when you add rows.
Fix 6: Switch to XLOOKUP when the value isn’t in the first column
If you need to search by product name (column B) and return the code (column A), VLOOKUP can’t do it. XLOOKUP can look in any direction:
=XLOOKUP(E2, B2:B6, A2:A6)XLOOKUP is available in Excel 365, Excel 2021 and later, and Google Sheets. In older Excel versions, use INDEX and MATCH instead. New to XLOOKUP? See our XLOOKUP beginner guide.
How to hide #N/A with a friendly message
Sometimes #N/A is correct: the item simply isn’t in the list yet. In that case, show a clear message instead of an error.
With VLOOKUP, use IFNA:
=IFNA(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not found")With XLOOKUP, use the built-in fourth argument:
=XLOOKUP(E2, $A$2:$A$6, $C$2:$C$6, "Not found")You can also return a blank (“”) or zero (0) instead of text, which is useful when you later add up the results.
IFNA or IFERROR? Prefer IFNA. IFERROR hides every error, including real mistakes such as a wrong column number (#REF!) or a typo in a function name (#NAME?). IFNA hides only “not found”, so genuine problems still show up.
Important: only hide #N/A after you’ve checked the causes above. Covering up a spacing or text-number problem makes your report look clean while showing wrong results.
Does this work in Google Sheets?
Yes. VLOOKUP, XLOOKUP, IFNA, TRIM and VALUE work the same way in Google Sheets, and every formula in this guide can be pasted in as-is. Two small differences: Google Sheets doesn’t show the green triangle for numbers stored as text, so use Format → Number to convert them, and there is no Ctrl + T table shortcut, so lock ranges with $ signs instead.
Quick checklist
When you see #N/A, go through this list in order:
- Does the value exist in the list? (Ctrl + F)
- Is it in the first column of the VLOOKUP range?
- Any extra spaces? (compare with LEN, fix with TRIM)
- Numbers stored as text? (Convert to Number)
- Is the last argument FALSE?
- Is the range locked with $ signs?
- Only then: wrap in IFNA if “not found” is expected
Frequently asked questions
Why does VLOOKUP work in some rows but not others?
Usually the range isn’t locked with $ signs, or only some rows contain extra spaces or text-numbers. Check one failing row with LEN and ISTEXT.
Why does #N/A appear even though I can see the value?
The two values look the same but aren’t identical: a hidden space, a text-number, or a non-breaking space copied from a website. For non-breaking spaces, use =SUBSTITUTE(E2, CHAR(160), “”).
Is XLOOKUP better than VLOOKUP?
For most tasks, yes: it uses exact match by default, can look left, doesn’t break when you insert columns, and has a built-in “not found” option. Use VLOOKUP only if you share files with people on Excel 2019 or older.
Does VLOOKUP care about upper and lower case?
No. “p101” matches “P101”. If you need a case-sensitive match, combine INDEX and MATCH with the EXACT function.
Next: learn more lookup and summary formulas in XLOOKUP in Excel: The Complete Beginner Guide and SUMIFS and COUNTIFS Explained with Real Examples.