Function Problems

Keep Excel Resident Account References Correct After Sorting

Khadija KhanKhadija Khan Nov 18, 2026 870 views

Question details

Keep resident account worksheets displaying the correct personal data when the master list is sorted.

Keep Excel Resident Account References Correct After Sorting
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a master table

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.

2
Enter the account ID

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.

3
Look up the resident's name

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.

4
Sort and verify the records

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.

Use resident IDs and XLOOKUP to retrieve master data
Keep IDs unique: XLOOKUP returns the first match when IDs are duplicated. Sort the entire table, not an individual column, so each resident's fields stay together.
Work with resident records in WPS

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. 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. 2. Enter the account ID: On an account sheet, enter the resident's unique ID in cell B2.
  3. 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. 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.
Match resident account data by a stable ID after sortingOpen and save Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and sorting controlsWork with spreadsheets using a free office suite
microsoft office alternative - wps office

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.