Keep Excel Columns Synchronized When External Data Changes
Question details
Keep related Excel columns matched to the correct records when data linked from an external workbook changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Columns A:D contain data linked to an external workbook, while columns E onward contain related values.
- Observed behavior
- Rows added or changed in the source can leave the values in later columns aligned with the wrong records.
Identify a unique, stable ID for each record in the source and in the related data. If the columns are already misaligned, verify the original relationships before creating the ID-based table; row position alone may not be enough to recover them.
Match Related Values to Source Records by Unique ID
Use a lookup formula when each source record has an ID that also appears in a separate table of related values.
Store related values with their IDs outside the imported range. A lookup can then retrieve the correct value even when the source records change order.
On a sheet named Related, put each unique ID in column A and its related value in column B. Add further related fields in later columns as needed.
Confirm that column A of the sheet containing the external data holds the matching ID for each record, starting in A2.
In E2, enter =XLOOKUP(A2,Related!$A$2:$A$1000,Related!$B$2:$B$1000,""). Adjust the ranges to cover your related-data table.
Fill the formula down column E and create similar formulas for other related fields. If the output is an Excel Table, check after each refresh that its calculated columns have extended to any new rows.

Merge Source and Related Data with Power Query
Use Power Query when you want to import and refresh a combined table as the source data changes.
Match Related Columns by ID in WPS Spreadsheets
Use an INDEX and MATCH formula in WPS Spreadsheets to retrieve related values by a stable ID instead of depending on row position.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select the workbook containing the source records.
- 2. Organize related values: On a sheet named Related, place unique IDs in column A and their corresponding values in column B.
- 3. Match values by ID: On the source sheet, enter =IFERROR(INDEX(Related!$B$2:$B$1000,MATCH(A2,Related!$A$2:$A$1000,0)),"") in E2, assuming A2 contains the source ID.
- 4. Fill and check the formula: Fill E2 down for the remaining records, then check that new rows have formulas and that blank results are not caused by missing or mismatched IDs.

Frequently Asked Questions
Why do my columns become misaligned after external data changes?
Imported rows may be added, removed, or reordered while manually entered values beside them remain in their existing cells. Store those values with a unique ID and retrieve them by that ID.
What can I use if my Excel version does not have XLOOKUP?
Use INDEX and MATCH instead. For example, enter =IFERROR(INDEX(Related!$B$2:$B$1000,MATCH(A2,Related!$A$2:$A$1000,0)),"") in E2 to match the ID in A2.
Will converting the range to an Excel Table preserve the relationships?
A table can help extend calculated-column formulas, but it does not preserve a relationship based only on row position. Use a unique ID in the formulas and verify that formulas extend after a refresh.
What happens if an ID appears more than once in the related table?
A lookup may return the first matching value, while a Power Query merge can produce multiple rows. Make the related-table ID unique, or use a combination of fields that uniquely identifies each record.
How do I update a Power Query merge after the source changes?
In Excel, choose Data > Refresh All. Check the refreshed results for unmatched IDs and confirm that the source files are accessible.




