How to Return a Blank Cell in WPS Office Excel When VLOOKUP Returns Zero
Question details
The user needs a formula that returns an empty cell when the final VLOOKUP result is zero, instead of displaying 0 or the fallback cell value.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user is working with a lookup table in WPS Office Excel and wants a blank cell instead of zero when VLOOKUP finds no value or the fallback result is zero.
- Observed behavior
- The current formula returns zero when VLOOKUP cannot find a value and IFERROR falls back to a specified cell.
Confirm that your WPS Office Excel version supports the LET function and verify that the lookup range BH:BP and fallback cell $AI2 match your workbook.
Use LET and IF to return an empty string when VLOOKUP returns zero
Recommended when you want the formula result to appear blank while keeping the lookup logic in one cell.
LET stores the lookup result, IFERROR keeps the fallback, and IF tests for zero. This returns an empty string as the formula result, so the cell appears blank while still containing a formula.
Click the cell that currently contains the VLOOKUP formula, such as the first row in your table column.
Enter =LET(result,IFERROR(VLOOKUP([@[Color]],BH:BP,3,FALSE),$AI2),IF(result=0,"",result)) and press Enter.
Drag the fill handle down or copy the formula to apply the same pattern to the remaining table rows.
Use number formatting to hide zero values without changing the formula
Optional when you want to keep the formula result but hide zeros across the sheet.
Return a Blank Cell with WPS Office Excel When VLOOKUP Returns Zero
Use the LET, IFERROR, and IF formula in WPS Office Excel to make VLOOKUP return an empty string instead of 0. WPS Office Excel supports the same Excel functions, so you can fix the formula without switching tools.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office and open the Excel file containing the VLOOKUP formula.
- 2. Select the formula cell: Click the cell where you want a blank returned instead of zero.
- 3. Enter the LET formula: Type =LET(result,IFERROR(VLOOKUP([@[Color]],BH:BP,3,FALSE),$AI2),IF(result=0,"",result)) and press Enter.
- 4. Apply to remaining rows: Drag the fill handle down or copy the formula to the other rows in the column.
Frequently Asked Questions
Why does my VLOOKUP return 0 instead of blank when no match is found?
VLOOKUP itself does not return blank, and IFERROR is falling back to a specified cell that contains 0. Use a LET and IF formula to test the result and return an empty string when the final value is zero.
Can I hide zero values without changing the VLOOKUP formula?
Yes. Select the range, press Ctrl+1, choose Custom under Number, enter 0;-0;;@, and click OK. The cell will look blank, but its underlying value remains 0.
Is the LET function available in WPS Office Excel?
Current versions of WPS Office Excel support LET, IFERROR, and VLOOKUP. If LET is not recognized, update WPS Office to the latest version.
How do I apply the fix to all rows in an Excel table?
Enter the formula in the first row of the table column. In an Excel table, the formula usually fills down automatically; otherwise, drag the fill handle down through the remaining rows.
