How to Use XLOOKUP in WPS Spreadsheets to Return Zero for Missing Linked Excel Data
Question details
The user needs a formula to look up an employee in a linked Excel table and return the corresponding count, displaying zero when no matching value exists.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with XLOOKUP across a linked Excel table that contains employee names and counts and needs missing matches to display 0 instead of an error.
- Observed behavior
- XLOOKUP returns an error or no value when the employee is not found in the linked table, so zero is needed instead.
Confirm that the linked workbook or table is accessible and that the column names used in the formula exactly match the source table headers. Also make sure your WPS Spreadsheets version supports XLOOKUP.
Use IFERROR with XLOOKUP to Return Zero for Missing Values
Use this solution when you want XLOOKUP to return the matching count for an employee and show 0 whenever the employee does not exist in the linked table.
The formula uses XLOOKUP to search for the employee name in the linked table's Name column and returns the corresponding Count column value. Wrapping XLOOKUP in IFERROR changes any lookup error to 0, so missing employees display 0 instead of #N/A.
Click the cell in your WPS Spreadsheets worksheet where the looked-up count should appear, such as B8 next to an employee name in A8.
Type =IFERROR(XLOOKUP(A8,Tabelle2[Name],Tabelle2[Count]),0) and replace Tabelle2[Name] and Tabelle2[Count] with your linked table and column names.
Press Enter to apply the formula. When no match exists, WPS Spreadsheets returns 0 instead of an error.
Return Zero for Missing Linked Excel Data with XLOOKUP in WPS Spreadsheets
WPS Spreadsheets can directly complete this task using the same XLOOKUP and IFERROR formula approach. Open your Excel workbook, enter the formula in the target cell, and WPS will return the matching count or 0 when the linked value is missing.
- 1. Open your Excel workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the workbook containing the linked table or external reference.
- 2. Check the linked table and columns: Go to the worksheet and confirm that the linked table name and column headers match the formula references.
- 3. Enter the formula in the target cell: In the result cell, type =IFERROR(XLOOKUP(A8,Tabelle2[Name],Tabelle2[Count]),0), replacing table and column names as required.
- 4. Press Enter and save the workbook: Press Enter to return the count or 0, then save the file in WPS Spreadsheets or Excel format.
Frequently Asked Questions
Why does my XLOOKUP return #N/A when the employee is missing?
XLOOKUP returns #N/A when it cannot find a match. Wrap the formula in IFERROR(...,0), such as =IFERROR(XLOOKUP(A8,Tabelle2[Name],Tabelle2[Count]),0), to display 0 instead.
Can I use this formula with a table in another workbook?
Yes. Include the workbook path and sheet or table reference before the column name, and keep the linked file accessible so WPS Spreadsheets can read the data.
Does WPS Spreadsheets support XLOOKUP?
Current versions of WPS Spreadsheets support XLOOKUP. If the function is unavailable, update WPS Office to the latest version.
Should I use IFERROR or IFNA with XLOOKUP?
IFERROR catches all formula errors, while IFNA only catches #N/A. If you only want missing matches to return 0 and other errors to remain visible, use IFNA instead.
