'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Prevent VLOOKUP from Displaying Zero in Excel & WPS
Question details
The user needs to prevent the VLOOKUP formula from returning a 0 when the matched record has a blank field, aiming to display a completely blank cell for both missing matches and blank results.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data using VLOOKUP from a source table that contains empty fields, which inadvertently populates the destination report with unwanted zero values.
- Observed behavior
- The VLOOKUP formula returns 0 instead of a blank cell when it encounters an empty field in the lookup array.
Verify that your lookup source range is correct and ensure that the 'blank' cells in your source data do not contain hidden spaces or actual zero values formatted as white text.
Use Nested IF and ISNA Functions (Recommended)
Combine VLOOKUP with IF and ISNA functions to test for both non-existent matches (#N/A errors) and blank source fields, returning a blank text string for both scenarios.
This is the most robust method because it changes the underlying data returned by the formula, rather than just hiding the zero visually. It handles missing lookups, blank results, and zero values all at once.
Click on the cell where you want to output the lookup result.
Type the formula =IF(ISNA(VLOOKUP(C3,data,4,0)),"",IF(VLOOKUP(C3,data,4,0)=0,"",VLOOKUP(C3,data,4,0))) into the formula bar.
Replace 'C3' with your lookup value cell, 'data' with your specific lookup range, and '4' with the column index number you are retrieving data from.
Press Enter to confirm the formula, then click and drag the fill handle at the bottom-right of the cell to apply it to the remaining rows.
Hide Zeros Using Custom Number Format
If you want the cell to retain the mathematical zero value but display as completely blank on your screen and prints, you can apply a custom cell format.
Use WPS Spreadsheet to Handle Complex Formulas
WPS Spreadsheet provides robust support for advanced data functions like VLOOKUP, IF, and ISNA. It allows you to clean up messy data, handle errors elegantly, and format reports exactly how you want without missing a beat.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load the workbook containing your VLOOKUP tables.
- 2. Use the Formula Builder: Click the 'Formulas' tab on the ribbon and select 'Insert Function'. This tool guides you step-by-step through nesting IF and VLOOKUP functions effortlessly.
- 3. Format Cells to Hide Zeros: Quickly hide unwanted zeros visually by selecting your cells, pressing Ctrl+1, and applying the custom format 0;-0;;@.
Frequently Asked Questions
Why does VLOOKUP return 0 for a blank cell?
By default, spreadsheet programs interpret an empty cell in a referenced lookup range as a mathematical zero. When VLOOKUP fetches this blank cell, it outputs 0 rather than a blank space.
Can I use IFERROR instead of ISNA?
Yes, you can use =IFERROR(IF(VLOOKUP(C3,data,4,0)=0,"",VLOOKUP(C3,data,4,0)),""). IFERROR is shorter and catches all error types (like #VALUE! or #REF!), whereas ISNA strictly targets missing match (#N/A) errors.
How do I return a dash instead of a blank cell?
In your IF formula, simply replace the empty quotation marks ("") with a dash enclosed in quotes ("-"). For example: IF(VLOOKUP(C3,data,4,0)=0,"-",VLOOKUP(C3,data,4,0)).
Will appending an empty string (&") fix the zero issue?
Yes, adding &"" to the end of your formula (e.g., =VLOOKUP(C3,data,4,0)&"") forces the result into a text string. This turns a zero from a blank cell into a true blank, but it also converts all valid numeric lookup results into text, which disables further mathematical calculations on them.
