VLOOKUP Errors in Excel: Causes, Checks, and Fixes

Formulas & functions · Updated November 19, 2026

Most VLOOKUP errors come from a lookup value that does not match the first column of the search range, an incorrect column number, or an unintended approximate match. To look for an exact match, set VLOOKUP’s final argument to FALSE; then check that the lookup value and search column contain the same kind of data and that the selected range includes the return column.

How VLOOKUP works

VLOOKUP searches vertically down the first column of a range, finds a matching value, and returns a value from another column in the same row. Its column number is counted from the left edge of the range, not from the worksheet’s column letters. If the search range is B2:D20, for example, B is column 1, C is column 2, and D is column 3.

The function has four arguments: the value to find, the table range, the number of the return column, and whether to use an exact or approximate match. The last argument matters especially. FALSE requests an exact match. TRUE requests an approximate match, and omitting the argument also uses approximate matching. Approximate matching is useful for sorted ranges such as tax bands or grading thresholds, but it can return a plausible-looking wrong answer if the first column is not sorted in ascending order.

VLOOKUP also searches only the first column of its selected range and returns data to its right. If the lookup column is not on the left, VLOOKUP cannot search it directly. These limits explain many cases where a formula seems close but does not behave as expected.

What common VLOOKUP errors mean

  • #N/A: Excel did not find a match under the formula’s matching rules. The value may truly be absent, or it may differ because of spaces, text-versus-number storage, or a slightly different spelling.
  • #REF!: The requested return-column number points beyond the selected table range, or a referenced range was deleted or otherwise became invalid.
  • #VALUE!: An argument is the wrong type or the column number is invalid, such as zero or a negative number.
  • #NAME?: Excel may not recognize the function name, or text in the formula may not be entered as a quoted text value.
  • A result that looks valid but is wrong: The formula may be using approximate matching, returning the first of several duplicate matches, or searching a range whose columns were counted incorrectly.

A formula can also be syntactically valid and still produce an unexpected answer. For example, approximate matching does not necessarily return #N/A when the data is unsorted; it may return a nearby row instead. When accuracy depends on an exact ID, product code, or name, use FALSE and verify the result.

Check the match type and column number

For a basic exact-match lookup, with product IDs in A2:A4, product names in B2:B4, prices in C2:C4, and the ID P-104 in E2, use:

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

The formula returns the value from the third column of the selected range for the matching product ID. If P-104 has a price of 18.5 in C4, the result is 18.5. The dollar signs keep the search range fixed if you copy the formula to other rows; E2 remains relative so it can change to E3, E4, and so on.

Count the return column from the range’s first column. If you change the range from A:C to B:C, the price is now column 2 of that range, not column 3. A column number that is too large causes #REF!, while a column number that is technically within the range but points to the wrong field can return an incorrect answer without an error.

Troubleshoot a VLOOKUP step by step

  1. Confirm the search column. The lookup value must be compared with the leftmost column in the table range. If your IDs are in column C, a range beginning in B will search B, not C.
  2. Use exact match for identifiers. For customer IDs, SKUs, names, and most record lookups, set the final argument to FALSE. Do not rely on the default when an exact result is required.
  3. Check the range boundaries. Make sure the range starts with the lookup column and extends far enough to include the return column. Count the return column from the range’s left edge.
  4. Compare the underlying values. A number stored as text is not always equivalent to the same number stored numerically. Formatting a cell to look like a number does not necessarily change the value’s storage type. Check both the source and lookup cells.
  5. Look for invisible differences. Leading or trailing spaces and nonprinting characters can prevent a text match. Inspect the source data or clean it in a helper column; TRIM removes ordinary extra spaces, while CLEAN removes certain nonprinting characters.
  6. Check duplicates and spelling. VLOOKUP returns the first matching row it encounters. If the key appears more than once, confirm that the first occurrence is the intended record. For names, also check punctuation, spaces, and spelling.
  7. Inspect the formula itself. In Microsoft 365 for Windows, select the cell and use Formulas > Formula Auditing > Evaluate Formula to follow how Excel evaluates its parts. Check that the lookup reference, range, and column number are the ones you intended.

Excel’s formula argument separator depends on regional settings. The examples here use commas; some installations expect semicolons instead. If Excel reports a formula-entry problem even though the function and references look right, check the separator convention used by your installation.

