Excel VLOOKUP IFERROR: Return a Blank for Lookup Errors

Formulas & functions · Updated November 19, 2026

To make an Excel VLOOKUP display a blank when it returns an error, wrap it in IFERROR: =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). With the lookup value in E2 and the lookup table in A2:C100, this returns the matching value from column C or an empty string if the lookup formula produces an error.

What the formula does

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. In the example, it looks for E2 in column A and returns the corresponding entry from column C. The number 3 means “return the third column of the selected range,” while FALSE requests an exact match.

IFERROR checks the result of the expression inside it. If VLOOKUP returns a value, IFERROR passes that value through unchanged. If the expression returns an Excel error, IFERROR returns the second argument instead. Here, two double quotation marks with nothing between them represent an empty text string, so the cell looks blank.

This is useful in reports and worksheets where a visible #N/A makes a table harder to scan. It does not repair the lookup or make a missing value exist; it changes what the formula displays when an error occurs. That distinction matters when other formulas depend on the result.

Understand the formula arguments

Assuming the lookup value is in E2, the lookup table is in A2:C100, and column C contains the values to return, the formula is:

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

The two functions have separate jobs:

  • VLOOKUP(E2,$A$2:$C$100,3,FALSE) searches the leftmost column of the range for E2 and returns a value from its third column.
  • IFERROR(value,value_if_error) returns the VLOOKUP result if it is not an error, or an empty string if it is.
  • FALSE specifies an exact match. It is generally the appropriate choice for identifiers such as product codes, employee IDs, or names.
  • $A$2:$C$100 uses absolute references, so the table range stays fixed if you copy the formula down a column.

The column number is counted from the left edge of the selected range, not from the worksheet. If the range were B2:D100, for example, its third column would be worksheet column D. If you insert or remove columns inside a lookup range, review the column number because it may no longer point to the intended return field.

Enter and copy the formula

In Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula, and press Enter. Replace E2, A2:C100, and the column number with references that match your worksheet. Then use the fill handle to copy the formula to other rows. The dollar signs keep the table range fixed while the relative reference E2 changes to E3, E4, and so on.

  1. Confirm that the lookup value is in the cell referenced by the first VLOOKUP argument.
  2. Make sure the first column of the selected table range contains the values Excel should search.
  3. Set the column index to the position of the return column within that range.
  4. Use FALSE for an exact match unless you specifically intend an approximate match and have prepared the data for it.
  5. Wrap the VLOOKUP in IFERROR and use "" as the value to display for errors.

The same formula structure is available in Excel for Mac and Excel for the web. The ribbon and interface can vary by platform, but the formula itself does not need a different version. If your regional Excel settings use semicolons rather than commas as formula separators, Excel may require semicolons when you enter the formula; the examples here use comma separators.

IFERROR or IFNA: which should you use?

IFERROR catches any error produced by the expression it checks, including #N/A, #REF!, #VALUE!, and #DIV/0!. In a straightforward VLOOKUP, #N/A commonly means no exact match was found. But an error can also signal a damaged reference, an invalid column index, or another mistake. IFERROR hides all of those by returning the same blank-looking result.

If your specific aim is to hide only the “not found” result while allowing other errors to remain visible, use IFNA instead. Assuming E2 contains the lookup value, A2:A100 contains the keys, and C2:C100 contains the return values, enter:

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

IFNA returns the empty string when VLOOKUP returns #N/A, but it does not conceal errors such as #REF!. That can make a worksheet easier to troubleshoot. Use IFERROR when every error from that expression should be hidden; use IFNA when you want to distinguish a missing match from a different formula problem.

A blank-looking result is not a truly empty cell

The "" in these formulas is an empty text string, not an actually empty cell. The cell contains a formula, and the formula returns text with zero characters. It appears blank on the worksheet, but some Excel functions treat it differently from a cell that has never contained anything.

For example, assuming the formula is in F2, ISBLANK(F2) returns FALSE because F2 contains a formula. COUNTA can also count a formula cell that returns an empty string as nonempty. By contrast, SUM ignores text in ordinary arithmetic, so an empty-string result generally does not add to a numeric total. If a later formula needs to distinguish “no match” from a genuine blank cell, test the result deliberately rather than relying on how it looks.

