VLOOKUP Not Working? Fix #N/A, #REF!, and Wrong Results

VLOOKUP returns an error for a small number of predictable reasons. The most common cause of #N/A is that the lookup value isn’t in the first column of your range — VLOOKUP only searches left to right. If your formula is broken, work through the five-point checklist below first; it resolves the vast majority of failures.

The 5-point checklist (fixes ~90% of failures)

Before editing anything, check these in order:

1. Is the lookup value in the first column of the range? VLOOKUP searches that column only.
2. Is the last argument FALSE? In Excel this is range_lookup; in Google Sheets it’s called is_sorted. It must be FALSE for an exact match.
3. Is the range locked? $A$2:$B$100, not A2:B100, if you plan to copy the formula down.
4. Do the types match? A number stored as text (“1001”) never equals the number 1001. Wrap values in TRIM() and/or VALUE().
5. Does the value actually exist? Sometimes the answer is simply that the row isn’t in your table.

If all five are correct and it still fails, the section for your specific error below will pinpoint it.

Error: #N/A — value not found

#N/A means VLOOKUP reached the end of the first column and never matched your lookup value. There are four usual culprits.

Cause 1: Leading or trailing spaces

"B200" and "B200 " are different strings, so an invisible space breaks the match. Wrap the lookup value in TRIM():

=VLOOKUP(TRIM(E2), $A$2:$B$100, 2, FALSE)

If the spaces are non-breaking or junk characters, TRIM() won’t remove them all — use CLEAN() as well, or fix the source data.

Cause 2: Number stored as text

If column A contains text-formatted numbers (a common result of CSV imports), Excel treats "1001" and 1001 as different. Convert the lookup value with VALUE():

=VLOOKUP(VALUE(E2), $A$2:$B$100, 2, FALSE)

Or convert the source column: select it, then Data → Text to Columns → Finish to force numbers back to numeric.

Cause 3: The lookup column isn’t the first column

VLOOKUP always searches the leftmost column of the range. If your IDs are in column B but you wrote A:B, the search looks in column A and fails. Either reorder the data so the lookup column is first, or switch to INDEX/MATCH / XLOOKUP.

Cause 4: The value genuinely isn’t there

Check for a real misspelling or a row that was never added. =ISNUMBER(MATCH(E2, $A$2:$A$100, 0)) returns TRUE/FALSE and confirms whether the value exists at all.

Error: #REF! — column index out of range

#REF! means your col_index_num points past the end of the range. If your range is A:B (2 columns wide) but you ask for column 3, there is nothing there:

=VLOOKUP(E2, $A$2:$B$100, 3, FALSE)   →   #REF!

Count the columns from the left edge of the range. Ask for 1 for the first column, 2 for the second, and so on — then widen the range if you need a column beyond it:

=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)

Note: after deleting a column, existing formulas shift and can also produce #REF!. Rebuild the range to fix it.

Wrong result with no error: the TRUE trap

This is the most dangerous failure because it returns a plausible-looking but wrong value instead of an error. It happens when the last argument is left as TRUE (or omitted, since TRUE is the default).

• In Excel, the argument is range_lookup. TRUE = approximate match.
• In Google Sheets, the same argument is called is_sorted. TRUE = approximate match.

With TRUE, VLOOKUP assumes the first column is sorted ascending and returns the closest value that does not exceed the lookup value — not necessarily an exact match. Look up “Pear” in an unsorted list and you can get the row for “Orange”. For text lookups this is almost never what you want.

The fix is always to set the final argument to FALSE:

=VLOOKUP(E2, $A$2:$B$100, 2, FALSE)

Use TRUE only for genuine banded lookups (tax brackets, grade scales, postcode ranges) where the first column really is sorted ascending.

Only returns the first match

VLOOKUP stops at the first matching row and cannot return duplicates. If your table has the same key more than once, every copy of the formula shows the same first hit.

Options:

• If duplicates are errors in the data, clean them up first — see how to remove duplicates in Excel.
• If you need all matches, use FILTER (Excel 365 / Google Sheets) or a helper column that makes each key unique (e.g. A2 & "-" & COUNTIF($A$2:A2, A2)).

Can’t look to the left

VLOOKUP physically cannot return a value from a column to the left of the lookup column. The two fixes:

• XLOOKUP (Excel 365, Excel 2021+, Google Sheets) — no column counting, any direction:

  =XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100, "Not found")

• INDEX + MATCH — works in every version of Excel:

  =INDEX($A$2:$A$100, MATCH(E2, $B$2:$B$100, 0))

Ready-reference table

For the full formula walkthrough, see how to use VLOOKUP in Excel or VLOOKUP in Google Sheets.

When to abandon VLOOKUP for XLOOKUP

Once you’ve fixed the errors above, consider replacing the formula entirely. XLOOKUP removes most of the reasons these errors happen:

• It searches in any direction, so no more “can’t look left.”
• It uses a range for the return value, so col_index_num counting (and #REF!) disappears.
• It defaults to an exact match, so the TRUE trap can’t catch you.
• It has a built-in if-not-found argument instead of an ugly #N/A.

=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "Not found")

The catch: XLOOKUP needs Excel 365, Excel 2021 or later, or Google Sheets. On Excel 2019 and older, INDEX/MATCH is the portable alternative.

FAQ

Why does VLOOKUP return #N/A when the value clearly exists?

Usually a hidden space (use TRIM), a text-vs-number mismatch (use VALUE), or the lookup value sitting in a column to the right of the range’s first column.

Can VLOOKUP look left?

No. The lookup value must be in the leftmost column of the range. Use INDEX/MATCH or XLOOKUP to search right-to-left.

Why does my VLOOKUP work in the first row but break when I copy it down?

Your range isn’t locked. Change A2:B100 to $A$2:$B$100 so it doesn’t shift as the formula copies.

Why do I get the wrong value instead of an error?

The last argument is TRUE (Excel range_lookup, Google Sheets is_sorted), which performs an approximate match. Set it to FALSE for an exact match.

What’s the difference between range_lookup and is_sorted?

They’re the same argument with different names — range_lookup in Excel, is_sorted in Google Sheets. Both must be FALSE for an exact match; TRUE enables approximate matching.