How to Return 0 Values and Blanks with Excel VLOOKUP
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.

- 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.
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.
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.
Click on the cell where you want the VLOOKUP result to appear.
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.
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.
Use the LET Function for Cleaner Syntax
If you are using Excel 365 or Excel 2021, the LET function calculates the VLOOKUP once, making the formula shorter and more efficient.
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. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Select the result cell: Click on the target cell where you need the formatted lookup result.
- 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. 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.

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.




