VBA VLookup in Excel — The "Not Found" Crash & When a Dictionary Wins

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-12

TL;DR — There are two ways to call VLOOKUP from VBA, and the difference decides whether a missing value crashes your macro. Application.WorksheetFunction.VLookup raises a run-time error when the value isn't found; Application.VLookup (no WorksheetFunction) returns an error value you can test with IsError. And once you're looping over thousands of rows, the real answer is usually neither — it's a Dictionary.

Sub VLookupTwoWays()
    Dim tbl As Range
    Set tbl = Sheet1.Range("A:B")

    ' (1) WorksheetFunction — CRASHES (run-time 1004) if "Acme" isn't found
    Debug.Print Application.WorksheetFunction.VLookup("Acme", tbl, 2, False)

    ' (2) Application — returns an ERROR VALUE if not found, no crash
    Dim r As Variant
    r = Application.VLookup("Ghost", tbl, 2, False)
    If IsError(r) Then Debug.Print "not found" Else Debug.Print r
End Sub

The mental model: VLOOKUP is a worksheet function visiting VBA

VLOOKUP was built to live in a cell. When you call it from VBA you're borrowing a worksheet function — and that loan comes with two strings attached that no cell formula ever warns you about: how it behaves when the value is missing, and how badly it scales when you call it in a loop. Keep that framing and both of this article's traps stop being surprises.

So the first question isn't "how do I write VLOOKUP in VBA?" — that's the easy part, one line. The real question is which VLOOKUP, because VBA exposes two of them with opposite failure behaviour, and then whether you should be calling it at all once a loop is involved.

The one rule: WorksheetFunction.VLookup crashes, Application.VLookup returns an error

This is the distinction behind the most-reported VBA VLOOKUP problem — the dreaded "Unable to get the VLookup property" / run-time error 1004:

Called through WorksheetFunction, a missing value raises a run-time error that stops your macro dead. Called through Application directly, a missing value comes back as an error value (like Error 2042) that you test with IsError — no crash.

Neither is "correct"; they're tools for two different situations. Use WorksheetFunction when a missing value is genuinely a bug and you want the macro to stop (or you've wrapped it in On Error). Use Application.VLookup when "not found" is a normal, expected outcome you want to handle gracefully.

' (A) WorksheetFunction + On Error — stop or branch on a real error
Dim price As Double
On Error Resume Next
price = Application.WorksheetFunction.VLookup(code, tbl, 2, False)
If Err.Number <> 0 Then
    price = 0                       ' default, and keep going
    Err.Clear
End If
On Error GoTo 0

' (B) Application.VLookup + IsError — the cleaner pattern for "might be missing"
Dim res As Variant
res = Application.VLookup(code, tbl, 2, False)
If IsError(res) Then price = 0 Else price = res

Pattern (B) reads better and doesn't hijack error handling for ordinary control flow, so reach for Application.VLookup whenever a miss is expected. (See VBA On Error for why scattering On Error Resume Next is a liability.)

The trap that actually costs you: VLOOKUP inside a loop is O(n²)

Here's the one the tutorials skip. A single VLOOKUP scans the lookup column until it finds a match. Put that call inside a loop over your rows, and you re-scan the whole column on every single iteration:

' ⚠ SLOW — 10,000 rows each VLOOKUP-ing a 10,000-row table.
' Up to 100,000,000 cell comparisons, and it re-reads the sheet every pass.
Dim i As Long
For i = 2 To 10000
    Cells(i, "C").Value = Application.WorksheetFunction.VLookup( _
        Cells(i, "A").Value, Sheet2.Range("A:B"), 2, False)
Next i

That's quadratic work, and on real data it turns a "why is my macro frozen?" coffee break into minutes. The number of comparisons grows with the square of your row count — double the rows, quadruple the wait.

The fix is to stop searching repeatedly. Read the lookup table into a Dictionary once — that's a hash table, so each lookup is O(1) instead of O(n) — and the whole job becomes linear:

