Function Problems

How to Fix VLOOKUP Results Changing When Filled Down

Maira Mehtab Sep 22, 2026 869 views

Question details

The user's VLOOKUP formula works correctly in the first cell but returns incorrect results or errors when the formula is copied or filled down to subsequent rows.

Product: Spreadsheets
Device & OS: not provided
Scenario: Copying or dragging a VLOOKUP formula down a column to apply it to multiple lookup values.
Observed behavior: The lookup table range shifts relatively with each row, causing the formula to reference incorrect cells and return wrong data or #N/A errors.

Before you start

Click on the cell with your initial VLOOKUP formula and look at the formula bar to verify that the table array references correctly cover your data source.

Solution 1Recommended

Use Absolute References to Lock the Lookup Range

Applying absolute references by adding dollar signs ($) to your table array ensures the lookup range stays exactly the same when you copy the formula down.

By default, cell references in formulas are relative. This means if you move down one row, the references also shift down one row. Locking the lookup array prevents this shifting.

Select the initial formula cell

Click on the cell that contains the first, correctly working VLOOKUP formula.

Highlight the table array

In the formula bar, highlight the portion of the formula that represents your lookup table (for example, B1:D4).

Apply absolute references

Press the F4 key on your keyboard. This will automatically add dollar signs to your column and row references, changing it to $B$1:$D$4.

Fill the formula down

Press Enter to save the formula. Then, click and drag the small square at the bottom-right corner of the cell (the fill handle) down to copy the updated formula to the rest of the rows.

Pro Tip: You only need to lock the table array. The lookup value reference (e.g., E2) should usually remain relative so it correctly updates for each row you drag the formula to.

Solution 2

Clean Extra Spaces in Lookup Values

If locking the range does not resolve all errors, the issue may be caused by hidden trailing or leading spaces in your lookup values or table array.

Manage Data Seamlessly

Use VLOOKUP Flawlessly in WPS Spreadsheet

WPS Office offers a powerful, fully compatible Spreadsheet tool that makes handling VLOOKUPs, data analysis, and formula debugging incredibly easy and efficient.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Edit the VLOOKUP formula: Double-click the cell with the VLOOKUP formula and select the table array reference.
3. Lock the range with F4: Press F4 to instantly lock the range with absolute references (adding dollar signs).
4. Drag to apply: Drag the fill handle down to apply the precise, locked formula across your entire dataset.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx, .csv)Smart formula hints to easily toggle between absolute and relative referencesLightweight architecture that runs smoothly even when calculating large datasetsFree built-in data cleaning tools to automatically remove extra spaces

microsoft office alternative - wps office

Frequently Asked Questions

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

When you drag a formula down without locking the lookup range, the reference shifts down row by row. Eventually, it starts looking outside your actual data table, which causes it to miss valid matches and return an #N/A error.

How do I toggle between absolute and relative references quickly?

You can highlight the cell reference in the formula bar and press the F4 key on your keyboard. Pressing it multiple times cycles through full absolute ($A$1), row absolute (A$1), column absolute ($A1), and back to relative (A1).

Can I use a named range instead of absolute references in VLOOKUP?

Yes. By selecting your lookup table data and giving it a specific name in the Name Box (e.g., 'SalesData'), you can write your formula as =VLOOKUP(E2, SalesData, 2, FALSE). Named ranges automatically act as absolute references.

Excel & Data Problems