Wie führt man einen SVERWEIS durch und verknüpft dabei mehrere zugehörige Werte in Excel?
Beim Einsatz von SVERWEIS in Excel gibt die Funktion normalerweise nur den ersten übereinstimmenden Wert zurück, den sie für ein bestimmtes Suchkriterium findet. Es gibt jedoch viele gängige Szenarien, in denen Sie alle passenden Werte zu einem bestimmten Schlüssel abrufen und kombinieren müssen – beispielsweise alle Schüler einer Klasse oder alle Produkte einer bestimmten Kategorie auflisten möchten. Da die Standard-SVERWEIS-Funktion diesbezüglich eingeschränkt ist, fragen Sie sich möglicherweise, wie Sie sowohl suchen als auch mehrere zugehörige Ergebnisse in einer einzigen Zelle verketten können. Im Folgenden stellen wir mehrere praktische und effiziente Methoden vor, um diese Aufgabe je nach verwendeter Excel-Version und Ihren persönlichen Vorlieben zu bewältigen.
VLOOKUP und Verketten mehrerer zugehöriger Werte in Excel
SVERWEIS und Verketten mehrerer zugehöriger Werte mithilfe der Funktionen TEXTVERKETTEN und FILTER
Wenn Sie Excel 365 oder Excel 2021 nutzen, bietet die Kombination aus TEXTVERKETTEN und FILTER eine effiziente, formelbasierte Lösung, um mit SVERWEIS alle übereinstimmenden Werte zu finden und zu verketten. Diese Methode eignet sich ideal für dynamische und sich aktualisierende Datensätze – das Ergebnis passt sich automatisch an, sobald sich die Quelldaten ändern. Verwenden Sie diese Lösung vorzugsweise dann, wenn Ihre Excel-Version die FILTER-Funktion unterstützt, die exklusiv in neueren Office-Versionen verfügbar ist.
Geben Sie in der Zielszelle die folgende Formel ein und ziehen Sie sie nach unten, um sie auf weitere Zeilen anzuwenden. Alle zugehörigen übereinstimmenden Werte werden extrahiert und in einer Zelle kombiniert. Siehe Screenshot:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, „")Dieser Teil der Formel prüft jeden Wert im Bereich $A$2:$A$16 – stimmt er mit dem Wert in D2 überein, wird der zugehörige Wert aus $B$2:$B$16 in das Ergebnisarray übernommen.
- $B$2:$B$16Der Bereich, aus dem die übereinstimmenden Werte abgerufen werden.
- $A$2:$A$16=D2Die Bedingung für die Auswahl der Werte: Es werden ausschließlich jene Zeilen berücksichtigt, in denen der Bereich $A$2:$A$16 dem Inhalt von Zelle D2 entspricht.
- TEXTVERKETTEN(", "; WAHR; ...): Diese Funktion nimmt die Ausgabe der FILTER-Funktion (ein Array mit Übereinstimmungen) und verkettet sie zu einer einzigen Textzeichenfolge, getrennt durch das angegebene Trennzeichen (Komma und Leerzeichen), wobei leere Einträge automatisch ignoriert werden.
- ",": Legt Komma und Leerzeichen als Trennzeichen fest. Sie können dieses Symbol bei Bedarf anpassen – zum Beispiel durch Semikolons oder Zeilenumbrüche ersetzen.
- WAHR: Stellt sicher, dass leere Zellen beim Zusammenfügen ignoriert werden – für ein sauber formatiertes Ergebnis.
Besonderer Hinweis: Diese Methode erfordert Excel 365 oder 2021 und funktioniert nicht in älteren Versionen (z. B. Excel 2019, 2016 oder früher). Prüfen Sie daher unbedingt Ihre Excel-Version, bevor Sie diese Methode anwenden.
Tipp: Sobald sich Ihr Suchwert (z. B. D2) ändert oder zusätzliche übereinstimmende Elemente zum Datenbereich hinzugefügt werden, aktualisiert sich das Ergebnis automatisch – ganz ohne weitere Schritte.
Mögliche Einschränkungen: Bei sehr großen Datensätzen kann die Berechnungszeit der Formel ansteigen. Außerdem sollten Benutzer sicherstellen, dass sich im Such- oder Ergebnisbereich keine zusammengeführten Zellen befinden, da diese zu Formelfehlern führen können.
SVERWEIS und Verketten mehrerer zugehöriger Werte mit Kutools für Excel
Wenn Ihnen integrierte Formellösungen zu kompliziert erscheinen oder Ihre Excel-Version erweiterte Funktionen wie TEXTVERKETTEN und FILTER nicht unterstützt, bietet Kutools für Excel eine benutzerfreundliche grafische Alternative. Mit der „Ein-zu-viele-Suche“-Funktion von Kutools suchen und verketten Sie mehrere übereinstimmende Ergebnisse mit nur wenigen Klicks – ideal für Anfänger ebenso wie für erfahrene Anwender. Dank Kutools entfällt das mühsame Schreiben komplexer Formeln oder Codes, was besonders bei großen oder variablen Datensätzen von Vorteil ist, die wiederholt Such- und Aggregationsvorgänge erfordern.
Nach der Installation von Kutools für Excel gehen Sie wie folgt vor:
Klicken Sie auf Kutools > Erweiterte SVERWEIS > Ein-zu-viele-Suche (mehrere Ergebnisse zurückgeben), um den Einrichtungsdialog zu öffnen. In diesem Dialog können Sie Ihre Such- und Ausgabeeinstellungen in wenigen Schritten schnell konfigurieren:
- Wählen Sie Ihre Zielzellen für die verketteten Ergebnisse sowie die Zellen mit den Werten aus, nach denen gesucht werden soll;
- Geben Sie den Tabellenbereich an, der sowohl die Suchschlüssel- als auch die Ergebnisspalten enthält;
- Legen Sie fest, welche Spalte die Suchschlüssel (Schlüsselspalte) und welche Spalte die zu verknüpfenden Werte (Rückgabespalte) enthält;
- Klicken Sie auf „OK“, um Ihre Einstellungen zu bestätigen und die Datenverarbeitung zu starten.
Ergebnis: Kutools zeigt nun alle übereinstimmenden und verketteten Werte in der von Ihnen ausgewählten Zelle an. Siehe Screenshot:
Diese Methode wird besonders empfohlen, wenn Sie lieber über die Excel-Oberfläche arbeiten möchten, ohne komplexe Formeln oder Code zu verwenden. Sie reduziert zudem das Risiko von Formelfehlern und steigert die Produktivität bei wiederkehrenden Such- und Verkettungsaufgaben.
SVERWEIS und Verketten mehrerer zugehöriger Werte mit einer benutzerdefinierten Funktion
Für Benutzer, die mit VBA (Visual Basic for Applications) vertraut sind oder ältere Excel-Versionen nutzen, die keine dynamischen Arrays oder die FILTER-Funktion unterstützen, lässt sich eine benutzerdefinierte Funktion (UDF) erstellen, um mehrere Ergebnisse flexibel zu verketten. Diese Methode ist mit allen Excel-Versionen kompatibel und kann individuell an spezifische Trennzeichen oder Bedingungen angepasst werden.
1. Halten Sie die ALT + F11-Tasten gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modulfenster ein.
VBA-Code: SVERWEIS und Verketten mehrerer übereinstimmender Werte in einer Zelle
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Speichern und schließen Sie den VBA-Editor. Kehren Sie zu Ihrem Arbeitsblatt zurück und verwenden Sie diese UDF, indem Sie die Formel: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in eine leere Zelle eingeben, in der das Ergebnis erscheinen soll. Ziehen Sie das Ausfüllkästchen nach unten, um die Formel bei Bedarf auf weitere Zellen zu übertragen. Alle zugehörigen Werte zum jeweiligen Suchbegriff werden in einer einzigen Zelle ausgegeben – getrennt durch Komma und Leerzeichen. Siehe Screenshot:
- D2: Der Suchwert, der in Ihrem Datensatz abgeglichen werden soll (Suchwert).
- A2:A16: Der Bereich, in dem die Funktion nach dem Suchwert sucht (Suchbereich).
- B2:B16: Der Bereich, der die Werte enthält, die verkettet werden sollen, sobald der Suchwert übereinstimmt (Rückgabebereich).
SVERWEIS und Verketten mehrerer zugehöriger Werte mit VBA-Code
Für Szenarien mit häufigem Einsatz oder wenn Sie benutzerdefinierte Funktionen in Arbeitsblattzellen vermeiden möchten, bietet sich ein vorgefertigtes VBA-Makro an, um Ergebnisse direkt zu verketten. Diese Methode eignet sich besonders gut in gemeinsam genutzten Umgebungen, in denen nicht alle Benutzer dieselbe Excel-Version oder dieselben Add-Ins verwenden.
1. Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul, und fügen Sie anschließend diesen Code in das Modul ein:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Klicken Sie auf die Schaltfläche 
, um das Makro auszuführen. Die Eingabefelder fordern Sie auf, Ihren Datenbereich, Ihren Suchbereich und Ihren Ergebnisbereich auszuwählen. Das verkettete Ergebnis erscheint anschließend direkt in den ausgewählten Ausgabezellen.
Dieser Makroansatz ist besonders nützlich, wenn Sie häufig mehrfache Verkettungssuchen mit unterschiedlichen Werten durchführen, da er verhindert, dass das Arbeitsblatt mit UDF-Aufrufen überladen wird.
Sie können das Trennzeichen im Code bei Bedarf mühelos anpassen und das Makro so erweitern, dass die Ergebnisse je nach Workflow entweder in eine Zelle oder in eine Datei ausgegeben werden.
Das Verketten mehrerer entsprechender Werte in Excel lässt sich auf verschiedene Weise umsetzen – jede Methode bietet je nach Situation ganz spezifische Vorteile. Ob Sie dynamische Arrayformeln, Add-Ins wie Kutools für Excel oder VBA-basierte Lösungen nutzen: Sie stärken damit Ihre Fähigkeit, gruppierte Daten effizient zu analysieren und übersichtlich darzustellen. Prüfen Sie daher abhängig von Größe und Komplexität Ihres Datensatzes, welcher Ansatz für Sie oder Ihr Team optimale Leistung und einfache Wartbarkeit gewährleistet. Achten Sie im täglichen Einsatz stets auf Datenkonsistenz, vermeiden Sie zusammengeführte Zellen und überprüfen Sie regelmäßig Ihre Referenzbereiche, um bestmögliche Ergebnisse zu erzielen. Sollten bei Formelberechnungen Fehler auftreten, stellen Sie sicher, dass Ihre Bereiche mit den zugrundeliegenden Daten übereinstimmen und dass Sie die für Ihre Excel-Version korrekte Eingabemethode für Formeln verwenden.
Für fortgeschrittene Excel-Techniken und eine Vielzahl praktischer Anleitungen besuchen Sie unsere umfangreiche Tutorial-Bibliothek.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins