IFERROR VLOOKUP Not Working? Find and Fix the Error

If IFERROR with VLOOKUP is not working, first check that the formula is structured correctly and that VLOOKUP is searching the intended column for the right kind of match. IFERROR only replaces an error with a fallback value; it cannot correct a wrong lookup range, mismatched data, or an incorrect column number.

What IFERROR and VLOOKUP are meant to do

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. IFERROR surrounds that calculation: if it returns an error, IFERROR displays a value you choose instead. This is useful for replacing a visible #N/A with a message such as Not found, but it can also hide errors that would be useful to diagnose.

For example, with product IDs in A2:A100, prices in B2:B100, and the ID to search for in E2, enter the result in F2:

=IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found")

If E2 contains P-104 and that ID is present in column A with a price of 18.50 in column B, the formula returns 18.50. If the ID is absent, it returns Not found. The dollar signs keep the lookup range fixed if you copy the formula down; E2 remains relative, so it changes to E3, E4, and so on.

In Excel, IFERROR handles any error value produced by the expression inside it, including #N/A, #REF!, #VALUE!, and #NAME?. That broad coverage is convenient when the fallback is appropriate, but it means a formula with a structural mistake may appear to work while quietly showing the fallback.

Check the formula structure first

The structure is IFERROR(value, value_if_error). For a VLOOKUP, the first argument is the complete lookup formula, and the second argument is what Excel should display if that lookup produces an error. The VLOOKUP arguments are the lookup value, the table range, the result-column number, and the match setting.

With product IDs in A2:A100, prices in B2:B100, and the searched ID in E2, a typical exact-match formula is:

=IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found")

E2 is the value to find.
$A$2:$B$100 is the table. Its first column, A, must contain the IDs.
2 tells VLOOKUP to return the second column of that table, column B.
FALSE asks for an exact match, which is usually appropriate for IDs, names, and codes.
“Not found” is displayed if the enclosed calculation returns an error.

Check commas, parentheses, quotation marks, and the cell references. The final parenthesis closes IFERROR, while the one before the comma closes VLOOKUP. In installations configured to use a different list separator, Excel may require semicolons instead of commas; use the separator accepted by your regional settings. The formulas here use standard comma separators.

Identify the error before hiding it

A useful first diagnostic is to temporarily remove IFERROR and inspect the result. With product IDs in A2:A100, prices in B2:B100, and the lookup ID in E2, test the VLOOKUP by itself:

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

If this returns #N/A, Excel did not find an exact match. If it returns #REF!, the requested result column is outside the selected table range. #VALUE! can point to an invalid column number, and #NAME? can indicate a misspelled function or text that Excel does not recognize. Fix the underlying cause before putting IFERROR back around the lookup.

Also check whether Excel is showing the formula as text instead of calculating it. If the cell displays the literal formula, it may be formatted as Text, or the formula may have been entered with a leading apostrophe. Change the cell format to General, then edit and confirm the formula again. If many cells show formulas rather than results, check whether Show Formulas is enabled on the Formulas tab.

IFERROR does not stop Excel from calculating the formula, and it does not repair broken references. It only changes the displayed result when the enclosed expression evaluates to an error. While troubleshooting, use the unwrapped formula or the formula bar to make the actual problem visible.

Confirm exact match and lookup direction

For most lookup lists, set VLOOKUP’s fourth argument to FALSE. If the argument is left out, VLOOKUP uses approximate matching. Approximate match can return an unexpected row when the first column is not sorted as required; it is intended for cases such as grading bands or rate thresholds, not ordinary product-code matching.

VLOOKUP also has a fixed direction: it searches only the first column of the selected table and returns a value to its right. If the lookup value is in column C but the result you need is in column A, changing IFERROR will not solve the problem. Rearrange the data, choose a range whose first column contains the lookup value, or use a lookup method that can return values from either side.

Check the column index against the selected range, not the worksheet’s column letters. If the table is D2:F100, D is column 1 of that table, E is 2, and F is 3. A column index of 4 would be outside this three-column range and can cause #REF!. In the product example, the range A:B has two columns, so 2 correctly returns the price from B.

Look for mismatched values, spaces, and data types

An exact-match #N/A often means the values look alike but are not identical to Excel. One cell may contain the number 104 while another contains text “104.” Leading or trailing spaces can also prevent a match. This commonly happens when data is copied from a website, exported from another system, or assembled from multiple files.

Compare the lookup value with the source value in the formula bar, and try checking lengths or cell formats. Formatting a text number as Number does not always convert the stored value into a number; conversion may require re-entering or transforming the data. Make sure both sides use a consistent type before relying on the lookup.

For ordinary spaces around a value, a helper column can clean a source entry. With the original text in A2, enter this in B2 and fill down:

=TRIM(A2)

TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to single spaces. It does not remove every invisible character: nonbreaking spaces, often brought in from web pages, may need a separate cleanup step. Also clean the lookup value using the same approach where appropriate. Do not remove spaces blindly from names or codes if spaces are meaningful parts of the data.

Understand what IFERROR may be hiding

Because IFERROR catches several different error types, a fallback such as Not found can conceal a broken formula. For instance, if someone changes a lookup range but leaves an invalid column index, the formula could display Not found even though the ID exists. That message suggests a missing record, so it can send the reader in the wrong direction.

If the intention is only to replace a missing-match error, IFNA is more specific. With product IDs in A2:A100, prices in B2:B100, and the lookup ID in E2, use:

=IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found")

IFNA replaces #N/A but leaves errors such as #REF! visible, making structural problems easier to notice. Choose IFERROR when any error should receive the same fallback; choose IFNA when the fallback is specifically for a value that was not found. Neither function fixes the lookup itself, and both can obscure useful information if used without checking the source formula.

Work through a practical troubleshooting sequence

Use this order to narrow down the cause without changing several parts of the formula at once:

Remove IFERROR temporarily. Note the actual error returned by VLOOKUP.
Verify the lookup value. Confirm the cell reference points to the value you intend to search for.
Check the first table column. The lookup value must be searched for in the leftmost column of the selected range.
Use an exact match for identifiers. Confirm the last VLOOKUP argument is FALSE.
Check the result-column number. Count columns from the left edge of the selected range, and make sure the number is within that range.
Compare the stored values. Check for spaces, invisible characters, and text-versus-number differences.
Restore a suitable fallback. Use IFERROR for any error or IFNA when only #N/A should be replaced.

After the formula works in one row, copy it to another row and confirm that the lookup reference changes while the table range remains fixed. If the table grows, adjust the range or use an Excel Table so new records are included more reliably. A lookup can be correct today and still return Not found later if new data is added outside a fixed range.

Consider alternatives when VLOOKUP is the limitation

If you have Microsoft 365 or Excel 2021 and later, XLOOKUP can search one range and return a result from another, including a column to the left. Its optional not-found argument can remove the need for a separate IFERROR wrapper when the only error you want to handle is a missing match.

With product IDs in A2:A100, prices in B2:B100, and the ID to search for in E2, XLOOKUP can return the price or the message Not found:

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

XLOOKUP is not available in some older Excel versions, so a workbook shared with older installations may need VLOOKUP or another compatible approach. Before replacing a working formula, consider who will open the workbook and which Excel versions they use. In Excel for the web, formulas such as VLOOKUP, IFERROR, and XLOOKUP work where supported by the account; the desktop and web interfaces differ, but the basic formula logic is the same.

Frequently Asked Questions

Why does IFERROR VLOOKUP still show Not found when the value exists?
IFERROR may be hiding an error other than a true missing match. Remove IFERROR to inspect the result, then check whether the lookup value is in the first column, whether the match is exact, whether the data types match, and whether spaces or invisible characters are present.

Why does my IFERROR VLOOKUP return a blank?
The fallback may be an empty string, written as two quotation marks with nothing between them, or the VLOOKUP may successfully return a blank cell. Test the VLOOKUP without IFERROR and inspect the returned source cell to distinguish those cases.

Should I use FALSE or TRUE in VLOOKUP?
Use FALSE for an exact match, especially for identifiers, names, and product codes. TRUE requests approximate matching and is appropriate only when you intend that behavior and the first column is sorted correctly for the lookup.

Can IFERROR fix #N/A in VLOOKUP?
It can replace the displayed #N/A with a message or another value, but it does not make VLOOKUP find a missing or mismatched item. Check the lookup range, exact-match setting, and source data to correct the cause.

Why does VLOOKUP return the wrong column?
The column number counts from the first column of the table range, not from column A on the worksheet. Recount the columns inside the selected range and verify that the range begins with the lookup column.

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.