VBA VLookup in Excel — der "nicht gefunden"-Absturz und wann ein Dictionary gewinnt

Getestet mit: Excel 365 v2509 · Excel 2021 · Excel 2019 · zuletzt geprüft am 12.06.2026

Kurzfassung — Es gibt zwei Wege, VLOOKUP (im deutschen Excel SVERWEIS) aus VBA aufzurufen, und der Unterschied entscheidet, ob ein fehlender Wert dein Makro abstürzen lässt. Application.WorksheetFunction.VLookup löst einen Laufzeitfehler aus, wenn der Wert nicht gefunden wird; Application.VLookup (ohne WorksheetFunction) liefert einen Fehlerwert, den du mit IsError prüfen kannst. Und sobald du über Tausende Zeilen läufst, ist die richtige Antwort meist keines von beiden — es ist ein Dictionary.

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

    ' (1) WorksheetFunction — STÜRZT AB (Laufzeitfehler 1004), wenn "Acme" fehlt
    Debug.Print Application.WorksheetFunction.VLookup("Acme", tbl, 2, False)

    ' (2) Application — liefert einen FEHLERWERT, wenn nicht gefunden, kein Absturz
    Dim r As Variant
    r = Application.VLookup("Ghost", tbl, 2, False)
    If IsError(r) Then Debug.Print "nicht gefunden" Else Debug.Print r
End Sub

Das Denkmodell: VLOOKUP ist eine Tabellenfunktion zu Besuch in VBA

VLOOKUP wurde gebaut, um in einer Zelle zu leben. Rufst du es aus VBA auf, leihst du dir eine Tabellenfunktion — und dieses Darlehen hat zwei Bedingungen, vor denen dich keine Zellformel je warnt: wie es sich bei einem fehlenden Wert verhält und wie schlecht es skaliert, wenn du es in einer Schleife aufrufst. Behalte dieses Bild, und beide Fallen dieses Artikels hören auf, Überraschungen zu sein.

Die erste Frage lautet also nicht „wie schreibe ich VLOOKUP in VBA?" — das ist der einfache Teil, eine Zeile. Die echte Frage ist welches VLOOKUP, denn VBA bietet zwei davon mit entgegengesetztem Fehlerverhalten, und dann ob du es überhaupt aufrufen solltest, sobald eine Schleife im Spiel ist.

Die eine Regel: WorksheetFunction.VLookup stürzt ab, Application.VLookup liefert einen Fehler

Das ist die Unterscheidung hinter dem meistgemeldeten VBA-VLOOKUP-Problem — dem berüchtigten Laufzeitfehler 1004 „VLookup-Eigenschaft konnte nicht abgerufen werden":

Über WorksheetFunction aufgerufen, löst ein fehlender Wert einen Laufzeitfehler aus, der dein Makro stoppt. Direkt über Application aufgerufen, kommt ein fehlender Wert als Fehlerwert zurück (etwa Fehler 2042), den du mit IsError prüfst — kein Absturz.

Keiner ist „richtig"; es sind Werkzeuge für zwei Situationen. Nutze WorksheetFunction, wenn ein fehlender Wert wirklich ein Bug ist und das Makro stoppen soll (oder du es in On Error gehüllt hast). Nutze Application.VLookup, wenn „nicht gefunden" ein normales, erwartetes Ergebnis ist, das du sauber behandeln willst.

' (A) WorksheetFunction + On Error — bei echtem Fehler stoppen oder verzweigen
Dim price As Double
On Error Resume Next
price = Application.WorksheetFunction.VLookup(code, tbl, 2, False)
If Err.Number <> 0 Then
    price = 0                       ' Standardwert, und weiter
    Err.Clear
End If
On Error GoTo 0

' (B) Application.VLookup + IsError — das sauberere Muster für "könnte fehlen"
Dim res As Variant
res = Application.VLookup(code, tbl, 2, False)
If IsError(res) Then price = 0 Else price = res

Muster (B) liest sich besser und zweckentfremdet die Fehlerbehandlung nicht für gewöhnlichen Kontrollfluss — greife also zu Application.VLookup, wann immer ein Fehlschlag erwartet wird. (Siehe VBA On Error, warum verstreutes On Error Resume Next eine Belastung ist.)

Die Falle, die dich wirklich kostet: VLOOKUP in einer Schleife ist O(n²)

Hier ist die, welche die Tutorials auslassen. Ein einzelnes VLOOKUP durchsucht die Nachschlagespalte, bis es einen Treffer findet. Steck diesen Aufruf in eine Schleife über deine Zeilen, und du durchsuchst die ganze Spalte bei jeder Iteration erneut:

' ⚠ LANGSAM — 10.000 Zeilen, jede mit VLOOKUP gegen eine 10.000-Zeilen-Tabelle.
' Bis zu 100.000.000 Zellvergleiche, und es liest das Blatt bei jedem Durchgang neu.
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

Das ist quadratischer Aufwand, und bei echten Daten wird aus „warum hängt mein Makro?" eine Kaffeepause von Minuten. Die Zahl der Vergleiche wächst mit dem Quadrat der Zeilenzahl — doppelte Zeilen, vierfache Wartezeit.

Die Lösung ist, das wiederholte Suchen zu beenden. Lies die Nachschlagetabelle einmal in ein Dictionary — das ist eine Hash-Tabelle, jeder Zugriff ist also O(1) statt O(n) — und die ganze Aufgabe wird linear:

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

    ' Beide Spalten in EINEM Zug in den Speicher lesen — siehe VBA Array, warum das zählt
    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)        ' Schlüssel = Spalte A, Wert = Spalte B
    Next i

    ' Schlüssel lesen, im Speicher auflösen, in EINEM Zug zurückschreiben
    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) = "k. A."
    Next i
    Sheet1.Range("C2").Resize(n, 1).Value = out      ' ein Schreibvorgang, nicht 10.000
