Fix XLOOKUP Returning Blanks with Table Structured References in WPS Spreadsheets
Question details
The user needs XLOOKUP to return matching values from an Excel-style table, but blank results appear when structured reference arguments include extra @ symbols.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up a value in a table and returning the corresponding job position using XLOOKUP with structured references.
- Observed behavior
- XLOOKUP returns blanks instead of exact matches when @ symbols are included in the lookup and return array arguments.
Before editing the formula, confirm the table name and column headers in the formula match the structured references in the worksheet, and verify that the lookup column contains the values you expect to match.
Remove Extra @ Symbols from XLOOKUP Lookup and Return Arrays
Use this fix when XLOOKUP returns blank cells because structured references have @ symbols on the full table columns instead of only on the current-row lookup value.
In a structured reference, [@OrderID] refers to the current row value. The lookup_array and return_array arguments must point to complete table columns, so they should not include the @ operator. Only the lookup_value uses [@OrderID].
Open the workbook in WPS Spreadsheets and select the cell containing the XLOOKUP formula that returns blanks.
Click the formula bar and locate the lookup_array and return_array arguments in the XLOOKUP formula.
Delete the @ symbol from the lookup and return array references. For example, change Detail_Data[@OrderID] and Detail_Data[@Job Position] to Detail_Data[OrderID] and Detail_Data[Job Position].
Ensure the first argument remains [@OrderID], so the formula reads =XLOOKUP([@OrderID],Detail_Data[OrderID],Detail_Data[Job Position],"",0).
Press Enter to recalculate and confirm that XLOOKUP now returns the expected job positions instead of blanks.
Fix XLOOKUP Blank Results by Correcting Structured References in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, edit the XLOOKUP formula, and remove the extra @ symbols from the lookup and return arrays so the function searches the complete table columns and returns exact matches.
- 1. Open the workbook in WPS Spreadsheets: Download and launch WPS Office, then open the Excel workbook that contains the XLOOKUP formula.
- 2. Locate the formula cell: Click the cell that returns blanks and view its formula in the formula bar.
- 3. Correct the structured references: Remove the @ symbol from the lookup_array and return_array column references, keeping [@OrderID] only as the lookup_value.
- 4. Check the result: Press Enter to recalculate and confirm XLOOKUP returns the matching values from the return column.
Frequently Asked Questions
Why does XLOOKUP return blank when I click table columns in Excel?
When you create an XLOOKUP formula by clicking table cells, the structured reference may add @ to every clicked column. Open the formula bar and remove @ from the lookup_array and return_array arguments, leaving it only on [@OrderID].
Do I need to remove @ from all XLOOKUP arguments?
No. Keep @ only in the current-row lookup value, such as [@OrderID]. The lookup_array and return_array should be complete table columns like Detail_Data[OrderID] and Detail_Data[Job Position].
Can I use this fix in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets, click the formula cell, and edit the formula in the formula bar exactly as you would in Excel; the structured reference behavior is compatible.
What should I do if XLOOKUP still returns blanks after removing @?
Check that the lookup values exist in the lookup column without leading or trailing spaces, and verify the table name and column headers match the structured references exactly.
