VLOOKUP Excel Troubleshooting: Fix Common Formula Errors

Errors & fixes · Updated November 18, 2026

To troubleshoot VLOOKUP in Excel, check that the lookup value is in the first column of the selected range, use FALSE for an exact match, confirm the column number, and make sure the lookup value and source data have matching types and characters. These checks resolve many #N/A errors and incorrect results; the right fix depends on what the formula returns.

Start with the VLOOKUP formula

VLOOKUP searches down the first column of a table and returns a value from another column in the same row. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The final argument controls whether Excel looks for an exact or approximate match. For troubleshooting, use FALSE unless you specifically need an approximate match.

For example, suppose A2:A6 contains product codes and B2:B6 contains product names. Cell D2 contains the code to search for, and E2 should display its name. With product code P104 in D2 and P104 in A4, the formula returns the name from B4:

With codes in A2:A6, names in B2:B6, and the search code in D2, enter this in E2; if A4 is P104, it returns the name in B4:

=VLOOKUP(D2,A2:B6,2,FALSE)

The range A2:B6 starts with the lookup column, and 2 means return the value from the second column of that range. The formula does not mean “return column B” in every case; the number is counted from the left edge of the selected range.

How VLOOKUP searches

VLOOKUP compares the lookup value with entries in the leftmost column of table_array. When it finds a match, it returns a value from the same row at the position specified by col_index_num. It searches vertically, not horizontally, and it cannot return a value from a column to the left of the lookup column. These design limits explain many formulas that appear close to correct but cannot produce the intended result.

With FALSE, VLOOKUP requests an exact match. With TRUE, or if the final argument is omitted, it uses approximate matching. Approximate matching expects the first column to be sorted in ascending order; otherwise, results may be unexpected. A common misconception is that omitting the last argument is equivalent to an exact match. It is not: explicitly include FALSE for ordinary code, name, or ID lookups.

Fixing #N/A: no exact match found

#N/A usually means Excel could not find the lookup value in the first column of the selected range. It does not necessarily mean the value is absent from the worksheet. The displayed entries can look identical while their underlying values differ.

  • Check the selected range. Confirm the lookup column is the range’s first column and that the range includes the row containing the sought value.
  • Check for extra spaces or hidden characters. A value such as North can differ from North. Inspect the source and lookup cells, or clean imported data before searching.
  • Compare data types. The number 104 and the text value "104" may not match. Look for numbers stored as text or IDs imported with inconsistent formatting.
  • Check spelling and punctuation. Hyphens, nonbreaking spaces, and look-alike characters from copied data can prevent an exact match.
  • Check the match argument. Make sure the formula ends with FALSE when an exact match is required.

If the source data has unwanted leading or trailing ordinary spaces, Excel’s TRIM function can help create a cleaned helper column. For example, with raw names in A2:A20, place this in B2 and fill it down; if A2 contains North , B2 returns North:

=TRIM(A2)

TRIM does not remove every kind of invisible character, such as a nonbreaking space often found in copied web data. If cleaning does not resolve the mismatch, inspect the source or use a deliberate cleaning step rather than assuming all spaces are identical.

Fixing a wrong result or the wrong row

A VLOOKUP formula can return a result without an error and still be wrong. First inspect col_index_num: it counts columns inside the selected range. If the range is C:F, then C is column 1, D is 2, E is 3, and F is 4. Using 3 returns the third column of that range, not worksheet column C.

Next check for duplicate lookup values. With exact matching, VLOOKUP returns the first matching row it encounters from the top of the first column. If an ID appears more than once with different associated details, the formula may return a valid but unintended entry. Remove unintended duplicates or use a lookup method that can return all matching rows when that is what the task requires.

Also check whether the formula accidentally uses approximate matching. If TRUE is supplied, or the final argument is left out, an unsorted lookup column can produce a plausible but incorrect result. Change the final argument to FALSE for an exact ID or text lookup.

Fixing #REF!, #VALUE!, and #NAME? errors

#REF! means a reference is invalid. In VLOOKUP, a common cause is a column index larger than the number of columns in the selected range. For example, asking for column 4 from a three-column range cannot work. Deleting a worksheet column that a formula relies on can also break references. Recheck the range and count its columns from left to right.

#VALUE! can appear when the column index is not a usable positive number, or when a referenced input is itself problematic. Confirm the third argument is a valid column number and that it does not evaluate to zero or a negative value.

#NAME? often means Excel does not recognize part of the formula, such as a misspelled function name or text entered without quotation marks. Check that the function is spelled VLOOKUP, that text criteria use straight double quotation marks where needed, and that argument separators match your Excel regional settings. Many installations use commas; some locales use semicolons.

