Fix VLOOKUP Range Shifting Error When Copying Formulas in Spreadsheet
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.

- 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.
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.
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.
Double-click the cell containing your first VLOOKUP formula to enter edit mode.
Highlight the table_array argument in your formula (the second part of the VLOOKUP function, for example, B1:C196).
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.
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 Mixed References to Lock Rows Only
Lock only the row numbers if you need the columns to remain flexible when copying formulas across horizontally.
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. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Start the VLOOKUP function: Type =VLOOKUP( into the target cell and click the cell containing your lookup value.
- 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. 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.

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.




