Keep Excel Structured References Relative Between Tables in WPS Spreadsheets
Question details
The user needs to understand why structured references such as TableName[@Column] may return errors or incorrect values when source and destination tables are separated by blank rows, and how to make cross-table references stable.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with multiple Excel tables in a workbook and wants to reference a column from another table when the tables are not aligned or have blank rows between them.
- Observed behavior
- A structured reference resolves against the current row context in the table where the formula is entered, not against the same row position in another table, so the reference can fail or return the wrong value.
Open the workbook in WPS Spreadsheets and check that each table has a unique identifier column, such as ID. If your tables use blank rows as separators, do not rely on row position; create a unique key before writing lookup formulas.
Use XLOOKUP with a Unique ID to Match Rows Between Tables
Use this when the source and destination tables are separated by blank rows or have different row order; it returns values by unique key instead of row position.
A structured reference such as TableName[@Column] always resolves against the current row context in the table where the formula is entered. It does not mean the same row number in another table, so blank rows or inserted rows can make positional references unreliable. Matching on a unique ID column keeps the link stable.
In WPS Spreadsheets, click the first cell in the destination table column where you want the returned value to appear.
Type =XLOOKUP([@ID], baseTableName[ID], baseTableName[baseTableColTitle], "") in the formula bar, replacing baseTableName and baseTableColTitle with your actual table and column names.
Press Enter, then use the table fill handle or drag the fill handle down so the table column applies the lookup to all rows.
Use INDEX and MATCH as a Compatibility Alternative
Use this if your WPS version or workbook does not have XLOOKUP, or if you need a formula that also works in older Excel versions.
Reference Excel Tables by ID in WPS Spreadsheets
WPS Spreadsheets supports Excel structured references, XLOOKUP, and INDEX/MATCH, so you can create stable cross-table references without depending on row position. Build the formula in the familiar Excel-compatible interface and save the workbook as .xlsx.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets, click Menu > Open, and select the Excel workbook containing the two tables.
- 2. Add or confirm an ID column: Ensure each table has a unique ID column. If not, click the top of a new column in the table, type ID, and fill it with unique values.
- 3. Enter the XLOOKUP formula: In the destination table, click the cell for the returned value, type =XLOOKUP([@ID], baseTableName[ID], baseTableName[baseTableColTitle], ""), and press Enter.
- 4. Copy the formula to the table rows: Use the table fill handle or drag the fill handle down so each row looks up its own ID value.
- 5. Save as Excel file: Click Menu > Save As and choose Excel Workbook (*.xlsx) to preserve the tables and formulas.
Frequently Asked Questions
Why does =TableName[@Column] return the wrong value when I reference another table?
Because the @ symbol means "this row in the current table context", not "the same row number in the other table". If the tables are not aligned or are separated by blank rows, use a unique ID and a formula like XLOOKUP or INDEX/MATCH.
What is the safest way to keep structured references relative between tables?
Add a unique ID column to both tables and use =XLOOKUP([@ID], SourceTable[ID], SourceTable[Column], ""). This matches by key and is not affected by row position or blank rows.
Can I use INDEX instead of XLOOKUP in WPS Spreadsheets?
Yes. Use =INDEX(SourceTable[Column], MATCH([@ID], SourceTable[ID], 0)). INDEX/MATCH is useful when you need compatibility with older Excel versions.
Do blank rows between tables break structured references?
Blank rows do not hide or disable structured references, but they make it clear that row position is not a safe way to match tables. A unique key lookup is the reliable solution.
