Formula Errors

Fix VLOOKUP Range Shifting Error When Copying Formulas in Spreadsheet

Maira MehtabMaira Mehtab Nov 10, 2026 868 views

Question details

Users experience #N/A errors when copying a VLOOKUP formula down a column because the lookup table array shifts relative to the new rows.

How to Fix VLOOKUP Range Shifting When Copying Formulas
Product
Spreadsheet
Device & OS
not provided
Scenario
Copying or dragging a VLOOKUP formula down multiple rows to find matched data from a specific reference table.
Observed behavior
The cell coordinates of the lookup table automatically increase (e.g., B1:C196 becomes B2:C197), causing the formula to miss values located in the original range and return an #N/A error.
Before you start

Identify the exact cell range that contains your complete lookup table and ensure your VLOOKUP formula is correctly pointing to the lookup value in the first row before modifying the cell references.

Solution 1Recommended

Use Fully Absolute References to Lock the Lookup Table

Apply dollar signs ($) to both the column letters and row numbers to ensure the table range remains completely static when copied.

By default, spreadsheet software uses relative references. When you copy a formula, the referenced cells move in the same direction. Converting the lookup table range into an absolute reference prevents this shifting.

1
Edit the initial formula

Double-click the cell containing your first VLOOKUP formula to enter edit mode.

2
Locate the table_array

Highlight the table_array argument in your formula (the second part of the VLOOKUP function, for example, B1:C196).

3
Apply absolute formatting

Press the F4 key on your keyboard once. This automatically adds dollar signs before the column letters and row numbers (changing it to $B$1:$C$196). Alternatively, you can type the dollar signs manually.

4
Copy the formula down

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

Use Fully Absolute References to Lock the Lookup Table
Pro Tip: Using the F4 shortcut is the fastest way to toggle between relative, absolute, and mixed references while typing or editing formulas.
WPS Spreadsheet Feature

Write and Troubleshoot VLOOKUP Formulas Easily in WPS Spreadsheet

WPS Spreadsheet provides intuitive formula hints, easy cell referencing, and powerful data analysis tools to help you manage complex lookups without syntax errors.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
  2. 2. Start the VLOOKUP function: Type =VLOOKUP( into the target cell and click the cell containing your lookup value.
  3. 3. Select and lock the table array: Drag your mouse to select the lookup table range, then immediately press F4 to lock it as an absolute reference ($B$1:$C$196).
  4. 4. Complete and fill: Finish the formula with the column index and match type, hit Enter, and double-click the fill handle to apply it flawlessly down the entire column.
100% compatible with Microsoft Excel formulas and formatsBuilt-in formula suggestions and real-time error checkingEasily toggle absolute references using the F4 key shortcutLightweight and completely free to download
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return #N/A even with absolute references?

The #N/A error can still occur if the exact lookup value doesn't exist in the first column of your table array. It may also happen if there are trailing spaces or formatting mismatches (such as text formatted as numbers) between the lookup value and the source data.

What is the keyboard shortcut to add dollar signs ($) in a formula?

While editing a formula, place your cursor inside the cell reference or highlight the range, and press the F4 key on Windows (or Cmd+T on Mac). Each press toggles through fully absolute ($A$1), mixed row-locked (A$1), mixed column-locked ($A1), and relative (A1) references.

Can I use named ranges instead of absolute references to stop VLOOKUP from shifting?

Yes. You can highlight your lookup table, type a descriptive name in the Name Box (located to the left of the formula bar), and use that name in your VLOOKUP formula (e.g., =VLOOKUP(A1, SalesData, 2, FALSE)). Named ranges are absolute by default and will never shift when copied.