WPS Spreadsheet: Keep Related Data Matched When Filtering Two Excel Tables
Question details
The user needs to keep related records aligned when filtering two independently filtered Excel tables.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Two tables contain related information, and the user wants filtering one or both tables without losing the relationship between corresponding records.
- Observed behavior
- Rows can become misaligned when formulas or links depend on matching row positions instead of a stable relationship key.
Review both tables and identify a column that uniquely identifies each record, such as an order ID, customer ID, or product code. Also decide whether filtering should affect only one table or whether both tables should show the same filtered record set.
Use a shared unique ID with XLOOKUP to retrieve related values
Use this method when the two tables must remain independent but their related values need to stay matched after sorting or filtering.
Do not link related rows by position, such as A2 to A2, because filtering or sorting can change which record appears on each row. Instead, use a unique identifier to find the correct related record every time.
If your WPS Spreadsheet version does not support XLOOKUP, use VLOOKUP or INDEX and MATCH with the same unique ID.
In each table, open the worksheet and add or confirm a column containing the same unique ID for every related record, such as Order ID or Customer ID. Make sure the IDs are spelled and formatted consistently.
Select the first data range, open the Insert tab, choose Table, and confirm that My table has headers is enabled. Repeat for the related data range so the headers and records are clearly defined.
In the destination table, click the first cell where the related value should appear and choose the formula bar or press =. Enter a formula such as =XLOOKUP([@[Order ID]],Table2[Order ID],Table2[Status],"Not found") and press Enter.
When the formula is entered in a table column, WPS Spreadsheet can fill the calculated column automatically. If it does not, drag the fill handle down through the remaining records.
Click the filter arrow in a table header and select the required values. The lookup formula continues to search by ID, so sorting or filtering does not replace a related record with the value from another row.
Combine related fields into one table before filtering
Use this approach when both sets of information should always be filtered, sorted, and displayed as one synchronized record list.
Keep linked spreadsheet records synchronized with ID-based lookups
WPS Spreadsheet lets you organize related data in tables and use XLOOKUP or compatible lookup formulas to return values by a stable unique ID. This keeps relationships intact when users sort or filter the tables instead of relying on row positions.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the two related tables.
- 2. Identify the matching key: Inspect both tables and select a column with a unique, consistently formatted ID for every related record.
- 3. Create the lookup column: Click the destination table cell and enter an XLOOKUP formula through the formula bar, using the shared ID column as the lookup value and the related table column as the return range.
- 4. Test the relationship: Use the table header filter arrows to hide or show records, then verify that each returned value still belongs to the same ID.
Frequently Asked Questions
Why do two linked Excel tables become mismatched after filtering?
They become mismatched when formulas refer to matching row numbers, such as A2 linked to A2. Filtering or sorting changes the visible row positions, so use a stable unique ID with XLOOKUP, VLOOKUP, or INDEX and MATCH instead.
How do I use XLOOKUP to match records between two WPS Spreadsheet tables?
Click the result cell and enter a formula such as =XLOOKUP([@[Order ID]],Table2[Order ID],Table2[Status],"Not found"). Replace the table and column names with those in your workbook, then fill the formula down the destination table.
What should I do if my version of WPS Spreadsheet does not support XLOOKUP?
Use VLOOKUP or INDEX and MATCH with the same unique identifier. For example, VLOOKUP can search the ID in the first column of a related range and return a value from another column.
Can filtering one table automatically filter a separate related table?
Independent table filters do not automatically synchronize just because the tables contain related values. Use one combined table for shared filtering, or use a lookup-based design and apply the appropriate filter to each table.