End Sub

Gleiches Ergebnis, aber es liest das Blatt zweimal statt 20.000-mal, und der Abgleich ist hash-basiert. Bei 10.000 Zeilen ist das der Unterschied zwischen „sofort" und „hol dir einen Kaffee". (Die andere Hälfte des Tempogewinns — Arrays in einem Zug lesen und schreiben statt Zelle für Zelle — ist eine eigene Gewohnheit, die sich lohnt.)

Wann was

Die Meinung: Wer VLOOKUP in einer Schleife aufruft, will eigentlich ein Dictionary

Die Linie, hinter der ich stehe: VLOOKUP gehört in Zellen, nicht in Schleifen. Eine Tabellenfunktion einmal aus VBA aufzurufen, um einen einzelnen Wert zu holen, ist völlig in Ordnung. In dem Moment, in dem sie in einer For-Schleife über deine Daten steckt, hast du eine für einen Lookup gebaute Operation gebeten, eine Spalte tausendfach neu zu durchsuchen — und du wirst es spüren.

Der Reflex „nimm einfach VLOOKUP, das kenne ich" ist genau das, was Makros langsam hält. Der professionelle Zug ist, die Form des Problems zu erkennen: viele Lookups gegen eine Tabelle ist das, wofür ein Dictionary da ist. Bau die Tabelle einmal im Speicher, löse jede Zeile dagegen auf, schreib die Antworten in einem Zug zurück. Dein Makro geht von quadratisch zu linear, und der Code dafür ist kürzer als die Fehlerbehandlung, die du sonst um ein schleifendes VLOOKUP wickeln müsstest.

Häufige VBA-VLOOKUP-Fehler (und die Lösung)

Wenn die Lookups sich stapeln — beschreibe stattdessen den Abgleich

Du wolltest keine Debatte über WorksheetFunction gegen Application. Du wolltest „gleiche den Export dieses Monats mit meiner Preisliste ab und markiere die Codes, die es nicht gibt". Bis du das richtige VLOOKUP gewählt, die Fehlschläge abgesichert und die Schleife als Dictionary neu geschrieben hast, damit sie in diesem Jahrzehnt fertig wird, ist die Mechanik das Makro. ExcelMaster Agent lässt dich den Abgleich in klarem Deutsch beschreiben — „schlage jeden Code in Spalte A in der Preisliste nach, schreib den Preis in C und liste die ohne Treffer" — und schreibt Python, das alles in einem Durchgang erledigt und vorher deine Arbeitsmappe sichert. Kostenlos testen →

Verwandte Anleitungen

• VBA Dictionary — O(1)-Lookups im Speicher
• VBA Array — einen Bereich in einem Zug lesen und schreiben
• VBA Copy Paste — die Zwischenablage überspringen für schnellere Makros
• VBA On Error — Resume Next vs GoTo & warum Makros Bugs verstecken
• VBA For-Schleife in Excel — 8 praxisnahe Beispiele

FAQ

Wie verwende ich VLOOKUP in VBA? Rufe es über das Application-Objekt auf: Application.WorksheetFunction.VLookup(Suchwert, Tabellenbereich, Spaltenindex, False). Übergib immer False als viertes Argument für eine exakte Übereinstimmung. Könnte der Wert fehlen, nutze stattdessen Application.VLookup (ohne WorksheetFunction) und prüfe das Ergebnis mit IsError, damit ein Fehlschlag einen Fehlerwert liefert, statt das Makro abstürzen zu lassen.

Warum bringt VBA-VLOOKUP den Laufzeitfehler 1004? Weil du es über WorksheetFunction aufgerufen hast und der Wert nicht gefunden wurde — dieser Weg löst einen Fehler aus, statt #NV zu liefern. Hülle den Aufruf entweder in On Error Resume Next und prüfe Err.Number, oder nutze Application.VLookup, das einen prüfbaren Fehlerwert zurückgibt, den du mit If IsError(Ergebnis) behandelst.

Was ist der Unterschied zwischen Application.VLookup und WorksheetFunction.VLookup? Sie führen denselben Lookup aus, scheitern aber unterschiedlich. WorksheetFunction.VLookup löst bei keinem Treffer einen Laufzeitfehler aus; Application.VLookup liefert einen Fehlerwert (etwa Fehler 2042), den du mit IsError prüfst. Nutze WorksheetFunction, wenn ein Fehlschlag ein echter Bug ist, und Application, wenn „nicht gefunden" erwartet wird.

Ist VLOOKUP in VBA langsam? Ein einzelner Aufruf ist in Ordnung. Der Aufruf in einer Schleife über viele Zeilen ist langsam, weil jeder Aufruf die Nachschlagespalte neu durchsucht und der Aufwand mit dem Quadrat der Zeilenzahl wächst. Lade für viele Lookups die Tabelle einmal in ein Scripting.Dictionary und löse jede Zeile in O(1) auf — bei großen Daten weit schneller.

Kann VBA-VLOOKUP einen Wert in einer anderen Tabelle nachschlagen? Ja — qualifiziere den Tabellenbereich mit dem Blatt: Application.WorksheetFunction.VLookup(Schlüssel, Worksheets("Preise").Range("A:B"), 2, False). Nutze eine voll qualifizierte Worksheets("Name").Range(...)-Referenz, damit der Lookup nicht versehentlich das aktive Blatt liest.