How to Use Stable Lookups Instead of Cell References in Excel
Question details
The user wants to prevent data from shifting or formulas pulling incorrect values when a source data table is filtered.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Linking data across sheets where the source dataset is frequently filtered, causing standard direct cell references to fetch the wrong row's data.
- Observed behavior
- Direct cell references return values based strictly on row numbers, which change unexpectedly when the underlying source table is filtered or sorted.
Ensure that both your source table and the destination sheet share a unique, stable key (such as a site name, employee ID, or product code) to match the records accurately.
Use INDEX and MATCH for Stable Key-Based Lookups
Replace volatile direct cell references with dynamic lookups that search for a specific identifier, making the formula immune to filtering.
Direct cell references like '=Invoiced!B2' point to a specific row coordinate. When you filter data, the row coordinates shift, but the formula does not adapt. By combining INDEX and MATCH, you instruct the spreadsheet to look for a specific value (e.g., a site name) and return the data adjacent to it, regardless of where it currently sits in the filtered list.
Determine the unique identifier in your destination sheet, for example, the site name located in cell A2.
Click on the cell where you want the related data (like Total Units) to be displayed.
Type the formula: =INDEX(Invoiced!$B$2:$B$100,MATCH($A2,Invoiced!$A$2:$A$100,0))
Press Enter to execute the formula, then drag the fill handle down to apply this logic to the remaining rows in your table.

Use XLOOKUP for Simplified Lookups
A modern and straightforward alternative to INDEX/MATCH available in newer versions of spreadsheet software.
Perform Advanced Lookups Seamlessly with WPS Spreadsheet
WPS Office provides a powerful, free spreadsheet application that fully supports advanced dynamic array formulas like XLOOKUP and INDEX/MATCH. Manage complex, filtered datasets effortlessly without worrying about broken references.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your filtered source table.
- 2. Select the destination cell: Click on the cell where you want to perform the stable lookup.
- 3. Insert the lookup function: Navigate to the Formulas tab, select Lookup and Reference, and choose XLOOKUP or MATCH.
- 4. Define your arrays: Set your lookup value, lookup array, and return array using the intuitive formula dialog box, then click OK.

Frequently Asked Questions
Why do direct cell references pull wrong data when I filter a table?
Direct references point to a specific cell coordinate (like B2). When you filter or sort a table, the original data in B2 might move to a different row. The formula still blindly pulls whatever is currently sitting in B2, leading to incorrect results.
Does VLOOKUP work as a stable lookup after filtering?
Yes, VLOOKUP also relies on matching a unique key rather than a cell coordinate, making it stable against filtering. However, INDEX/MATCH and XLOOKUP are generally preferred because they do not require the lookup column to be the first column on the left.
Will XLOOKUP work if I send my file to someone using an older spreadsheet version?
XLOOKUP is a newer function and is not natively supported in older software versions like Excel 2016 or 2019. If you are sharing the file with users on legacy platforms, it is safer to use the INDEX and MATCH combination, which is universally supported.




