Formula Errors

How to Return Zero Instead of #N/A for Blank VLOOKUP Cells

Maira Mehtab Sep 21, 2026 870 views

Question details

The user needs a VLOOKUP formula to output a 0 rather than an #N/A error when the referenced lookup cell is blank.

Product: Spreadsheets (Excel / WPS Spreadsheet)
Device & OS: not provided
Scenario: Importing data from Microsoft Forms via Power Automate into a spreadsheet where some fields may be submitted as blank.
Observed behavior: The formula =VLOOKUP(AW2,Quals,2,FALSE) evaluates to the #N/A error when cell AW2 contains no data, interrupting calculations that expect a numerical value.

Before you start

Verify your current VLOOKUP formula syntax and confirm whether you want blank lookup inputs to always default to exactly 0, or if you prefer to leave the destination cell completely empty.

Solution 1Recommended

Use the IFERROR Function (Recommended)

Wrap your existing VLOOKUP formula within the IFERROR function to seamlessly catch the #N/A error and specify an alternative output value.

The IFERROR function is the most efficient way to handle spreadsheet formula errors. It evaluates your primary formula first, and if it results in any error (like #N/A), it outputs a custom value you define instead.

Select the target cell

Click on the cell in your spreadsheet that currently displays the #N/A error.

Edit the formula bar

Click into the formula bar at the top of the spreadsheet to edit your existing formula.

Wrap with IFERROR

Type IFERROR( immediately after the equals sign, and add ,0) to the very end of your formula. For example, change =VLOOKUP(AW2,Quals,2,FALSE) to =IFERROR(VLOOKUP(AW2,Quals,2,FALSE),0).

Apply and drag

Press Enter to save the formula. You can then click and drag the fill handle at the bottom right of the cell to copy this updated logic to the rest of the column.

Alternative Output: If you want the cell to appear completely empty instead of showing a '0', use double quotation marks at the end of the formula: =IFERROR(VLOOKUP(AW2,Quals,2,FALSE),"").

Solution 2

Use the IF and ISBLANK Functions

Check if the target lookup cell is empty before running the VLOOKUP formula to prevent the error from triggering in the first place.

Advanced Data Processing with WPS Spreadsheet

Master VLOOKUP and Handle Errors Easily in WPS Spreadsheet

WPS Spreadsheet fully supports standard formulas like VLOOKUP, IFERROR, and ISBLANK. Process imported data effortlessly with a robust, high-performance spreadsheet tool that is entirely compatible with your existing files.

1. Open your data file: Launch WPS Spreadsheet and open the .xlsx file containing your Power Automate data.
2. Enter the IFERROR formula: Select your desired cell and type =IFERROR(VLOOKUP(AW2,Quals,2,FALSE),0) into the formula bar.
3. Fill the column: Double-click the fill handle in the bottom-right corner of the cell to automatically apply the error-handling formula down your entire dataset.

100% compatible with Microsoft Excel (.xlsx) formats and standard formulasFree built-in data processing, pivot tables, and analysis toolsLightweight architecture opens large automated datasets instantlyFamiliar user interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return #N/A when the lookup cell is blank?

The #N/A error stands for 'Not Available'. When your VLOOKUP formula is instructed to search for a blank value, and it cannot find a corresponding blank cell in the first column of your lookup range, it returns #N/A to indicate that no exact match was found.

Is there a way to handle only #N/A errors without hiding other formula errors?

Yes. The IFERROR function catches all types of errors (such as #DIV/0! or #REF!). If you specifically want to target just the #N/A error, use the IFNA function instead: =IFNA(VLOOKUP(AW2,Quals,2,FALSE), 0). This ensures you are still alerted to other potential math or reference issues.

Will this error-handling formula work if I share my file with Excel users?

Absolutely. Functions like IFERROR, IFNA, and VLOOKUP are standard spreadsheet functions. Formulas written in WPS Spreadsheet are seamlessly cross-compatible and will work perfectly when opened in Microsoft Excel.

Excel & Data Problems