Wie sucht man Werte in einer anderen Arbeitsmappe?

Die Konfiguration von Nachschlagereferenzen mit VLOOKUP kann verwirrend sein, insbesondere wenn sich Pfad, Name oder Arbeitsblatt der Quelldatei häufig ändern. In solchen Fällen bietet die Automatisierung des Nachschlagevorgangs mittels VBA eine effizientere Lösung, da Sie Werte auch dann abrufen können, wenn die Quellarbeitsmappe geschlossen ist, und die Bereichsauswahl sowie die Rückgabe der Daten automatisiert werden.

Führen Sie diese Schritte aus, um VBA für arbeitsmappenübergreifende Nachschlagevorgänge zu verwenden:

1. Drücken Sie gleichzeitig Alt + F11, um das Microsoft Visual Basic for Applications-Editorfenster zu öffnen.

2.Klicken Sie im VBA-Editor auf Einfügen>Modul, und kopieren Sie anschließend den folgenden Code in das Modulfenster:

VBA: Vlookup-Daten und Rückgabewert aus einer anderen geschlossenen Arbeitsmappe

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Wichtige Hinweise:

• Der Code gibt den übereinstimmenden Wert in einer Spalte zurück, die um 2 Spalten gegenüber dem Nachschlagebereich versetzt ist. Wenn Sie beispielsweise Spalte B auswählen, erscheinen die Ergebnisse in Spalte D.
• Wenn das Ergebnis in einer anderen Spalte erscheinen soll, ändern Sie die Zahl 2 in xRg.Offset(0,2).Formulain einen anderen Wert (z. B.)1 für die nächste Spalte, 3 für die dritte Spalte rechts).
• Wählen Sie beim Aufforderungsdialog die richtige Arbeitsmappe und das richtige Arbeitsblatt aus; der Code verwendet stets das erste Arbeitsblatt der ausgewählten Datei. Passen Sie den Code an, falls Ihr Quellarbeitsblatt nicht das erste ist.
• Speichern Sie Ihre Datei stets, bevor Sie unbekannte Makros ausführen. Makros können nach der Ausführung nicht rückgängig gemacht werden.

3. Führen Sie das Makro aus, indem Sie die F5-Taste drücken oder auf die Schaltfläche Ausführen klicken. Ein Dialogfeld mit dem Titel „Kutools für Excel“ fordert Sie auf, den Zellbereich auszuwählen, dessen Werte Sie nachschlagen möchten.

4. Nachdem Sie Ihren Bereich ausgewählt haben, klicken Sie auf OK. Kurz darauf erscheint ein weiteres Dialogfeld, in dem Sie aufgefordert werden, die Quellarbeitsmappe auszuwählen (auch wenn sie geschlossen ist). Navigieren Sie zur entsprechenden Datei, wählen Sie sie aus und klicken Sie auf Öffnen, um Ihre Auswahl zu bestätigen.

Sobald das Makro abgeschlossen ist, werden die entsprechenden Werte aus der Quellarbeitsmappe in die Zielspalte Ihrer Aktuelles Arbeitsblatt eingefügt. Falls einige Werte fehlen, prüfen Sie, ob die Suchwertbereich in der Aktuelles Arbeitsblatt exakt mit denen in der Quelldaten übereinstimmen (Groß-/Kleinschreibung und führende Leerzeichen am Ende sind für eine exakte Übereinstimmung relevant).

Vorteile: Funktioniert mit geschlossenen Arbeitsmappen, vermeidet fest kodierte Dateipfade in Formeln und bietet Flexibilität bei der Auswahl der Quelldateien zur Laufzeit.
Hinweise: Makros müssen aktiviert sein. VBA funktioniert möglicherweise nicht auf geschützten Blättern oder mit Nicht-Excel-Dateien. Speichern Sie Ihre Arbeitsmappe im Format mit Makrounterstützung (*.xlsm), wenn Sie diese Methode regelmäßig nutzen möchten.

