Formula Errors

How to Stop XLOOKUP Ranges from Changing When Copying in Excel

Huma Ashraf ChHuma Ashraf Ch Oct 29, 2026 873 views

Question details

The user wants to prevent XLOOKUP formula ranges from shifting or changing when copying the formula to other cells.

How to Stop XLOOKUP Ranges from Changing When Copying Formulas
Product
Excel
Device & OS
not provided
Scenario
Copying an XLOOKUP formula across multiple cells or columns within an expanding data table.
Observed behavior
The lookup array and return array ranges change automatically due to relative referencing, causing errors or incorrect lookup results.
Before you start

Identify the specific cell ranges in your formula that must remain fixed, usually the lookup array and the return array, before editing the formula.

Solution 1Recommended

Lock Ranges Using Absolute References

Convert the relative cell references in your XLOOKUP formula to absolute references to lock the lookup and return arrays in place.

By default, spreadsheet applications use relative references. When you copy a formula down a column or across a row, the references shift accordingly. Using absolute references (indicated by dollar signs) tells the application to keep the exact row and column fixed, no matter where the formula is pasted.

1
Edit the formula

Double-click the cell containing your XLOOKUP formula, or select the cell and click into the formula bar at the top of the screen.

2
Lock the lookup array

Highlight the lookup array reference (e.g., A2:A100) in the formula. Press the F4 key on your keyboard to add dollar signs, transforming it into an absolute reference ($A$2:$A$100).

3
Lock the return array

Highlight the return array reference (e.g., B2:B100) and press the F4 key again to lock it as well ($B$2:$B$100).

4
Apply and copy the formula

Press Enter to save the formula. Click the cell, grab the fill handle in the bottom-right corner, and drag it across your table to copy the formula with the locked ranges.

Lock Ranges Using Absolute References
Pro Tip: You can press the F4 key multiple times to toggle between locking both rows and columns ($A$1), just the row (A$1), or just the column ($A1).
Seamless Formula Management

Easily Manage XLOOKUP and Absolute References with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP and VLOOKUP. With seamless absolute referencing and a highly intuitive interface, handling complex data lookups without syntax errors is easier than ever.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data table.
  2. 2. Start the formula: Type =XLOOKUP( in the formula bar to activate the function wizard.
  3. 3. Lock references easily: Select your lookup arrays and press F4 on your keyboard to instantly lock the ranges with absolute references.
  4. 4. Copy without shifting: Drag the fill handle to copy the formula across your worksheet—your defined ranges will stay perfectly in place.
Full compatibility with Microsoft Excel formulas and functions, including XLOOKUP.Intuitive formula editing bar with instant syntax highlighting.One-click absolute and relative reference toggling using the F4 shortcut key.Free and lightweight spreadsheet alternative for handling large datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formula ranges change when I drag them down?

By default, Excel uses relative cell references. When you copy or drag a formula down a row or across a column, the program automatically adjusts the row numbers and column letters relative to the new position to speed up sequential calculations.

Can I lock just the row or just the column in XLOOKUP?

Yes, you can use mixed references. By pressing the F4 key multiple times, you can cycle through locking both the row and column ($A$1), locking just the row (A$1), and locking just the column ($A1).

Does formatting my data as an Excel Table fix shifting ranges?

Yes, formatting your data as an official Table (using the Ctrl+T shortcut) allows you to use structured references, such as Table1[Column1]. Structured references automatically adjust to table expansions and do not shift unexpectedly when formulas are copied.