There is another related case: a matching row may exist, but its return cell may be empty. VLOOKUP can return 0 for a blank cell in the return column, and IFERROR will not replace that zero because zero is not an error. If you want a blank display both when there is no match and when the matched return cell is empty, add a check for an empty result. Assuming E2 is the lookup value and the table and return column are as above, use:

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

This version performs the VLOOKUP twice when it succeeds. For a simple worksheet that may be acceptable, but a longer or more complex formula can be easier to maintain with LET. LET is available in Microsoft 365 and Excel 2021 or later. With the same cell layout, this version stores the lookup result once:

=IFERROR(LET(result,VLOOKUP(E2,$A$2:$C$100,3,FALSE),IF(result="","",result)),"")

Choose this extra blank-cell handling only if it matches your data requirements. A zero can be a meaningful return value, so suppressing it just because it looks inconvenient could hide valid information.

Common causes of unexpected blanks or errors

Because IFERROR hides problems as well as missing matches, a blank result is not proof that the lookup worked as intended. If a result is unexpectedly blank, temporarily remove IFERROR or inspect the underlying VLOOKUP to see the error. Check these common causes:

  • The key is absent: With an exact match, VLOOKUP returns #N/A if it cannot find the lookup value in the first column.
  • The columns are arranged the wrong way: VLOOKUP only searches the first column of its table range. It cannot search a column to the right and return a value from a column to its left.
  • The column index is too large: The index must be between 1 and the number of columns in the selected range. An invalid index can return #REF!, which IFERROR would hide.
  • Numbers and text do not match: A number stored as text may not match a numeric lookup value as expected. Check the source data types and whether imported values contain leading apostrophes or other formatting differences.
  • Extra spaces or inconsistent characters: Leading or trailing spaces, nonbreaking spaces, or slightly different spelling can prevent a text key from matching. Cleaning the source values may be better than masking the resulting error.
  • The formula uses approximate match: If the final VLOOKUP argument is omitted or TRUE, Excel can perform an approximate match. That behavior is different from an exact lookup and can return an unintended result if the first column is not sorted appropriately.

Also inspect the range before copying the formula. Absolute references keep it fixed as intended, but they do not make an incorrectly selected range correct. A blank caused by an absent key may be normal; a blank caused by a broken reference may indicate that the workbook needs repair.

Alternatives to VLOOKUP

VLOOKUP remains useful for familiar, compact tables, but its left-to-right layout requirement can be limiting. In Microsoft 365 and Excel 2021 or later, XLOOKUP can search one range and return from a separate range. Its fourth argument lets you specify what to return when no match is found. Assuming E2 contains the key, A2:A100 contains the keys, and C2:C100 contains return values, use:

=XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100,"")

This returns an empty string when no match is found, without wrapping the formula in IFERROR. XLOOKUP uses exact match by default and can return values from either side of the lookup column. It does not automatically hide every other error, so its “not found” argument is not a general substitute for IFERROR.

For a workbook that must support older Excel versions, VLOOKUP with IFERROR may be the more compatible option. For a new formula in a supported version, XLOOKUP can be clearer, especially when the return column is left of the key column or when you want a dedicated not-found result. Consider the Excel versions used by everyone who will open the workbook before choosing.

Frequently Asked Questions

How do I make VLOOKUP return blank instead of #N/A?
Assuming the lookup value is in E2 and the table is A2:C100, with the return value in column C, use =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). It displays an empty string if VLOOKUP returns an error.

Does IFERROR hide errors other than #N/A?
Yes. IFERROR replaces any error from its first argument, not just a missing-match error. Use IFNA instead if you want to hide #N/A while keeping errors such as #REF! visible.

Why does VLOOKUP show 0 instead of a blank?
The lookup may have found a matching row whose return cell is empty. That result is not an error, so IFERROR does not change it. Add an explicit empty-result check if you need blank return cells to display as blank.

Does a formula returning “” count as a blank cell?
It looks blank but is not truly empty: the cell still contains a formula. Functions such as ISBLANK can distinguish it from a cell with no contents, and COUNTA may count it.

Should I use IFERROR or IFNA with VLOOKUP?
Use IFERROR if you intentionally want to suppress every error from the lookup expression. Use IFNA if only a missing match should look blank and other formula errors should remain visible for diagnosis.

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.