Function Problems

How to Use Stable Lookups Instead of Cell References in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 11, 2026 868 views

Question details

The user wants to prevent data from shifting or formulas pulling incorrect values when a source data table is filtered.

How to Use Stable Lookups Instead of Cell References After Filtering
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify your stable key

Determine the unique identifier in your destination sheet, for example, the site name located in cell A2.

2
Select the destination cell

Click on the cell where you want the related data (like Total Units) to be displayed.

3
Input the INDEX and MATCH formula

Type the formula: =INDEX(Invoiced!$B$2:$B$100,MATCH($A2,Invoiced!$A$2:$A$100,0))

4
Apply to the rest of the column

Press Enter to execute the formula, then drag the fill handle down to apply this logic to the remaining rows in your table.

Use INDEX and MATCH for Stable Key-Based Lookups
Formula Breakdown: MATCH finds the exact row of your unique key ($A2) in the source column, and INDEX uses that row number to retrieve the correct value from the target column ($B$2:$B$100), bypassing any active filters.
Advanced Lookups Made Easy

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your filtered source table.
  2. 2. Select the destination cell: Click on the cell where you want to perform the stable lookup.
  3. 3. Insert the lookup function: Navigate to the Formulas tab, select Lookup and Reference, and choose XLOOKUP or MATCH.
  4. 4. Define your arrays: Set your lookup value, lookup array, and return array using the intuitive formula dialog box, then click OK.
100% compatible with Microsoft Excel formulas and .xlsx filesNative support for advanced functions like XLOOKUP, INDEX, and MATCHBuilt-in data validation and advanced filtering toolsLightweight, fast, and completely free to use
QA img-9

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.