VLOOKUP #N/A Error in Google Sheets: Causes and Fixes
FALSE that leaves approximate match on, or a key that is not in the first column of the range. Fix the cause, then use IFNA to show a clean message.
What #N/A means in VLOOKUP
Google's help page says VLOOKUP returns #N/A when no exact match was found, or when an approximate match was used and the key is smaller than the smallest value in the first column. In plain terms: Sheets looked and could not find your value where it expected it. The fix depends on why.
The example
Here is a product table in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | Price |
| 2 | P-101 | Mug | 6.5 |
| 3 | P-102 | Lamp | 24 |
| 4 | P-103 | Backpack | 39 |
| 5 | P-104 | Notebook | 8.75 |
Cause 1: the key really is not there
A lookup for P-999 returns #N/A because there is no such ID. That is the correct answer. To show a message instead of an error, wrap the formula in IFNA, as Google's page suggests:
=IFNA(VLOOKUP("P-999", A2:C5, 3, FALSE), "Not found")
This returns Not found. IFNA only replaces #N/A, so other errors still show, which makes real mistakes easier to spot than with IFERROR.
Cause 2: extra spaces
A key that looks right can carry a trailing space. Here E2 holds P-103 followed by one space.
| E | |
|---|---|
| 1 | Search key |
| 2 | P-103 |
A direct lookup of E2 fails, because "P-103 " is not equal to "P-103". Trim the key:
=VLOOKUP(TRIM(E2), A2:C5, 2, FALSE)
This returns Backpack. Google's page recommends trimming extra spaces. The data in the table can have the same problem, so clean that side too.
Cause 3: numbers or dates stored as text
The number 101 and the text "101" look the same but do not match. Google's page says not to store numbers or dates as text. Convert the column to real numbers, or make both sides the same type.
Cause 4: approximate match left on
If you leave out the last argument, VLOOKUP uses TRUE, an approximate match that needs the first column sorted ascending. On unsorted data it can return wrong values or #N/A. Always add FALSE for exact lookups.
Cause 5: the key is not in the first column
VLOOKUP searches only the first column of the range. If your range starts at column B but the key is in column A, nothing matches. Start the range at the column that holds the key. To look to the left, see how to VLOOKUP to the left.
Other VLOOKUP errors
- #REF!: the index is larger than the number of columns in the range.
- #VALUE!: the index is text, less than 1, or a mistyped column name.
- #NAME?: a text search key is missing its quotation marks.
Try it yourself
Use the tables above. Type your formula into the box, starting with =, then press Check.
Exercise 1: Hide the error
Write a formula that looks up "P-999" in A2:C5 (price, column 3, exact match) and shows Not found instead of #N/A. Use IFNA.
Exercise 2: Fix the stray space
Cell E2 holds P-103 with a trailing space. Write a VLOOKUP that looks it up in A2:C5, returns the Name (column 2), uses an exact match, and trims the key first.
What to learn next
- VLOOKUP: fetch one value from a table.
- IFERROR: replace an error with a clean result.
- XLOOKUP vs VLOOKUP: which lookup function to use and why.
- IFNA: replace only #N/A errors.
- IFNA: replaces only #N/A and leaves other errors visible.
Syntax and rules in this guide were checked against Google's VLOOKUP #N/A error help page. See all guides.