Formula Errors

Excel Formula to Replace VLOOKUP #N/A Errors With Blank Cells

Steve KSteve K Nov 12, 2026 868 views

Question details

The user needs an Excel formula that displays a blank cell instead of a #N/A error when VLOOKUP cannot find a matching value.

Replace Excel VLOOKUP #N/A Errors With Blank Cells
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VLOOKUP searches for the value in cell H5 on the Video Overview worksheet and should return a related value when a match exists.
Observed behavior
VLOOKUP returns #N/A for missing matches, and comparisons against zero or #N/A do not suppress the error.
Before you start

Confirm that the lookup value is in H5 and that the lookup range and return-column number are correct. IFERROR will hide all errors produced by the wrapped formula, not only #N/A errors.

Solution 1Recommended

Wrap the VLOOKUP formula with IFERROR

Use this method when a missing lookup result should appear as an empty cell instead of #N/A.

IFERROR returns the original VLOOKUP result when a match is found. If the lookup produces an error, it returns an empty text string instead.

1
Select the formula cell

Click the cell that contains the VLOOKUP formula, or select the first cell where you want the result to appear.

2
Enter the IFERROR VLOOKUP formula

In the formula bar, enter =IFERROR(VLOOKUP(H5,'Video Overview'!B:D,2,FALSE),"") and press Enter.

3
Fill the formula down if needed

Drag the fill handle from the selected formula cell down the column to apply the same lookup logic to additional rows.

Wrap the VLOOKUP formula with IFERROR
Expected result: Matching values continue to display normally, while values that are not found display as blank cells.
Work with lookup formulas in WPS Spreadsheets

Use IFERROR and VLOOKUP to Hide Missing Matches in WPS Spreadsheets

WPS Spreadsheets supports familiar Excel lookup formulas, including VLOOKUP, IFERROR, and IFNA. You can open an Excel workbook, update the formula to return blank cells for missing matches, and save the completed file in its original format.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the VLOOKUP formula.
  2. 2. Select the lookup result cell: Click the cell that currently shows #N/A or contains the VLOOKUP formula.
  3. 3. Add IFERROR around VLOOKUP: In the formula bar, enter =IFERROR(VLOOKUP(H5,'Video Overview'!B:D,2,FALSE),"") and press Enter.
  4. 4. Apply the formula to other rows: Drag the fill handle in the lower-right corner of the selected cell down to copy the formula.
  5. 5. Save in Excel format: Select Menu > Save As, choose Excel Workbook (*.xlsx), and save the updated workbook.
Use IFERROR with VLOOKUP to replace missing-match errors with blank cells.Supports Microsoft Excel .xlsx and .xls workbook formats.Familiar spreadsheet formula bar and fill handle make formula updates easy.Open, edit, and save compatible Excel files without changing your workflow.
microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return #N/A in Excel?

VLOOKUP returns #N/A when it cannot find the lookup value in the first column of the selected table range. Check the lookup value, table range, and whether the values have extra spaces or different data types.

What formula shows a blank cell when VLOOKUP cannot find a match?

Use =IFERROR(VLOOKUP(H5,'Video Overview'!B:D,2,FALSE),""). The empty quotation marks tell Excel to display a blank result when the lookup errors.

Should I use IFERROR or IFNA with VLOOKUP?

Use IFNA when you only want to replace missing-match #N/A errors. Use IFERROR when you also want to hide other errors returned by the VLOOKUP formula.

Can VLOOKUP return a blank from the source table?

Yes. If VLOOKUP finds a matching row and the return cell is empty, the result may display as 0 depending on the source data and formula context. Use a separate IF test if you need to treat returned zero values differently.