How to Fix Excel VLOOKUP Formula Returning Incorrect Results
Question details
The user needs to fix a VLOOKUP formula that stops working and returns incorrect results after being copied across multiple cells.

- 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.
Double-click the cell returning the incorrect result so you can visually inspect the highlighted lookup range on your spreadsheet before making edits.
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.
Click on the first cell containing your original, working VLOOKUP formula.
Click into the formula bar and locate the table array portion of your formula (e.g., A1:Z100).
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.
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.

Use HLOOKUP for Horizontal Layouts
Since the data is being converted from a vertical layout to a horizontal layout, VLOOKUP (Vertical Lookup) may no longer be the correct function.
Convert Layout Using Paste Special Transpose
If you just need to switch your vertical data to match a horizontal workbook structure, bypassing formulas entirely might be the cleanest solution.
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. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your broken VLOOKUP formula.
- 2. Activate Formula Highlight: Double-click the cell returning the incorrect result to display the color-coded reference boxes.
- 3. Lock the Search Range: Select the table array in the formula bar and press F4 to insert absolute references ($).
- 4. Fill the Results: Drag the bottom-right corner of the cell to instantly fill the rest of your horizontal entries correctly.

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.