Sub LookupWithDictionary()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    ' Read both columns into memory in ONE hit — see VBA Array for why this matters
    Dim lookup As Variant
    lookup = Sheet2.Range("A2:B10000").Value
    Dim i As Long
    For i = 1 To UBound(lookup, 1)
        dict(lookup(i, 1)) = lookup(i, 2)        ' key = col A, value = col B
    Next i

    ' Read the keys to resolve, resolve them in memory, write back in one hit
    Dim keys As Variant, out() As Variant, n As Long
    keys = Sheet1.Range("A2:A10000").Value
    n = UBound(keys, 1)
    ReDim out(1 To n, 1 To 1)
    For i = 1 To n
        If dict.Exists(keys(i, 1)) Then out(i, 1) = dict(keys(i, 1)) Else out(i, 1) = "n/a"
    Next i
    Sheet1.Range("C2").Resize(n, 1).Value = out      ' one write, not 10,000
End Sub

Same result, but it reads the sheet twice instead of 20,000 times, and the matching is hash-based. On 10,000 rows this is the difference between "instant" and "go get a coffee." (The other half of the speed-up — reading and writing arrays in one hit instead of cell-by-cell — is its own habit worth building.)

When to use which

The opinion: if you're calling VLOOKUP in a loop, you actually want a Dictionary

Here's the line I'll stand behind: VLOOKUP belongs in cells, not in loops. A worksheet function is a fine thing to call once from VBA to grab a single value. The moment it's inside a For loop running over your data, you've taken an operation designed for one lookup and asked it to re-scan a column thousands of times — and you'll feel it.

The instinct to "just use VLOOKUP, I know it" is exactly what keeps macros slow. The professional move is to recognise the shape of the problem: many lookups against one table is what a Dictionary is for. Build the table in memory once, resolve every row against it, write the answers back in a single shot. Your macro goes from quadratic to linear, and the code that does it is shorter than the error-handling you'd otherwise wrap around a looping VLOOKUP.

Common VBA VLOOKUP mistakes (and the fix)

When the lookups pile up — describe the join instead

You didn't want a debate about WorksheetFunction versus Application. You wanted "match this month's export to my price list and flag the codes that don't exist." By the time you've picked the right VLOOKUP, guarded the misses, and rewritten the loop as a Dictionary so it finishes this decade, the plumbing is the macro. ExcelMaster Agent lets you describe the join in plain English — "look up each code in column A against the price sheet, put the price in C, and list the ones with no match" — and it writes Python that does the whole thing in one pass, backing up your workbook first. Try it free →

Related guides

• VBA Dictionary — O(1) Lookups in Memory
• VBA Array — Read and Write a Range in One Hit
• VBA Copy Paste — Skip the Clipboard for Faster Macros
• VBA On Error — Resume Next vs GoTo & Why Macros Hide Bugs
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

How do I use VLOOKUP in VBA? Call it through the application object: Application.WorksheetFunction.VLookup(lookupValue, tableRange, colIndex, False). Always pass False as the fourth argument for an exact match. If the value might not exist, call Application.VLookup (without WorksheetFunction) instead and test the result with IsError, so a miss returns an error value rather than crashing the macro.

Why does VBA VLOOKUP give run-time error 1004? Because you called it through WorksheetFunction and the value wasn't found — that path raises an error instead of returning #N/A. Either wrap the call in On Error Resume Next and check Err.Number, or use Application.VLookup, which returns a testable error value you handle with If IsError(result).

What is the difference between Application.VLookup and WorksheetFunction.VLookup? They do the same lookup but fail differently. WorksheetFunction.VLookup raises a run-time error when nothing matches; Application.VLookup returns an error value (such as Error 2042) that you test with IsError. Use WorksheetFunction when a miss is a real bug, and Application when "not found" is expected.

Is VLOOKUP slow in VBA? A single call is fine. Calling it inside a loop over many rows is slow because each call re-scans the lookup column, making the work grow with the square of the row count. For many lookups, load the table into a Scripting.Dictionary once and resolve each row in O(1) — far faster on large data.

Can VBA VLOOKUP look up a value in another sheet? Yes — qualify the table range with the sheet: Application.WorksheetFunction.VLookup(key, Worksheets("Prices").Range("A:B"), 2, False). Use a fully qualified Worksheets("name").Range(...) reference so the lookup doesn't accidentally read the active sheet.