IFERROR VLOOKUP Not Working? Common Causes and Fixes

IFERROR with VLOOKUP usually seems not to work because the formula is malformed, the problem is not an Excel error, or VLOOKUP returns a wrong result instead of an error. For an exact-match lookup, assuming the ID to find is in E2, IDs are in A2:A100, and results are in C2:C100, use =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). If E2 contains an ID whose value in column C is 875, the formula returns 875; if no exact ID is found, it returns Not found.

What IFERROR does—and what it does not do

IFERROR evaluates an expression and displays a replacement value only if that expression produces an Excel error. In a lookup formula, VLOOKUP does the search; IFERROR handles an error result from that search. It does not repair the lookup range, make an inexact match exact, or check whether a returned value is logically correct.

VLOOKUP can return errors such as #N/A when it cannot find a match, or #REF! when its column number points outside the selected range. IFERROR can replace those errors with a message, a blank, or another value. It also catches other formula errors, including #VALUE! and #NAME?. That broad coverage is useful, but it can conceal a separate problem in the formula.

IFERROR cannot catch an error-looking value that is actually text. For instance, a cell containing the characters #N/A as imported text is not the same as the #N/A error value generated by a failed lookup. Nor can IFERROR catch a wrong answer: if VLOOKUP finds a near match or returns the wrong column, the result may be a valid value, not an error.

Check the formula structure first

The usual formula pattern is IFERROR(value, value_if_error). For VLOOKUP, the lookup value comes first, followed by the table range, the number of the return column within that range, and the match setting. The fallback is a separate second IFERROR argument. Check that the parentheses enclose the whole VLOOKUP and that the comma between IFERROR arguments comes after VLOOKUP’s closing parenthesis.

With the same layout—lookup ID in E2, lookup table in A2:C100, and the desired result in the third column—the exact-match formula is =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). The 3 means the third column of A:C, which is column C; it does not mean worksheet column C in every possible table. FALSE requests an exact match. If E2 is 104 and the row for 104 has 875 in column C, this returns 875.

When Excel displays an error while you enter the formula, check for a missing parenthesis, a misplaced comma, or quotation marks around the fallback text. In Excel for Microsoft 365 on Windows, formulas normally use commas as argument separators, as shown here. Some regional settings use semicolons instead; if Excel rejects a formula with commas, check the separator used by another working formula in that installation. Do not mix separators within one formula.

Find out which error VLOOKUP is producing

A fallback such as Not found can hide useful information. Temporarily remove IFERROR and inspect the VLOOKUP result, or evaluate the formula in pieces. You can also use Excel’s Formulas > Evaluate Formula command to follow how a formula is calculated. On Windows, select the formula cell and choose Formulas > Evaluate Formula. This helps distinguish a failed search from a bad column number or another error.

#N/A: VLOOKUP did not find the lookup value under the selected match rules. Check the value, data type, spaces, and exact-match setting.
#REF!: The column index is larger than the number of columns in the table range, or a referenced range was removed.
#VALUE!: An argument may be invalid, such as a column index that is not a usable number.
#NAME?: Excel may not recognize a function name or text in the formula may be missing quotation marks.

If you want to replace only the common not-found error and let other errors remain visible, use IFNA instead. Assuming the same E2 lookup value and A2:C100 table, =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found") returns Not found for #N/A, but does not suppress an error such as #REF!. This can make formula faults easier to spot.

Fix mismatched lookup values

Exact-match VLOOKUP can fail even when two cells look identical. One value might be a number while the other is text; imported IDs often cause this. Leading or trailing spaces, nonbreaking spaces from web data, and inconsistent punctuation can also prevent a match. Formatting a text value to look like a number does not necessarily convert the stored value into a number.

First inspect representative cells in the formula bar and compare the source and lookup values. Try copying a known value directly from the lookup table into the lookup cell as a diagnostic. If that works, the issue is probably in how the original values were imported or constructed. Use a consistent cleanup or conversion process on both sides rather than changing only one value without checking the data.

For a simple space-cleaning test, with the lookup value in E2 and a source value in A2, enter =TRIM(E2)=TRIM(A2) in an unused cell. TRUE indicates the trimmed values compare equal, although this test does not remove every invisible character or convert text numbers to numeric values. TRIM removes ordinary extra spaces; data copied from websites may contain other whitespace that needs a different cleanup step.

Be cautious about converting identifiers to numbers. Product codes, postal codes, and account IDs may contain leading zeroes that are significant. If 00124 and 124 are distinct codes in the source system, treating them as the same number would create a data-quality problem, not a useful lookup fix.

