[Excel VBA] How to Use VLOOKUP for Table Search (Including Error Handling)

Introduction

In Excel data processing, the VLOOKUP function is one of the most commonly used tools. You can also utilize this powerful function within VBA macros via the WorksheetFunction object. This allows you to easily automate table lookup tasks, such as retrieving a price based on a product code.

However, when using VLOOKUP in VBA, error handling for cases where the search value is not found is extremely important. In this article, I will explain everything from basic usage to safe error handling methods to prevent your macro from crashing.

Basic Syntax of VLOOKUP in VBA

The syntax for using VLOOKUP in VBA is almost the same as using it on a worksheet.

WorksheetFunction.VLookup(LookupValue, TableArray, ColIndexNum, RangeLookup)

• LookupValue: The value you want to search for.
• TableArray: The cell range containing the data.
• ColIndexNum: The column number in the table from which to retrieve the value.
• RangeLookup: Use False for an exact match or True for an approximate match. Usually, False is used.

[Crucial] Error Handling for VLOOKUP

When VLOOKUP fails to find a value on a worksheet, it displays “#N/A” in the cell. However, if this happens with WorksheetFunction.VLookup in VBA, a runtime error occurs, and the macro stops immediately.

To avoid this, the safest and recommended method is to call VLOOKUP directly from the Application object and check the result using the IsError function.

Completed VBA Code (Recommended Pattern)

The following code uses Application.VLookup and IsError to perform a safe table lookup.

Sub SafeVLookupInVBA()

    ' Declare variables
    ' Note: result is declared as Variant to hold either a value or an error code
    Dim productCode As String
    Dim priceListTable As Range
    Dim result As Variant

    '--- Settings ---
    productCode = "P-003" ' The product code to search for
    ' The table to search (Column A has codes, Column B has prices)
    Set priceListTable = ThisWorkbook.Worksheets("PriceList").Range("A2:B100")
    '--- End Settings ---

    ' 1. Get the result using Application.VLookup
    '    If not found, this method returns an error value (the macro does NOT stop)
    result = Application.VLookup(productCode, priceListTable, 2, False)

    ' 2. Check if the return value is an error using IsError
    If IsError(result) Then
        ' Process when the value is not found
        MsgBox "Product code '" & productCode & "' was not found.", vbExclamation
    Else
        ' Process when the value is found
        MsgBox "The price for '" & productCode & "' is " & result & " yen.", vbInformation
    End If

End Sub

Explanation of Key Points

Application.VLookup(...)

Unlike WorksheetFunction.VLookup(...), using Application.VLookup(...) does not stop the macro even if the search value is not found. Instead, it returns a special error value.

IsError(result)

This function returns True if the variable result contains an error value. This allows you to easily and safely branch your code to handle both “found” and “not found” scenarios.

(Reference) Old Error Handling Method: On Error

There is also a method using On Error Resume Next to ignore errors, but this tends to make the code more complex.

Sub Vlookup_WithOnError()
    Dim result As Variant
    On Error Resume Next ' Continue processing even if an error occurs
    result = WorksheetFunction.VLookup("P-004", Range("A2:B100"), 2, False)
    On Error GoTo 0 ' Reset error handling

    If IsEmpty(result) Then ' Result is Empty if an error occurred
        MsgBox "Not found."
    Else
        MsgBox "Price: " & result
    End If
End Sub

Summary

The golden rule when using VLOOKUP in VBA is to handle errors correctly.

• Basic Call: WorksheetFunction.VLookup(...) (Prone to crashing)
• Recommended Error Handling: Get the result with result = Application.VLookup(...) and check it with If IsError(result) Then.
• Old Method: Trap errors with On Error Resume Next.

Unless you have a specific reason not to, I strongly recommend using the combination of Application.VLookup and the IsError function because it makes the code simpler and the intent clearer.