Your VLOOKUP in Google Sheets returns #N/A, and hovering over the cell shows "Did not find value ... in VLOOKUP evaluation." Yet when you look at the table, the value is right there. This guide walks through why that happens and how to fix it in a few minutes.
The short answer: #N/A means the search key has no exact match in the first column of your range. Google's own help page says plainly that if #N/A is returned, a value isn't found. When the value looks identical but still doesn't match, the cause is almost always one of five things: (1) leading or trailing spaces, (2) a number vs. text mismatch, (3) full-width vs. half-width characters, (4) the search column is not the leftmost column of the range, or (5) the range shifted when you copied the formula.
Find the cause from what you see
What you see | Most likely cause | Fix |
|---|---|---|
Only some rows return #N/A, but the value is visibly in the table | (1) spaces or (3) character width | Clean with TRIM / ASC (causes 1 and 3) |
Numeric keys such as product codes or employee IDs fail on almost every row | (2) number vs. text | Match the number format, or convert with VALUE (cause 2) |
Every row returns #N/A | (4) search column is not the leftmost | Start the range at the search column (cause 4) |
The first rows work, but #N/A spreads as you fill down | (5) relative range shifted | Lock the range with $ (cause 5) |
No #N/A, but the result is the wrong value | Approximate match (TRUE) is in effect | Set the fourth argument to FALSE |
The last row is the opposite symptom with the same root. If you leave out VLOOKUP's fourth argument (is_sorted), it defaults to TRUE, which is an approximate match. Google's VLOOKUP help page strongly recommends FALSE. For lookups in business sheets, simply make it a rule: always write FALSE as the fourth argument.
A one-minute test: are the two values really the same?
Before guessing, type this in an empty cell, where A2 is your search key and D5 is the table cell that "should" match:
=A2=D5
If it returns FALSE, the two cells look the same but are not. Then check length and type:
=LEN(A2) =LEN(D5) =ISNUMBER(A2) =ISNUMBER(D5)
Different LEN results mean invisible characters such as spaces (cause 1). If ISNUMBER is TRUE for only one of them, it is a number vs. text mismatch (cause 2). These two checks settle two of the five causes in a minute.
Cause 1: invisible leading or trailing spaces
This is the most common cause after importing a CSV or pasting from another system. Google's help notes that " Apple", "Apple " and "Apple" are all treated differently by VLOOKUP.
- Clean the whole column: select it, then Data → Data cleanup → Trim whitespace
- Clean inside the formula: =VLOOKUP(TRIM(A2), $D$2:$F$500, 3, FALSE)
TRIM removes leading and trailing spaces. If non-printable characters are mixed in, add CLEAN as well. Cleaning inside the formula is the better choice when the table is overwritten by an external import every time.
Cause 2: numbers stored as text
The code "1001" is a number in your key cell but text in the table. It looks the same, but it will not match. If the table side is left-aligned and the key side is right-aligned, this is almost certainly the issue.
- Match the format: select the search key column, then Format → Number and choose Number or Date (Google's recommended step)
- Convert in the formula: =VLOOKUP(VALUE(A2), $D$2:$F$500, 3, FALSE)
- Or convert to text instead: =VLOOKUP(A2&"", $D$2:$F$500, 3, FALSE)
Codes with leading zeros, such as "0012", lose the zeros when converted to numbers and become a different value. In that case, convert both sides to text rather than to numbers.
Cause 3: full-width vs. half-width characters
This one mainly affects sheets with Japanese, Chinese or Korean input, where "ABC-01" and the full-width "ＡＢＣ－０１" look nearly identical. Google Sheets has an ASC function that converts full-width ASCII and katakana characters to half-width.
=VLOOKUP(ASC(TRIM(A2)), $D$2:$F$500, 3, FALSE)
Note that this only normalizes the search key. If the table itself contains full-width characters, add a helper column that applies ASC to the table and look up against that column instead.
Cause 4: the search column is not the leftmost column of the range
VLOOKUP searches only the leftmost column of the range you give it. Google's help states that the search key must be in the first column of the range to return a non-error value, with a quick check: if the search key is in column B, the range must start with column B.
A typical mistake is selecting the whole table from column A while the key actually lives in column C. Restart the range at column C and recount the third argument (index). Count columns from the left edge of your selected range, starting at 1, not from column A of the sheet.
If you need a value from a column to the left of the key, VLOOKUP cannot do it. XLOOKUP in Google Sheets lets you specify the search column and the result column separately, so it has no such restriction.
=XLOOKUP(A2, $C$2:$C$500, $A$2:$A$500, "Not found")
Cause 5: the range shifted when you copied the formula
The first row is correct, but #N/A appears more and more as you fill down. If your range is a relative reference like B3:D7, it moves down one row with every copy, and the top rows gradually fall out of the lookup range. Google's recommended practice is to use an absolute reference such as $B$3:$D$7.
Press F4 to toggle the range to absolute, or reference whole columns like $D:$F so that newly added rows are never left out.
Hiding #N/A, and why not to do it first
If you want rows with no match to show as blank, wrap the formula in IFNA:
=IFNA(VLOOKUP(A2, $D$2:$F$500, 3, FALSE), "")
One warning: if you hide #N/A before fixing the cause, data that really exists will silently show as blank. In invoices or inventory sheets, a single stray space can blank out an amount and leave totals wrong without anyone noticing. Use IFNA only after you have ruled out the five causes above. IFERROR also hides other errors such as #REF!, so for lookup misses specifically, IFNA is the better fit.
If you fix the same #N/A after every monthly import, the sheet itself has hit its limit. Preventing inconsistent codes at the point of entry, or normalizing data automatically on import, usually helps far more than formula tricks. When many people edit the same sheet, problems like this tend to multiply; we cover the common patterns in why shared spreadsheets break and how to fix them.
FAQ
What does #N/A mean in a Google Sheets VLOOKUP?
It means no value matching the search key was found in the first column of the range. With exact match (FALSE as the fourth argument), values that look the same still fail if one has extra spaces or is stored as text instead of a number. With approximate match (TRUE), #N/A also appears when the search key is smaller than the smallest value in the first column.
The value is in the table, so why does VLOOKUP return #N/A?
Compare the two cells with a formula such as =A2=D5. If it returns FALSE, they differ. If LEN gives different lengths, there are leading or trailing spaces; if ISNUMBER is TRUE for only one cell, it is a number vs. text mismatch. Fix spaces with Data cleanup > Trim whitespace or TRIM, and number vs. text by matching the number format or using VALUE.
Can VLOOKUP return a value from a column to the left of the key?
No. VLOOKUP only searches the leftmost column of the range and returns values from columns to its right. To return a column on the left, use XLOOKUP, which lets you set the search range and the result range separately.
How do I show a blank instead of #N/A?
Wrap the formula in IFNA, for example =IFNA(VLOOKUP(A2, $D$2:$F$500, 3, FALSE), ""). But check for spaces and number vs. text mismatches first; hiding the error too early can make data that really exists appear blank.