Function Problems

How to Return 0 Values and Blanks with Excel VLOOKUP

Steve KSteve K Nov 11, 2026 869 views

Question details

The user needs VLOOKUP to return actual 0 values (even if custom-formatted as text) but still return a blank cell when the source cell is completely empty or the lookup fails.

How to Return 0 Values and Blanks with Excel VLOOKUP
Product
Excel
Device & OS
not provided
Scenario
Retrieving data where 0 is a valid result, but ensuring that truly empty source cells are returned as blanks instead of default zeros.
Observed behavior
Standard VLOOKUP translates empty source cells into 0s, making it difficult to distinguish between a true 0 and a blank cell. Additionally, standard VLOOKUP fails to hide #N/A errors when a match isn't found.
Before you start

Ensure that the zero values in your source data are actual numbers (0), and verify your lookup range and column index number before building the nested formula.

Solution 1Recommended

Use IFERROR and IF with VLOOKUP

This formula tests for an empty string before returning the VLOOKUP result, distinguishing between a true zero and a blank cell.

By default, Excel's VLOOKUP returns a 0 when it encounters a blank cell. By nesting VLOOKUP inside an IF function, you can explicitly check if the return cell is blank ("") and output a blank accordingly. Wrapping the entire formula in IFERROR handles cases where the lookup value isn't found at all.

1
Select the destination cell

Click on the cell where you want the VLOOKUP result to appear.

2
Enter the nested formula

Type the formula: =IFERROR(IF(VLOOKUP(G1528,$G$5:$Q$1494,11,0)="","",VLOOKUP(G1528,$G$5:$Q$1494,11,0)),"") into the formula bar. Replace the cell references with your actual lookup value and table array.

3
Apply to remaining cells

Press Enter to execute the formula. Double-click or drag the fill handle in the bottom-right corner of the cell to copy the formula down your column.

Format Preservation: If your 0 values are custom-formatted to display as "no" in the source data, you must apply that same Custom Number Format to your destination cells for them to display correctly.
Free Spreadsheet Software

Master VLOOKUP Formulas Easily with WPS Office

WPS Spreadsheet offers full support for advanced formulas like VLOOKUP, IFERROR, IF, and LET. It provides a highly compatible and user-friendly interface to process complex data lookups seamlessly without formatting issues.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
  2. 2. Select the result cell: Click on the target cell where you need the formatted lookup result.
  3. 3. Enter the nested formula: Type =IFERROR(IF(VLOOKUP(...)="","",VLOOKUP(...)),"") into the formula bar and let the smart formula syntax prompt guide your inputs.
  4. 4. Format destination cells: Right-click the result cell, select 'Format Cells', and apply your custom format so that true 0 values display exactly as they do in your source data.
Fully compatible with Microsoft Excel formulas, functions, and custom number formats.Supports advanced data lookup, logical testing, and error handling seamlessly.Lightweight software with a familiar, tabbed interface for easy navigation.Free to use for everyday data analysis and spreadsheet formatting tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does standard VLOOKUP return 0 for blank cells?

By default, when VLOOKUP finds a match but the return cell is completely empty, Excel evaluates that empty cell as a 0 rather than a blank. Using an IF statement to check if the result is exactly equal to a blank string ("") forces the formula to return a true blank instead.

Will custom number formats affect my VLOOKUP results?

VLOOKUP retrieves the underlying mathematical or text value of a cell, not its formatted appearance. If a cell contains a 0 that is custom-formatted to display as 'no', VLOOKUP will still return the underlying 0. You must apply the same custom number format to the destination cell to make it display as 'no'.

What is the IFERROR function doing in this formula?

IFERROR catches standard calculation errors (such as the #N/A error that occurs when the lookup value is not found anywhere in the table array). Instead of displaying an ugly error code, IFERROR replaces it with a specified value—in this case, an empty string (""), which keeps your spreadsheet looking clean.