Fix Excel VBA VLOOKUP Errors from Incorrect Worksheet References
Question details
Correct a VBA VLOOKUP that uses worksheet ranges across workbooks and may raise an application-defined or object-defined error.

- 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.
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.
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.
In Excel, open Developer > Visual Basic, then select Insert > Module in the VBA editor. Keep the source and destination workbooks open.
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
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.

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.

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.




