Fix #VALUE! When Referencing Another Excel Table Column in WPS Office
Question details
The user needs to resolve #VALUE! errors caused by Excel structured references such as Table1[@Link] that depend on worksheet row position, and retrieve corresponding table records reliably.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is writing a formula in one Excel table that references a column in another table and expects the corresponding record, not the same worksheet row.
- Observed behavior
- The formula returns #VALUE! or returns the value from the same worksheet row instead of the corresponding relative table row, especially after sorting or filtering.
Open the workbook in WPS Office Spreadsheets and verify that both ranges are formatted as Excel tables with visible header rows. Check whether your current formula uses the @ structured reference and note the starting row of each table.
Use INDEX to retrieve the corresponding table row instead of the same worksheet row
Best when both tables are laid out in parallel and you need a value based on relative row position from the table header.
The @ symbol in =Table1[@Link] means implicit intersection, so it returns the value from Table1[Link] at the same current worksheet row. If the second table starts in a different row, this produces the wrong record or a #VALUE! error.
The INDEX formula below calculates the relative row inside Table2 and retrieves the corresponding row from Table1.
Click the first cell in the column where you want the corresponding value.
Type =LET(v,INDEX(Table1[Desc],ROW()-ROW(Table2[#Headers])),IF(v="","",v)) in the formula bar, replacing Table1[Desc] and Table2[#Headers] with your actual table and column names.
Press Enter, then use the fill handle to copy the formula down the table column.
Use XLOOKUP with a lookup key for reliable records after sorting or filtering
Use this when your records have a unique key and the table order may change, because positional formulas can break after sorting or filtering.
Rebuild hyperlinks with HYPERLINK when the referenced value is a URL
Use this when the source column contains hyperlinks and you need a clickable link rather than a plain URL or an error.
Fix #VALUE! Table Reference Errors in WPS Office Spreadsheets
Correct the #VALUE! error directly in WPS Office Spreadsheets by replacing same-row @ structured references with INDEX or XLOOKUP. WPS Office supports Excel tables, .xlsx workbooks, and the functions used in the fix, so the workbook remains fully compatible.
- 1. Open the workbook: Start WPS Office Spreadsheets and open the .xlsx file that contains the #VALUE! error.
- 2. Locate the faulty formula: Click the cell showing #VALUE! and review the formula in the formula bar.
- 3. Replace the structured reference: Enter the INDEX or XLOOKUP formula described in the solution above using your actual table and column names.
- 4. Confirm and save: Press Enter, fill the formula down if needed, and save the workbook to keep the corrected version.
Frequently Asked Questions
Why does =Table1[@Link] not return the corresponding record from the other table?
Because the @ symbol uses implicit intersection and returns the value from the same worksheet row. If the other table starts in a different row, the rows do not align.
How do I reference a column in another table by relative table row?
Use INDEX with ROW()-ROW(Table2[#Headers]), for example =LET(v,INDEX(Table1[Desc],ROW()-ROW(Table2[#Headers])),IF(v="","",v)).
What is the safest formula when my table can be sorted or filtered?
Use a lookup key with XLOOKUP, such as =XLOOKUP([@Key],Table1[Key],Table1[Link],"").
Can WPS Office work with Excel tables, structured references, and XLOOKUP?
Yes. WPS Office Spreadsheets supports .xlsx Excel tables, structured references, INDEX, LET, and XLOOKUP functions.
