VBA – VLOOKUP / XLOOKUP in einem anderen Blatt oder anderer Mappe

In diesem Artikel zeigen wir Ihnen, wie Sie die Funktionen VLOOKUP und XLOOPUP verwenden können, um Werte in anderen Blättern oder Arbeitsmappen in VBA nachzuschlagen.

VLOOKUP und XLOOKUP (Deutsch: SVERWEIS und XVERWEIS) sind in Excel nützliche Funktionen, mit denen wir nach einem passenden Wert in einem Bereich suchen und einen entsprechenden Wert aus einer anderen Spalte zurückgeben können. Diese Funktionen können direkt in einer Formel in Excel verwendet werden oder in der VBA-Programmierung mit der WorksheetFunction-Methode eingesetzt werden. Mit diesen Funktionen können Sie Werte nachschlagen, die in einem anderen Blatt als dem, in dem das Ergebnis erzeugt werden soll, oder sogar in einer anderen Arbeitsmappe enthalten sind.

VLOOKUP und XLOOKUP aus einem anderen Blatt

VLOOKUP aus einem anderen Blatt

Um VLOOKUP in einem anderen Blatt als den Nachschlagedaten zu verwenden, können Sie den folgenden Code eingeben:

Sub PreisNachschlagen()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("Produktliste").Range("B2:C6"), 2, False)
End Sub

ODER

Sub PreisNachschlagen() 
   ActiveCell = Application.VLookup(Range("B3"), Sheets("Produktliste").Range("B2:C6"), 2, False) 
End Sub

Dies führt zu Folgendem:

Der Einfachheit halber könnten wir auch Variablen in unserem Code verwenden.

Sub PreisNachschlagen()
   Dim ws As Worksheet
   Dim bereich As Range
   Dim strProdukt As String
   strProdukt = Range("B3")
   Set ws = Sheets(1)
   Set bereich = ws.Range("B2:C6")
   ActiveCell = Application.WorksheetFunction.VLookup(strProdukt, bereich, 2, False)
End Sub

Dies würde das gleiche Ergebnis liefern.

Wenn wir einen Wert suchen, der nicht existiert, sollten wir die Funktion Application.VLookup verwenden, um #N/A an die Zelle zurückzugeben und nicht die Funktion Application.WorksheetFunction.VLookup, die zu einem VBA-Fehler führen würde.

Wenn wir nicht den Wert, sondern die Formel an die Zielzelle zurückgeben wollen, müssen wir den folgenden Code eingeben.

Sub PreisNachschlagen()
   ActiveCell = "=VLOOKUP(RC[-1],Produktliste!RC[-1]:R[3]C,2,FALSE)"
End Sub

Dabei verwendet die Formel die R1C1-Syntax (Zeilen und Spalten) anstelle der A1-Syntax (Bereich).

Dies würde zu folgendem Ergebnis führen:

 

XLOOKUP aus einem anderen Blatt

Die Verwendung von XLOOKUP, um Daten in einem anderen Blatt nachzuschlagen, ist fast dasselbe. Denken Sie daran, dass XLOOKUP mehrere Bereiche und nicht nur eine bestimmte Spalte durchsucht, um den Wert zu finden.

Sub PreisNachschlagen()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Sheets("Produktliste").Range("B2:B6"), Sheets("ProduktListe").Range("C2:C6"))
End Sub

oder mit Variablen:

Sub PreisNachschlagen()
  Dim ws As Worksheet
  Dim strProdukt As String
  Dim rngProdukt As Range
  Dim rngPreis As Range
  strProdukt = Range("B3")
  Set ws = Sheets("Produktliste")
  Set rngProdukt = ws.Range("B2:B6")
  Set rngPreis = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProdukt, rngProdukt, rngPreis)
End Sub

Das Ergebnis eines dieser Beispiele wäre dann:

Um eine Formel mit XLOOKUP zurückzugeben, müssten wir wieder die Zeilen-/Spaltensyntax (R1C1) und nicht die Bereichssyntax (A1) verwenden.

Sub FormelEingeben()
  ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-1],Produktliste!RC[-1]:R[3]C[-1],Produktliste!RC:R[3]C)"
End Sub

Das Ergebnis wäre dann:

Einer der größten Vorteile der Verwendung von XLOOKUP gegenüber VLOOKUP ist die Möglichkeit, einen Bereich von Spalten zu durchsuchen und den Wert in jeder Spalte zurückzugeben.

