How to Return 0 When VLOOKUP Finds No Match in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula that looks up a location number and returns 0 instead of an error when the value is missing.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a location number lookup table and wants missing values to display 0 rather than #N/A.
- Observed behavior
- A standard exact-match VLOOKUP returns #N/A when no matching location number exists.
Open the workbook in WPS Spreadsheets and confirm the lookup column is the leftmost column in your VLOOKUP range. Also check that location numbers use a consistent format, because text and numeric mismatches can cause lookups to fail.
Use IFERROR or IFNA with exact-match VLOOKUP
Best for Excel 2016 and newer, including WPS Spreadsheets, when you need an exact match and want missing values to display 0 instead of #N/A.
The FALSE argument forces exact matching. IFERROR catches any error, while IFNA only catches #N/A; using IFNA is safer if you still want other errors to appear.
Click the cell where you want the looked-up value to appear, then click the formula bar.
Type =IFERROR(VLOOKUP(location_number,lookup_range,result_column,FALSE),0) and replace location_number with the cell containing the location number, lookup_range with your data range, and result_column with the column number containing the value to return.
Press Enter. If the location number is missing, the cell now displays 0; if it exists, the corresponding value appears.
Use XLOOKUP as a modern alternative
Use this in Excel 365 or WPS Spreadsheets versions that support XLOOKUP when you want a shorter formula without column-index counting.
Return 0 for Missing VLOOKUP Matches in WPS Office Spreadsheets
WPS Office Spreadsheets supports VLOOKUP, IFERROR/IFNA, and XLOOKUP, so you can build the same formula and get 0 when no location number is found.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the location numbers and lookup table.
- 2. Click the result cell: Select the cell where you want to display the lookup result or 0.
- 3. Enter the formula: Type =IFERROR(VLOOKUP(location_number,lookup_range,result_column,FALSE),0) or =XLOOKUP(location_number,lookup_locations,lookup_results,0) and adjust the cell references to your data.
- 4. Press Enter and verify: Press Enter to apply the formula. Missing values now return 0, and matching values return the correct result.
Frequently Asked Questions
Why does my VLOOKUP return #N/A when no match is found?
VLOOKUP returns #N/A when the lookup value is not present. Wrap it in IFERROR or IFNA and set the value_if_error argument to 0 to display 0 instead.
Can I use XLOOKUP instead of VLOOKUP to return 0?
Yes. In Excel 365 or WPS Spreadsheets versions that support XLOOKUP, use =XLOOKUP(lookup_value,lookup_array,return_array,0).
Does VLOOKUP only search the first column?
Yes. VLOOKUP searches the leftmost column of the specified range, so ensure the location numbers are in the first column of lookup_range.
Should I use IFERROR or IFNA?
Use IFNA if you only want to replace #N/A errors, so other errors remain visible. Use IFERROR if you want all errors to return 0.
Will these formulas work in WPS Spreadsheets?
WPS Spreadsheets supports VLOOKUP, IFERROR/IFNA, and XLOOKUP in current versions, so the formulas work in WPS Office and remain compatible with Excel files.
