Fix VLOOKUP Table Array Changing When Copied Down in Excel
Question details
The user needs to prevent the VLOOKUP table array reference from shifting downward when copying or dragging the formula down a column.

- 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.
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.
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.
Double-click the cell containing your VLOOKUP formula to enter edit mode, or select the cell and click into the formula bar.
Locate the table array portion of your formula (for example, Data!B2:E110) and highlight it with your cursor.
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.
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).
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.

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. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you need to merge or look up.
- 2. Start your VLOOKUP formula: In your target cell, type =VLOOKUP( and click on the specific lookup value you want to search for.
- 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. Complete and fill: Enter the column index number, type FALSE, close the parenthesis, and press Enter. Then drag the formula down flawlessly.

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.




