Formula Errors

How to Fix Excel VLOOKUP Formula Returning Incorrect Results

Natalie TaylorNatalie Taylor Oct 29, 2026 872 views

Question details

The user needs to fix a VLOOKUP formula that stops working and returns incorrect results after being copied across multiple cells.

How to Troubleshoot an Excel VLOOKUP Formula That Stops Working
Product
Excel
Device & OS
not provided
Scenario
Converting a vertically oriented loan repayment worksheet into a horizontal layout using the VLOOKUP function.
Observed behavior
The formula correctly fetches data for the first 100 entries but then starts returning incorrect results near the end of the lookup range.
Before you start

Double-click the cell returning the incorrect result so you can visually inspect the highlighted lookup range on your spreadsheet before making edits.

Solution 1Recommended

Fix Absolute References in the Lookup Range

The most common reason VLOOKUP stops working when copied is that the table array range shifts because it lacks absolute references (dollar signs).

When you drag a formula to copy it to other cells, Excel automatically adjusts the cell references. If you do not lock your lookup table range, it will shift down or to the right, causing values at the beginning of the table to fall out of the search area.

1
Select the Formula Cell

Click on the first cell containing your original, working VLOOKUP formula.

2
Highlight the Table Array

Click into the formula bar and locate the table array portion of your formula (e.g., A1:Z100).

3
Apply Absolute References

Highlight the table array reference and press the F4 key. This will add dollar signs (e.g., $A$1:$Z$100), locking the range in place.

4
Copy the Formula

Press Enter to save the formula, then click and drag the fill handle to copy the updated formula across the rest of your horizontal layout.

Fix Absolute References in the Lookup Range
Range Locked: By locking the lookup range, your formula will always search the exact same table array no matter where you copy it in your workbook.
Effortless Spreadsheet Formulas

Easily Manage and Troubleshoot Formulas with WPS Spreadsheet

WPS Spreadsheet offers a highly intuitive formula environment. Its intelligent color-coded formula auditing makes troubleshooting cell references incredibly simple, allowing you to instantly spot missing absolute references.

  1. 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your broken VLOOKUP formula.
  2. 2. Activate Formula Highlight: Double-click the cell returning the incorrect result to display the color-coded reference boxes.
  3. 3. Lock the Search Range: Select the table array in the formula bar and press F4 to insert absolute references ($).
  4. 4. Fill the Results: Drag the bottom-right corner of the cell to instantly fill the rest of your horizontal entries correctly.
100% compatible with Microsoft Excel formulas and formats (.xlsx)Intelligent formula auditing and visual error checkingBuilt-in financial templates for loan repayment schedulesLightweight, fast, and completely free to use
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP work for the first few rows and then return #N/A?

This usually happens when you use relative references for your table array. As you drag the formula down or across, the lookup range also moves, causing valid data to fall out of the search window. You must lock the range using absolute references (e.g., $A$1:$Z$100).

Can I use VLOOKUP to search data horizontally?

No, VLOOKUP (Vertical Lookup) is designed specifically for vertical data where the search value is in the first column. If you need to search across the top row of a horizontal dataset, you must use the HLOOKUP function.

Why is VLOOKUP returning a wrong value instead of an error message?

This occurs when the range_lookup argument (the 4th part of the formula) is set to TRUE or omitted, which tells Excel to find an approximate match. If your data isn't sorted in ascending order, it will return incorrect results. To fix this, always use FALSE as your final argument to force an exact match.

How do I switch a vertical table to a horizontal layout without using lookup formulas?

You can use the Paste Special feature. Copy your original vertical table, right-click the cell where you want the new layout to start, select 'Paste Special', check the 'Transpose' option, and click OK. This automatically converts columns to rows.