Keep the lookup range from shifting

When copying a formula down a column, a relative table range moves unless you lock it. For example, copying a formula that refers to A2:B6 down one row can change the range to A3:B7, potentially excluding the first record and including an unintended row. Absolute references keep the source range fixed.

With codes in A2:A6, descriptions in B2:B6, and lookup values in D2:D10, enter this in E2 and fill down; for a matching code, it returns the description from column B while keeping the source range fixed:

=VLOOKUP(D2,$A$2:$B$6,2,FALSE)

The dollar signs lock both endpoints of the table range. The lookup reference D2 remains relative, so it becomes D3, D4, and so on as the formula is copied down. In Excel for Microsoft 365 on Windows, select a reference while editing a formula and press F4 to cycle through reference styles. On Mac, the shortcut can depend on keyboard settings; you can also type the dollar signs directly.

Make missing results easier to read

If a missing match is an expected possibility, you can display a helpful message instead of #N/A. Use IFNA to handle that particular error while allowing other formula errors to remain visible. Hiding every error with IFERROR may conceal a broken range or invalid column number, so use it only when that broader behavior is intentional.

With codes in A2:A6, descriptions in B2:B6, and the search code in D2, enter this in E2; if D2 is absent from A2:A6, the result is Not found, otherwise the matching description is returned:

=IFNA(VLOOKUP(D2,$A$2:$B$6,2,FALSE),"Not found")

This changes how a missing match is displayed; it does not repair a mismatch between text and numbers or a flawed source range. Fix the underlying cause if the lookup should have found a value.

A practical troubleshooting sequence

When a formula is difficult to diagnose, change one thing at a time. This makes it easier to tell whether the cause was the lookup value, the range, or the match setting. A useful sequence is:

  1. Click the formula cell and inspect the complete formula in the formula bar.
  2. Verify the first column of the selected range contains the values being searched for.
  3. Count the range columns and compare that count with col_index_num.
  4. Set the final argument to FALSE unless approximate matching is deliberate.
  5. Compare the lookup cell with a source entry, checking spaces, punctuation, and number-versus-text formatting.
  6. Check for duplicate source keys and confirm the first matching row is the intended one.
  7. If formulas were copied, verify that the source range stayed fixed and the lookup reference moved as intended.

For a quick visual check, select the lookup cell and the apparent matching source cell and inspect their values in the formula bar. Number formatting can make different underlying values look alike, so a displayed value alone is not conclusive. Avoid changing an entire column’s format as a first fix: formatting changes how values appear, but does not always convert the stored values into the same type.

When to use XLOOKUP instead

VLOOKUP remains useful for existing workbooks and straightforward tables, but it requires the lookup column to be leftmost and uses a numeric column position. XLOOKUP can search in one range and return a corresponding value from another, including from a column to the left. It also uses exact matching by default and has an optional not-found result. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not supported in older perpetual versions such as Excel 2019.

For new formulas in a supported version, XLOOKUP can make the intended lookup and return ranges clearer. With codes in A2:A6, descriptions in B2:B6, and the search code in D2, enter this in E2; if D2 matches A4, it returns B4, and if there is no match it returns Not found:

=XLOOKUP(D2,A2:A6,B2:B6,"Not found")

Switching functions is not always necessary. If a workbook must remain compatible with older Excel versions, or already contains many VLOOKUP formulas, correcting the existing formula may be the safer and simpler choice. For repeated records where every match is needed, a single-result lookup function is not the right tool; Microsoft 365 and Excel 2021 include dynamic-array functions such as FILTER that can return multiple matching rows.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
The lookup value may differ from the source entry because of spaces, hidden characters, punctuation, or a text-versus-number mismatch. Also confirm that the matching value is in the first column of the selected range and that the formula uses the intended range.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s text matching is not case-sensitive, so entries such as abc and ABC are treated as equivalent for this lookup. If capitalization must distinguish records, use a case-sensitive approach rather than relying on VLOOKUP alone.

Why does VLOOKUP return the wrong duplicate?
It returns the first matching row it finds from the top of the lookup column. Check whether the key appears more than once and decide which record should be used. A formula that returns one match cannot resolve ambiguous duplicate data by itself.

Why does my formula work in one row but fail when copied?
The source range may be shifting as the formula is filled down, or the lookup reference may not be moving as intended. Lock the table range with dollar signs, then verify that each copied formula points to the correct lookup cell.

Should the last VLOOKUP argument be TRUE or FALSE?
Use FALSE for an exact match, which is appropriate for most IDs, product codes, and names. Use TRUE only when approximate matching is intended and the first column is sorted in ascending order. Leaving the argument out defaults to approximate matching.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.