Falls Fehler auftreten, vergewissern Sie sich, dass weder im Arbeitsblatt- noch im Dateinamen Tippfehler vorliegen, dass der ausgewählte Bereich geeignet ist und dass der Dateipfad erreichbar ist. Zur Fehlersuche empfiehlt es sich, den Code im VBA-Editor zeilenweise schrittweise auszuführen.

Neben dem klassischen SVERWEIS-Ansatz und VBA gibt es alternative Methoden, um arbeitsmappenübergreifende Nachschlagevorgänge in Excel durchzuführen. Diese können sich in bestimmten Situationen als vorteilhafter erweisen – beispielsweise bei einer abweichenden Datenstruktur, wenn Sie Formeln Makros vorziehen oder mehr Flexibilität benötigen, etwa für eine Suche nach links oder die Verwendung mehrerer Suchkriterien.

Verwendung der INDEX- und VERGLEICH-Funktionen über Arbeitsmappen hinweg

Durch die Kombination von INDEX und VERGLEICH können Sie Werte in jede Richtung – links, rechts, oben oder unten – in einer anderen Arbeitsmappe nachschlagen. Dies ist besonders nützlich, wenn die Spalte, aus der Sie Daten abrufen möchten, nicht rechts von der Suchspalte liegt (eine Einschränkung von VLOOKUP).

Szenario: Angenommen, Sie möchten einen Preis aus einer anderen geöffneten Arbeitsmappe abrufen, obwohl der Fruchtname möglicherweise nicht in der ersten Spalte steht.

1. Wählen Sie in Ihrer Zieldatei die Zelle aus, in der das Ergebnis angezeigt werden soll (z. B. C2), und geben Sie die folgende Formel ein (passen Sie Arbeitsmappe, Arbeitsblatt und Bereich entsprechend an):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Drücken Sie Enter. Kopieren oder füllen Sie die Formel anschließend bei Bedarf in andere Zeilen, indem Sie den Ausfüllknauf ziehen.

Parametererklärung:

• [Price.xlsx]Sheet1!$B$1:$B$24: Der Bereich, in dem die Preise gespeichert sind.
• B2: Der Fruchtname, nach dem gesucht werden soll.
• [Price.xlsx]Sheet1!$A$1:$A$24: Der Bereich, in dem Ihr Suchwert gesucht wird.
• Die 0am Ende stellt eine exakte Übereinstimmung sicher.

Stärken: Funktioniert sowohl für Links- als auch Rechtsnachschläge und unterstützt flexiblere Layouts.
Tipps: Vermeiden Sie es, Quelldateien zu verschieben oder umzubenennen, ohne Ihre Formel zu aktualisieren.

Verwendung von XLOOKUP für arbeitsmappenübergreifende Nachschlagevorgänge (Excel 365 und neuer)

Wenn Sie Excel 365 oder Excel 2021 verwenden, bietet die neue XLOOKUP-Funktion noch mehr Flexibilität. Sie ermöglicht das einfache Finden exakter Übereinstimmungen, unterstützt Links-Nachschläge und verarbeitet fehlende Werte automatisch, ohne Fehlermeldungen zu erzeugen.

So verwenden Sie sie:
Geben Sie in der Zelle, in der das Ergebnis erscheinen soll, Folgendes ein:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Drücken Sie Enter, und kopieren Sie die Formel bei Bedarf. Dabei können Sie „Nicht gefunden“ durch beliebigen eigenen Text ersetzen, der angezeigt werden soll, falls ein Nachschlagewert nicht gefunden wird.

Vorteile: Flexibler als VLOOKUP und einfacher zu verwalten – zudem vermeidet es viele häufige Fehler älterer Formeln. XLOOKUP ist jedoch nur in neueren Excel-Versionen verfügbar.

Für komplexe Anforderungen wie Mehrfachkriterienabgleich, Suche in zusammengeführten Blättern oder zur Vermeidung von Leistungsproblemen in sehr großen Dateien sollten Sie erwägen, Quelldaten in strukturierten Tabellen zu organisieren oder das Power Query-Tool von Excel zu verwenden, um effizient zwischen mehreren Arbeitsmappen zu Daten verknüpfen.