Formula Errors

How to Fix XLOOKUP Range Shifting When Copied in Spreadsheet

Chanuka Geekiyanage Oct 1, 2026 868 views

Question details

The user needs to copy an XLOOKUP formula down a column where the lookup value updates automatically, but the source data and return arrays remain fixed.

How to Keep XLOOKUP Ranges Fixed When Copying Formulas

Product: Spreadsheet
Device & OS: not provided
Scenario: Filling an XLOOKUP formula down multiple rows to process a large dataset.
Observed behavior: The lookup and return array ranges shift down unintentionally because the row references in the formula are not locked with absolute references.

Before you start

Identify the exact cell ranges for your source data and ensure you understand which part of the formula needs to change dynamically and which parts must stay fixed.

Solution 1Recommended

Apply Absolute References to Lock XLOOKUP Ranges

Use dollar signs ($) to lock the row and column references for your lookup array and return array so they do not change when the formula is filled down.

When you copy a formula in a spreadsheet, relative cell references automatically adjust based on the new row or column. To prevent this, you must convert relative references to absolute references by adding a dollar sign ($) before the column letter and row number.

Enter the relative lookup value

Click your target cell (e.g., E2) and enter the lookup value reference without dollar signs (e.g., C2) so it updates automatically to C3, C4, etc., when copied.

Enter the absolute lookup array

Input your lookup array range and manually type dollar signs before both column letters and row numbers (e.g., Sheet1!$B$2:$B$403).

Enter the absolute return array

Input your return array range exactly the same way, ensuring both columns and rows are locked (e.g., Sheet1!$F$2:$F$403).

Finalize and fill down

Ensure the complete formula looks like =XLOOKUP(C2,Sheet1!$B$2:$B$403,Sheet1!$F$2:$F$403), press Enter, then click and drag the bottom-right fill handle down to copy the formula.

Apply Absolute References to Lock XLOOKUP Ranges

Keyboard Shortcut for Absolute References: You can quickly apply absolute references by highlighting the specific range in the formula bar and pressing the F4 key on your keyboard to automatically add the dollar signs.

Efficient Formula Management

Master XLOOKUP and Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced functions like XLOOKUP, VLOOKUP, and dynamic arrays. You can easily manage absolute and relative references to process massive datasets efficiently and flawlessly.

1. Open Data: Open your dataset in WPS Spreadsheet.
2. Trigger Formula: Type =XLOOKUP( in your target cell to trigger the smart formula hint and syntax guide.
3. Lock Ranges: Select your lookup and return arrays, then press F4 to instantly lock your ranges.
4. Apply Everywhere: Double-click the fill handle to apply the formula perfectly across all rows in the column.

100% compatible with Microsoft Excel formulas and functionsQuickly toggle absolute references using the F4 keyIntuitive formula builder with real-time error checking toolsFree to download and lightweight on system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP returning #N/A when I drag it down?

This usually happens if your lookup array is not locked with absolute references (e.g., $A$2:$A$100). As you drag the formula down, the range shifts, missing the data at the top of your original list.

What is the difference between $B403 and $B$403 in a formula?

$B403 locks only the column (B) but allows the row (403) to shift when copied down. $B$403 locks both the column and the row, ensuring the exact cell reference never changes regardless of where you copy the formula.

Can I use the F4 shortcut on multiple cell ranges at once?

No, you need to select or click within each individual cell reference or range in the formula bar, then press F4 to toggle between relative and absolute references for that specific block.

Excel & Data Problems