Formula Errors

How to Fix VLOOKUP Range Changing When Copied Down

Steve K Oct 1, 2026 869 views

Question details

The user needs to prevent the VLOOKUP table array (lookup range) from shifting down when copying or dragging the formula to other rows.

How to Fix VLOOKUP Range Changing When Copied Down

Product: Spreadsheet
Device & OS: not provided
Scenario: Copying or filling a VLOOKUP formula down a column to populate multiple rows of data automatically.
Observed behavior: The lookup range increments its row numbers as it moves down (e.g., from G5:J7 to G6:J8), resulting in inaccurate lookups or #N/A errors.

Before you start

Ensure you have identified the exact range of your source data table that needs to remain constant before modifying your VLOOKUP formula.

Solution 1Recommended

Use Absolute References to Lock the Lookup Range

Applying dollar signs ($) to your cell references makes them absolute, preventing the row and column coordinates from shifting when the formula is filled down.

By default, spreadsheet applications use relative references. When you copy a formula down one row, the cell references also shift down one row. To fix this, you must convert the table array reference in your VLOOKUP formula into an absolute reference.

Select the Formula Cell

Click on the cell containing your original VLOOKUP formula to make it active.

Highlight the Table Array

Click into the formula bar at the top of the screen and highlight the range coordinates representing your lookup table (e.g., G5:J7).

Apply Absolute References

Press the F4 key on your keyboard. This will automatically add dollar signs to the column letters and row numbers, changing the range to $G$5:$J$7.

Fill the Formula Down

Press Enter to save the formula. Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the locked formula to the rest of the rows.

Use Absolute References to Lock the Lookup Range

F4 Key Shortcut: Pressing F4 repeatedly cycles through different types of references: absolute ($G$5), mixed row (G$5), mixed column ($G5), and relative (G5). Ensure both letters and numbers have dollar signs for a fully locked range.

Solution 2

Define a Named Range for the Lookup Table

Creating a named range automatically acts as an absolute reference. It makes formulas easier to read and completely immune to drag-and-fill shifting.

Solve Formula Errors Easily

Master VLOOKUP and Other Formulas in WPS Spreadsheet

WPS Spreadsheet provides intuitive formula hints, an easy-to-use Name Manager, and seamless keyboard shortcut support to help you manage complex lookups effortlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your VLOOKUP data.
2. Edit the Formula: Double-click the cell with the VLOOKUP formula and select the table array reference.
3. Lock the Range: Press the F4 key to instantly lock the range with absolute references ($).
4. Apply Across Rows: Drag the fill handle to apply the locked formula across multiple rows perfectly.

Fully compatible with Microsoft Excel (.xlsx) formats, formulas, and functions like VLOOKUP.Built-in error checking helps quickly identify shifting ranges and #N/A errors.Lightweight, ad-free interface that runs smoothly even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return #N/A when copied down?

When copied down without locking the range, the lookup table reference shifts downwards relative to the row. If the lookup value falls outside the newly shifted range, the formula cannot find a match and returns an #N/A error.

What is the shortcut to lock a cell reference in VLOOKUP?

The standard keyboard shortcut is the F4 key. Select the cell reference in the formula bar and press F4 to automatically add dollar signs, converting a relative reference (A1) into an absolute reference ($A$1).

Can I lock just the row and not the column?

Yes, this is called a mixed reference. By pressing F4 multiple times, you can format the reference to lock only the row (A$1) or only the column ($A1). This is highly useful when filling formulas across both rows and columns.

Do I need to lock the lookup value reference too?

Usually, no. The lookup value (the first argument in VLOOKUP) needs to remain a relative reference so it changes appropriately as you copy the formula down to search for the next row's item.

Excel & Data Problems