Troubleshooting VLOOKUP and MATCH when called via WorksheetFunction.

When using Application.WorksheetFunction.VLookup or Application.WorksheetFunction.Match in VBA, code execution abruptly halts with Runtime Error 1004: Unable to get the VLookup property of the WorksheetFunction class whenever a lookup value cannot be found in the target table. While standard worksheet formulas simply return #N/A in a cell, calling these functions through the WorksheetFunction object treats a missing match as a critical runtime exception that breaks macro execution.

Quick Answer

Drop the .WorksheetFunction qualifier and call the method directly from the Application object: result = Application.VLookup(...). Assign the output to a Variant variable. If the lookup value is not found, Application.VLookup returns a catchable error value instead of crashing, which you can check using If IsError(result) Then.

---

Why WorksheetFunction Throws a Runtime Error

Excel provides two distinct pathways for executing native spreadsheet functions inside VBA, and they handle lookup failures very differently:

Pathway 1: Application.WorksheetFunction.VLookup
  Lookup item NOT found ──> Halts execution with Runtime Error 1004
                            Requires On Error GoTo to handle

Pathway 2: Application.VLookup (Late-Bound)
  Lookup item NOT found ──> Returns a Variant containing CVErr(xlErrNA)
                            Can be evaluated cleanly using IsError()

When you use WorksheetFunction.VLookup, VBA forces the function to comply with standard COM error propagation: if the function fails to produce a result, it raises a fatal runtime exception. Conversely, invoking Application.VLookup replicates normal spreadsheet behavior: it packages the failure as an #N/A error value and hands it back to your code.

For a broader breakdown of why #N/A behaves this way across different VBA calls, consult #N/A in VBA-called WorksheetFunctions.

---

The Preferred Solution: Safe Lookups with Application.VLookup

To handle missing values cleanly without cluttering your code with error traps, follow these three rules:

1. Call the function directly on Application (omit WorksheetFunction).
2. Declare the receiving variable as a Variant (a typed variable like String or Double cannot hold an error value).
3. Check the result using the built-in IsError() function.

Sub SafeLookupExample()
    Dim lookupVal As Variant
    Dim lookupRange As Range
    Dim result As Variant

    lookupVal = "EMP-9999" ' Assume this ID does not exist in the table
    Set lookupRange = Sheet1.Range("A2:B100")

    ' Call Application.VLookup directly
    result = Application.VLookup(lookupVal, lookupRange, 2, False)

    ' Check whether the lookup failed
    If IsError(result) Then
        ' Handle the missing record gracefully
        Debug.Print "Record " & lookupVal & " was not found in the database."
    Else
        ' Safe to process the return value
        Debug.Print "Found match: " & result
    End If
End Sub

The exact same design applies to Application.Match:

Dim matchedRow As Variant
matchedRow = Application.Match("Product_X", Sheet1.Range("A1:A500"), 0)

If IsError(matchedRow) Then
    MsgBox "Product not found!", vbExclamation
Else
    MsgBox "Product found at row: " & CLng(matchedRow)
End If

---

Alternative: Handling Errors with WorksheetFunction

If your project requires using WorksheetFunction (for example, to maintain strict IntelliSense auto-complete during development), you must wrap the call in a structured error trap:

Sub WorksheetFunctionWithTrap()
    Dim result As String

    On Error Resume Next
    result = Application.WorksheetFunction.VLookup("EMP-9999", Sheet1.Range("A2:B100"), 2, False)

    If Err.Number <> 0 Then
        If Err.Number = 1004 Then
            Debug.Print "Item not found in table."
        Else
            Debug.Print "Unexpected error: " & Err.Description
        End If
        Err.Clear
    Else
        Debug.Print "Match: " & result
    End If
    On Error GoTo 0
End Sub

While functional, this approach requires more lines of code, runs slower inside large loops, and risks accidentally suppressing unrelated errors if not handled carefully.

---

Diagnostic Checklist for Recurring Lookup Breaks

If your lookup still fails or generates unexpected errors after switching to Application.VLookup, work through these common data issues:

1. Data Type Mismatch Between Search Key and Table (Runtime Error 13)

If you search for a numeric ID stored as text in the table (or vice versa), VLOOKUP and MATCH will not find a match, even if the values look identical to the naked eye.

• The Diagnostic: Check the variable type in the Immediate Window using ? TypeName(lookupVal). If the worksheet cells store numbers as text (with a green error triangle or leading apostrophe), convert the search key to text using CStr(lookupVal) or to a number using CLng(lookupVal).
• If assigning the output to a typed variable causes a crash, verify your data types against Runtime Error 13: Type Mismatch (Trying to perform math on a string variable).

2. Range Reference Failures on Other Sheets

If your code runs from a standard module and references a range using Range("A2:B100") without specifying the worksheet, Excel defaults to the ActiveSheet. If the user is viewing a different tab, the lookup evaluates against the wrong grid and crashes with Runtime Error 1004: Method ‘Range’ of object ‘_Worksheet’ failed.

• The Fix: Always qualify lookup ranges: ThisWorkbook.Sheets("Data").Range("A2:B100").

3. Omitting the Exact Match Parameter

In both VLookup and Match, the final argument controls the matching mode:

• In VLookup(lookup_val, table, col_index, [range_lookup]), omitting the fourth parameter defaults to True (Approximate Match), which requires the first column to be sorted ascending. If your data is unsorted, it will return incorrect rows or fail. Always pass False or 0 for exact matches.
• In Match(lookup_val, lookup_array, [match_type]), omitting the third parameter defaults to 1 (Less Than), which also requires sorted data. Always pass 0 for exact matches.

To evaluate variable contents and step through lookup logic line by line, test your variables in real time using How to use the Immediate Window to debug variable values in real-time.

If you are developing custom spreadsheet functions that need to return programmatic #N/A or #VALUE! errors directly to grid cells based on lookup results, see Using CVErr to return custom Excel errors from VBA.