Formula Errors

Fix VLOOKUP Table Array Changing When Copied Down in Excel

Jason MillerJason Miller Nov 2, 2026 870 views

Question details

The user needs to prevent the VLOOKUP table array reference from shifting downward when copying or dragging the formula down a column.

How to Fix a VLOOKUP Table Array That Changes When Copied Down
Product
Spreadsheets
Device & OS
not provided
Scenario
Filling down a VLOOKUP formula to apply it to multiple rows of data, such as a list of SKUs or Brands.
Observed behavior
The table array range shifts relatively when the formula is copied down, causing the lookup area to miss data and return formula errors.
Before you start

Ensure your lookup data sheet contains all the required information and that you know the exact range of your table array before editing the formula.

Solution 1Recommended

Lock the Table Array with Absolute References

Use dollar signs ($) to lock the rows and columns of your lookup range so it remains static when the formula is copied to other cells.

By default, spreadsheet formulas use relative references. This means if you drag a formula down one row, the reference to the lookup table also moves down one row. To fix this, you must apply absolute references using dollar signs.

1
Edit the VLOOKUP formula

Double-click the cell containing your VLOOKUP formula to enter edit mode, or select the cell and click into the formula bar.

2
Highlight the table array

Locate the table array portion of your formula (for example, Data!B2:E110) and highlight it with your cursor.

3
Apply absolute references

Press the F4 key on your keyboard to insert dollar signs before the column letters and row numbers. The range will change to Data!$B$2:$E$110.

4
Ensure exact match formatting

Make sure your formula ends with FALSE to require an exact match, yielding a complete formula like =VLOOKUP($B2,Data!$B$2:$E$110,2,FALSE).

5
Copy the formula down

Press Enter to save the formula. Click the cell again, grab the small square fill handle at the bottom-right corner, and drag it down to apply the fixed formula to the rest of the column.

Lock the Table Array with Absolute References
Exact Match Advantage: Using FALSE ensures an exact match for your VLOOKUP. Unlike TRUE, it does not require your raw lookup data to be sorted alphabetically, preventing unintended errors when matching specific SKUs or IDs.
Solve Formula Errors Easily

Easily Manage Formulas and Data with WPS Spreadsheet

WPS Spreadsheet offers a robust and highly compatible environment for data analysis, fully supporting standard formulas like VLOOKUP with intuitive tools to manage cell references seamlessly.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you need to merge or look up.
  2. 2. Start your VLOOKUP formula: In your target cell, type =VLOOKUP( and click on the specific lookup value you want to search for.
  3. 3. Select and lock the table array: Navigate to the sheet containing your source data, highlight the table array, and press the F4 key to immediately apply the $ signs to lock the range.
  4. 4. Complete and fill: Enter the column index number, type FALSE, close the parenthesis, and press Enter. Then drag the formula down flawlessly.
Fully compatible with Microsoft Excel formulas, functions, and file formatsIntuitive formula bar with one-click absolute referencing using the F4 keyBuilt-in error checking to troubleshoot #N/A or #REF! formula errors quicklyFree, lightweight, and works seamlessly across Windows, Mac, Linux, and mobile devices
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return #N/A when I drag it down?

When you copy a formula down without locking the table array with absolute references ($), the lookup range shifts downward relative to the rows. This causes the formula to miss the data located in the upper rows of your original range, resulting in a #N/A error.

What is the keyboard shortcut to lock a formula reference?

You can highlight the cell reference inside the formula bar and press the F4 key on Windows (or Cmd + T on Mac) to automatically toggle between relative and absolute references, changing a reference like A1 to $A$1.

Should I use TRUE or FALSE at the end of my VLOOKUP formula?

Use FALSE for an exact match, which is highly recommended for looking up specific data like product SKUs, IDs, or exact names. TRUE is used for approximate matches and requires the first column of the table array to be sorted in ascending order.

How can I lock just the column or just the row in a formula?

By pressing the F4 key multiple times while highlighting a reference, you can cycle through mixed references. A reference like $A1 locks only the column, while A$1 locks only the row, which is useful when dragging formulas across both rows and columns.