Betrachten wir das folgende Beispiel:

Wir haben die Formel in Zelle C3 erstellt, wo sie die Werte im Bereich C2:E6 nachschlägt. Da es sich um mehrere Spalten handelt, werden die Spalten D und E automatisch mit den entsprechenden Ergebnissen gefüllt. Die Formel läuft in die Spalten D und E über, ohne dass wir STRG+SHIFT für eine Array-Formel verwenden müssen. Diese Fähigkeit der Formel, über die Spalten überzulaufen, ist eine der neuen Ergänzungen für Excel 365.

Um dies mit VBA-Code nachzubilden, können wir Folgendes in unser Makro eingeben:

Sub PreisNachschlagen()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],Produktliste!RC[-1]:R[3]C[-1],Produktliste!RC:R[3]C[2])"
End Sub

Dabei verwendet die Formel die R1C1-Syntax (Zeilen und Spalten) anstelle der A1-Syntax (Bereich). Dies führt dazu, dass die Formeln in Excel wie in der obigen Grafik dargestellt eingegeben werden.

Sie können nicht mehrere Spalten nachschlagen, wenn Sie die WorksheetFunction-Methode verwenden.

VLOOKUP und XLOOKUP aus einer anderen Arbeitsmappe

Das Arbeiten mit einer anderen Arbeitsmappe anstelle eines anderen Arbeitsblattes ist weitgehend identisch.

VLOOKUP aus einer anderen Arbeitsmappe

Betrachten Sie das folgende Beispiel:

Um dies mit VBA-Code zu replizieren, würden wir das folgende Makro erstellen:

Sub PreisNachschlagen()    
  ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Workbooks("Produkte.xlsm").Sheets("Produktliste").Range("B2:C6"), 2, False)
End Sub

die Datei Produkte.xlsm müsste in Excel geöffnet sein, damit diese Suche funktioniert.

Wir könnten auch Variablen verwenden:

Sub PreisNachschlagen()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim bereich As Range
  Dim strProdukt As String
  Set wb = Workbooks("Produkte.xlsm")
  Set ws = wb.Sheets("ProduktListe")
  Set bereich = ws.Range("B2:C6")
  strProdukt = Range("B3")
  ActiveCell = Application.WorksheetFunction.VLookup(strProdukt, bereich, 2, False)
End Sub

Das Ergebnis eines dieser Beispiele wäre dann:

Wenn wir wollen, dass die eigentliche Formel in der Zelle erscheint und nicht der Wert, dann würde unser VBA-Code den Namen der Arbeitsmappe sowie den Namen oder die Nummer des Blattes enthalten.

Zum Beispiel:

Sub PreisNachschlagen()
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Produkte.xlsm]Produktliste!R3C2:R6C3,2,FALSE)"
End Sub

XLOOKUP aus einer anderen Arbeitsmappe

Die Verknüpfung mit einer anderen Datei mit Hilfe von XLOOKUP anstelle von VLOOKUP erfolgt nach demselben Muster.

Um den Wert an Excel zurückzugeben:

Sub PreisNachschlagen()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Workbooks("Produkte.xlsm").Sheets("ProduktListe").Range("B2:B6"), Workbooks("Produkte.xlsm").Sheets("ProduktListe").Range("C2:C6"))
End Sub

oder mit Variablen:

Sub PreisNachschlagen()
  Dim wb as Workbook
  Dim ws As Worksheet
  Dim strProdukt As String
  Dim rngProdukt As Range
  Dim rngPreis As Range
  strProdukt = Range("B3")
  Set wb = Workbooks("Produkte.xlsm")
  Set ws = wb.Sheets(Produktliste)
  Set rngProdukt = ws.Range("B2:B6")
  Set rngPreis = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProdukt, rngProdukt, rngPreis)
End Sub

Das Ergebnis eines dieser Beispiele wäre dann:

 

Um nicht den Wert, sondern die Formel an Excel zurückzugeben, können wir unser Makro so ändern, dass es sowohl den Namen der Arbeitsmappe als auch den Blattnamen oder die Blattnummer enthält.

Sub PreisNachschlagen()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],[Produkte.xlsm]ProduktListe!RC[-1]:R[3]C[-1],[Produkte.xlsm]ProduktListe!RC:R[3]C[2])"
End Sub