How to Stop XLOOKUP Ranges from Changing When Copying in Excel
Question details
The user wants to prevent XLOOKUP formula ranges from shifting or changing when copying the formula to other cells.

- 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.
Identify the specific cell ranges in your formula that must remain fixed, usually the lookup array and the return array, before editing the formula.
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.
Double-click the cell containing your XLOOKUP formula, or select the cell and click into the formula bar at the top of the screen.
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).
Highlight the return array reference (e.g., B2:B100) and press the F4 key again to lock it as well ($B$2:$B$100).
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.

Use VLOOKUP with Locked Entire-Column References
If XLOOKUP behaves unpredictably with an expanding table, use VLOOKUP locked to entire columns as a reliable alternative.
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. Open your workbook: Launch WPS Spreadsheet and open the document containing your data table.
- 2. Start the formula: Type =XLOOKUP( in the formula bar to activate the function wizard.
- 3. Lock references easily: Select your lookup arrays and press F4 on your keyboard to instantly lock the ranges with absolute references.
- 4. Copy without shifting: Drag the fill handle to copy the formula across your worksheet—your defined ranges will stay perfectly in place.

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.