Confirm the table range and return column

VLOOKUP searches only the first column of its table range. If the lookup key is in column B but the selected range begins in column A, VLOOKUP will search A, not B. Rearrange the range so the key is its leftmost column, or use another lookup approach when changing the layout is unsuitable.

The return-column number is counted from the left edge of the selected range. For a range B2:D100, B is 1, C is 2, and D is 3. A number greater than the range width produces #REF!. Also check whether the range moved when the formula was copied. Dollar signs in $A$2:$C$100 keep the range fixed when filling the formula down, while E2 remains relative and changes to E3, E4, and so on.

For example, with IDs in A2:A100, returned prices in C2:C100, and the lookup ID in E2, this formula has a fixed table range and a return index of 3: =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). If the table later grows beyond row 100, the formula will not include new rows unless you expand the range or use an Excel Table reference.

Use exact match unless approximate match is intended

VLOOKUP’s fourth argument controls matching. FALSE requests an exact match and is usually the right choice for names, IDs, and product codes. If you omit that argument, VLOOKUP uses approximate matching. Approximate matching is intended for ordered ranges, such as tax bands or commission thresholds, and generally requires the first column to be sorted in ascending order. Used on an unsorted list of IDs, it can return a plausible but incorrect value without producing an error.

With the same E2 and A2:C100 layout, a formula that explicitly requests exact match is =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). Do not rely on IFERROR to protect an approximate lookup: if the approximate search returns a value, IFERROR considers the calculation successful, even if the value is not the one you wanted.

Check calculation, display, and fallback behavior

If the cell displays the formula itself instead of a result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change the cell format to General, then re-enter the formula; changing the format alone may not make an already-entered formula recalculate. On Windows, Ctrl+` toggles Show Formulas. On Mac, the equivalent shortcut is Control+`; keyboard layouts can affect how the grave-accent key is entered.

Also check Formulas > Calculation Options. If calculation is set to Manual, changes to the lookup data may not update results until you recalculate. Choose Automatic if that is appropriate for the workbook. On Windows, F9 recalculates formulas; on Mac, use Fn+F9 where the keyboard requires the Fn key.

A blank fallback can make a sheet look tidy, but it can also make a failed lookup indistinguishable from a genuinely empty result. Assuming the same table and lookup cell, =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"") returns an empty string when the lookup errors. Consider using a visible message during troubleshooting, then switching to a blank only when missing matches are expected and monitored.

When to choose another lookup function

VLOOKUP cannot return a value from a column to the left of its lookup column, and its numeric column index can become fragile if columns are inserted into the table. In Microsoft 365, XLOOKUP is an alternative that can search in either direction and has a built-in not-found argument. XLOOKUP is available in Excel for Microsoft 365 and Excel 2021 or later, but not in some older Excel releases.

For the same example, with IDs in A2:A100, results in C2:C100, and the lookup value in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). It returns the matching value from column C or Not found when there is no match. XLOOKUP uses exact matching by default, but IFERROR may still be useful if you need to handle errors other than a missing match. For compatibility with older Excel versions, a correctly structured VLOOKUP with FALSE remains a practical choice.

Frequently Asked Questions

Why does IFERROR still show #N/A?
Check that IFERROR wraps the entire VLOOKUP, rather than only one of its arguments, and that the formula was accepted as a formula. If the visible #N/A is text stored in a cell instead of an error value, IFERROR will not replace it. Temporarily inspect the formula without IFERROR to identify the actual result.

Why does IFERROR VLOOKUP return the wrong answer?
IFERROR does not validate a successful result. A common cause is approximate matching because the fourth VLOOKUP argument is missing or set to TRUE. Use FALSE for an exact lookup, confirm the table’s first column contains the lookup key, and verify the return-column index.

Why does VLOOKUP say a value is missing when it is visible?
The lookup value and source value may differ in stored type, contain extra or invisible spaces, or use different characters. Compare the underlying values, not just their appearance, and test a copied source value. Keep leading zeroes when they are meaningful identifiers.

Can IFERROR return a blank when VLOOKUP finds nothing?
Yes. Use an empty text string as the fallback, written as two quotation marks with nothing between them. However, a blank can hide missing data, so a clear message is often safer while building or auditing a workbook.

Should I use IFERROR or IFNA with VLOOKUP?
Use IFERROR when you intentionally want to replace any error produced by the expression. Use IFNA when the only error you want to handle is a missing match. IFNA lets other problems, such as an invalid return-column index, remain visible for diagnosis.

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.