Keep Excel Resident Account References Correct After Sorting
Question details
Keep resident account worksheets displaying the correct personal data when the master list is sorted.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A master worksheet contains resident records, and separate account worksheets retrieve each resident's personal data.
- Observed behavior
- Account worksheets should continue to display data for the same resident after the master list is sorted.
Make sure each resident has a unique, stable ID and that each account sheet has the corresponding ID available in a cell. Check that the master list has a header row and that its records can be sorted together.
Use resident IDs and XLOOKUP to retrieve master data
Use this approach to match each account sheet to a resident rather than to a fixed row position.
Select the master list, including its headers, and choose Insert > Table. Confirm that the table has headers, then use Table Design > Table Name to name it Master.
On each account worksheet, enter that resident's unique ID in cell B2. Make sure it matches the value in the master table's Resident ID column.
In the cell where the name should appear, enter =IF($B$2="","",XLOOKUP($B$2,Master[Resident ID],Master[Name],"")) and press Enter. Replace Name with the relevant table column header to retrieve another field.
On the master sheet, use the filter arrow in the name column to sort the table alphabetically. Check an account sheet to confirm that its ID still returns the correct resident's data.

Look up resident records in WPS Spreadsheets
Use an exact-match VLOOKUP in WPS Spreadsheets to retrieve resident data by ID instead of relying on a fixed row. Sort the complete master range to keep each record together.
- 1. Open the workbook: Open the resident workbook in WPS Spreadsheets. On the master sheet, arrange Resident ID in column A and Name in column B.
- 2. Enter the account ID: On an account sheet, enter the resident's unique ID in cell B2.
- 3. Add the lookup formula: In the name cell, enter =IF($B$2="","",IFERROR(VLOOKUP($B$2,Master!$A$2:$B$1000,2,FALSE),"")), replacing Master with the actual master sheet name. Expand the range if records extend beyond row 1000.
- 4. Sort and check: Select the full master data range and use Data > Sort to sort by name. Check that the account sheet still displays the name associated with its ID.

Frequently Asked Questions
Why use a resident ID instead of a row reference?
A resident ID identifies the record even when sorting changes its row position. A fixed reference such as Master!B2 points to a cell, which may contain a different resident's data after sorting.
What happens if two residents have the same ID?
XLOOKUP returns the first matching record, so duplicate IDs can show the wrong person's data. Assign each resident a unique ID in the master table and update the corresponding account sheet.
Why does the lookup return a blank?
The example formula returns a blank when B2 is empty or no match is found. Check that the account ID matches the master ID, including its text or number format, and confirm the Master table and column names.
Can I retrieve fields other than the resident's name?
Yes. Copy the XLOOKUP formula into another cell and replace Master[Name] with the desired column, such as Master[Phone], using the exact header from the master table.