Distinguish a missing match from messy data

When an exact-match formula returns #N/A, first decide whether the lookup key should exist in the source list. If it should, compare the two cells’ actual contents rather than just their appearance. A code such as 00427 can be stored as text to preserve its leading zeros, while 427 stored as a number is a different value for lookup purposes. Similarly, a cell can display a date in a familiar format while its underlying value or type differs from the lookup key.

For text keys, inspect spaces and unusual characters. A helper column can standardize the source values, but apply the same cleanup approach to the lookup value as well. Be cautious about removing characters indiscriminately: spaces or punctuation may be meaningful in some IDs. If a workbook imports data from another system, check whether the imported column is consistently typed and cleaned before changing the formula.

VLOOKUP’s exact text matching is not case-sensitive, so it does not distinguish ABC from abc. If the data contains duplicate keys, it returns the first matching row from top to bottom. These are not formula errors, but they can explain why the returned record is not the one a reader expected.

Show a useful message without hiding problems

If a missing record is an expected possibility, you can replace only the #N/A result with a clear message. With product IDs in A2:A4, prices in C2:C4, and the lookup ID in E2, use:

=IFNA(VLOOKUP(E2,$A$2:$C$4,3,FALSE),"Not found")

If P-104 exists and its price is 18.5, this returns 18.5; if E2 contains an ID with no exact match, it returns Not found. IFNA is often preferable here to IFERROR because it leaves other errors, such as #REF!, visible for investigation. A broad error handler can make a broken range or invalid column number look like an ordinary missing record.

Use a message that fits the purpose of the workbook. In a report, Not found may be clearer than a raw #N/A. In a data-cleaning sheet, leaving the error visible may be more useful because it highlights an unresolved key that needs attention.

Prevent errors in copied formulas

When filling a formula down a column, lock the table range with dollar signs so it does not slide as the formula is copied. Keep the lookup-cell reference relative if each row should search for a different key. If the source data grows, consider using an Excel Table: structured table references can expand as rows are added, reducing the chance that a lookup range stops before newer records.

Keep lookup keys consistent at the point where data is entered or imported. Avoid mixing numbers and text for the same kind of ID, and preserve leading zeros when they are part of the identifier. Also document approximate-match formulas: sorted threshold tables are a legitimate use, but a future editor may mistake TRUE or an omitted final argument for an exact lookup.

Finally, test representative cases: a key that exists near the top and bottom of the list, a key that is absent, and any key with leading zeros or unusual characters. This helps reveal range limits, duplicate-key behavior, and formatting inconsistencies before the results are used elsewhere.

When to use XLOOKUP or another alternative

VLOOKUP is still useful in older workbooks and for simple left-to-right searches. In Microsoft 365 and Excel 2021 or later, XLOOKUP is often easier to maintain: it can search one range and return from another without counting a column number, and it can look to the left as well as the right. This avoids two common VLOOKUP limitations, though the lookup values still need to match correctly.

INDEX with MATCH is another established alternative and works in older Excel versions. It can return values from either side of the lookup column, but its syntax is less direct for many users. If a workbook must be opened in older versions, check function compatibility before replacing VLOOKUP with XLOOKUP. For a simple VLOOKUP repair, correcting the range, match type, or source data is often the smallest and safest change.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
Check whether the lookup value is in the first column of the selected range and whether both values have the same underlying type. Extra spaces, hidden characters, text-versus-number differences, or a misspelling can make values that look alike fail an exact match.

Why is VLOOKUP returning the wrong row?
Check whether the last argument is TRUE or omitted, which requests approximate matching. Use FALSE for an exact lookup. If the formula already uses FALSE, look for duplicate keys: VLOOKUP returns the first match in the range.

What does #REF! mean in VLOOKUP?
Usually, the column number asks for a column beyond the right edge of the selected range, or a referenced cell or range was deleted. Recount the return column from the first column in the table range and verify the range still exists.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s text matching is not case-sensitive, so values that differ only by capitalization are treated as matches. If case must distinguish records, choose a different approach designed for case-sensitive comparison.

Can VLOOKUP search to the left?
No. The lookup field must be the first column of the selected range, and VLOOKUP returns from a column to its right. XLOOKUP in Microsoft 365 or Excel 2021 and later, or INDEX with MATCH, can handle lookups in either direction.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.