VBA & Macro Problems

Fix Excel VBA VLOOKUP Errors from Incorrect Worksheet References

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Correct a VBA VLOOKUP that uses worksheet ranges across workbooks and may raise an application-defined or object-defined error.

Fix Excel VBA VLOOKUP Errors from Incorrect Worksheet References
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro looks up values from a destination worksheet in a range on another worksheet or workbook.
Observed behavior
The macro may use row bounds calculated on one worksheet to address another, while an invalid range, missing match, or incorrect column index can also cause errors or unexpected results.
Before you start

Open both workbooks and identify their exact workbook and worksheet names, the lookup column, and the column to return. Check the destination column before running the example because it writes results there.

Solution 1Recommended

Qualify worksheet ranges and handle VLOOKUP errors

Use this approach when a macro looks up destination values in a source worksheet, especially when the two sheets have different row counts.

Calculate the source and destination row bounds separately, and qualify every Cells and Range reference with its worksheet. The first column of the lookup range must contain the lookup values.

The example assumes headers in row 1, source data in columns A:B, destination lookup values in column A, and results in destination column B. Adjust these assumptions and the names before running it.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then select Insert > Module in the VBA editor. Keep the source and destination workbooks open.

2
Add and adapt the lookup code

Paste the following code into the module. Replace the workbook and worksheet names with the exact names shown in Excel, and adjust the columns if needed: Sub LookupAcrossWorkbooks() Dim ws As Worksheet, destWs As Worksheet Dim wsRng As Range Dim sourceLastRow As Long, destLastRow As Long, destRow As Long Dim result As Variant Set ws = Workbooks("Source.xlsx").Worksheets("Sheet1") Set destWs = Workbooks("Destination.xlsx").Worksheets("Sheet1") sourceLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row destLastRow = destWs.Cells(destWs.Rows.Count, "A").End(xlUp).Row If sourceLastRow < 2 Or destLastRow < 2 Then Exit Sub Set wsRng = ws.Range(ws.Cells(2, "A"), ws.Cells(sourceLastRow, "B")) For destRow = 2 To destLastRow result = Application.VLookup(destWs.Cells(destRow, "A").Value, wsRng, 2, False) If IsError(result) Then destWs.Cells(destRow, "B").Value = "Lookup error" Else destWs.Cells(destRow, "B").Value = result End If Next destRow End Sub

3
Run and check the results

Run LookupAcrossWorkbooks from the VBA editor using Run > Run Sub/UserForm. Check destination column B and investigate any "Lookup error" entries, including missing matches or mismatched data types.

Qualify worksheet ranges and handle VLOOKUP errors
Check the return column: The VLOOKUP column index is relative to the lookup range. An index of 1 returns the lookup value itself; use 2 for the second column, and never exceed the range width.
Free Microsoft Office alternative

Work with lookup tables in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix errors in Microsoft Excel VBA macros, and macro compatibility can vary.

Use spreadsheet lookup formulas to work with tabular data.Open and edit common Microsoft Excel formats, including XLSX and XLS.Navigate spreadsheets with a familiar, easy-to-use interface.Use a lightweight office suite for everyday document work.
microsoft office alternative - wps office

Frequently Asked Questions

Why can row numbers from one worksheet cause an error on another?

The sheets may have different layouts or data lengths. Calculate the last row separately with each worksheet's Cells and Rows references, then use those bounds only for that worksheet.

Why does VLOOKUP return the value I searched for?

A column index of 1 returns the first column of the lookup range, which contains the lookup value. Use 2 to return the second column, provided the range includes it.

How can VBA handle a missing VLOOKUP match?

Assign Application.VLookup to a Variant and check it with IsError before writing the result. With False as the fourth argument, a missing exact match returns an error value.

What should I check if the macro cannot find a workbook or worksheet?

Open both workbooks and compare the names in Workbooks("...") and Worksheets("...") with the actual workbook and sheet names, including the workbook file extension.