Fix Excel IFERROR VLOOKUP Returning Zero or Jan-00 in WPS Spreadsheets
Question details
The user needs to stop an IFERROR(VLOOKUP(...)) formula from displaying zero or Jan-00 when the looked-up cell is empty, and return a blank instead.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is using IFERROR with VLOOKUP to retrieve a value from a table and expects a blank when the lookup result is empty.
- Observed behavior
- The formula displays 0 or January 1900 because VLOOKUP returns zero for an empty cell and the cell formatting interprets the zero as a date.
Open the workbook in WPS Spreadsheets and identify the lookup table (Table3) and the return column index (15). Check whether the returned cells in column 15 are empty, contain numeric zeros, or use a date format that turns zeros into Jan-00.
Return Blank When the Source Cell Is Empty Using ISBLANK and INDEX/MATCH
Use this if you need a robust formula that checks the actual source cell in column 15 and returns an empty string only when that cell is blank.
VLOOKUP returns 0 for an empty cell, and IFERROR does not catch that because no error occurs. Testing the source cell directly with ISBLANK gives you an explicit blank result without changing the table.
Click the cell that currently contains the IFERROR(VLOOKUP(...)) formula in WPS Spreadsheets.
Paste =IF(ISBLANK(INDEX(Table3,MATCH(B10,INDEX(Table3,,1),0),15)),"",VLOOKUP(B10,Table3,15,FALSE)) into the formula bar, keeping the same B10 references for your workbook.
Press Enter, then change the source cell in column 15 between a value and blank to confirm the formula now displays a blank instead of zero or Jan-00.
Use a Simple Zero-Check with IFERROR as a Shorter Alternative
Use this when your return column never uses 0 as a valid result and you want a minimal change to the existing IFERROR formula.
Fix VLOOKUP Blank Results in WPS Spreadsheets
WPS Spreadsheets supports the same IFERROR, VLOOKUP, INDEX, MATCH, and ISBLANK functions as Excel, so you can apply this blank-result fix directly in a familiar spreadsheet interface.
- 1. Open your workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel workbook that contains the VLOOKUP formula.
- 2. Select the formula cell: Click the cell with the IFERROR(VLOOKUP(...)) formula so it appears in the formula bar.
- 3. Apply the ISBLANK or zero-check formula: Paste one of the recommended formulas from this article into the formula bar and press Enter.
- 4. Verify formatting: Check the cell’s number format if Jan-00 appears; set it to General or Number if you need to see the underlying zero while testing.
- 5. Save and share: Save the file as .xlsx to keep full compatibility with Microsoft Excel users.
Frequently Asked Questions
Why does IFERROR(VLOOKUP(...)) show January 1900 instead of blank?
VLOOKUP returns 0 when the matched source cell is empty. IFERROR does not trigger because 0 is not an error, and if the result cell is formatted as a date, Excel or WPS Spreadsheets interprets the serial number 0 as January 0, 1900, which often displays as Jan-00.
How can I return a blank instead of zero in VLOOKUP?
Use a formula that tests the returned value or source cell. For example, wrap VLOOKUP in IF(VLOOKUP(...)=0,"",VLOOKUP(...)) or use ISBLANK with INDEX/MATCH to test the exact source cell before returning the lookup result.
Will changing the cell format fix Jan-00?
Changing the format from Date to General or Number makes the underlying zero visible instead of Jan-00, but it does not create a blank. Use a formula fix if you need a true empty result.
Can I use these formulas in Microsoft Excel as well?
Yes. IFERROR, VLOOKUP, INDEX, MATCH, and ISBLANK behave the same way in Microsoft Excel and WPS Spreadsheets, so the same formula can be used when you open the workbook in either application.
What should I check if the formula still returns 0?
Check that the lookup value in B10 actually matches a row in Table3, verify that the return column index is 15, and confirm the matched cell is not actually storing the number 0 instead of being empty.
