Wie kann man in Excel mit SVERWEIS suchen und mehrere entsprechende Werte verketten?

Wenn Sie SVERWEIS in Excel verwenden, gibt es normalerweise den ersten übereinstimmenden Wert aus einem Datensatz zurück. Es gibt jedoch Szenarien, in denen Sie möglicherweise mehrere entsprechende Werte zurückgeben und verketten müssen (z. B. alle Namen, die einer bestimmten Klasse zugeordnet sind). Da SVERWEIS dies allein nicht leisten kann, werden wir fortgeschrittene Techniken untersuchen, um das gewünschte Ergebnis zu erzielen.

SVERWEIS und Verkettung mehrerer entsprechender Werte in Excel

• Verwendung von TEXTVERBINDEN und FILTER-Funktionen (Excel 365/2021)
• Verwendung von Kutools für Excel
• Verwendung einer benutzerdefinierten Funktion

SVERWEIS und Verkettung mehrerer entsprechender Werte mit TEXTVERBINDEN und FILTER-Funktionen

Wenn Sie Excel 365 oder Excel 2021 verwenden, bietet die Kombination der Funktionen TEXTVERBINDEN und FILTER eine leistungsstarke und effiziente Möglichkeit, mehrere entsprechende Werte zu verketten.

Geben Sie in der Zielzelle die folgende Formel ein und ziehen Sie die Formel dann in andere Zellen, um diese zu füllen. Alle übereinstimmenden Werte werden extrahiert und in einer Zelle zusammengefasst. Siehe Screenshot:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

SVERWEIS und Verkettung mehrerer entsprechender Werte mit Kutools für Excel

Haben Sie genug von den Einschränkungen von Excels SVERWEIS bei der Arbeit mit mehreren entsprechenden Werten? Mit der Funktion „Ein-zu-viele-Suche“ von Kutools für Excel können Sie ganz einfach nach mehreren entsprechenden Werten suchen und diese mit nur wenigen Klicks verketten!

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Ein-zu-viele-Suche (gibt mehrere Ergebnisse zurück)“, um das Dialogfeld zu öffnen. Geben Sie im Dialogfeld die folgenden Vorgaben an:

1. Wählen Sie den „Platzierungsbereich der Liste“ und die „Bereich der zu durchsuchenden Werte“ separat in den Textfeldern aus;
2. Wählen Sie den Tabellenbereich aus, den Sie verwenden möchten;
3. Geben Sie die Schlüsselspalte und die Rückgabespalte separat aus den Dropdown-Menüs „Schlüsselspalte“ und „Rückgabespalte“ an;
4. Klicken Sie abschließend auf die Schaltfläche OK.
   

Ergebnis: Jetzt sind alle übereinstimmenden Werte in einer Zelle verkettet. Siehe Screenshot:

SVERWEIS und Verkettung mehrerer entsprechender Werte mit einer benutzerdefinierten Funktion

Wenn Sie sich mit VBA (Visual Basic for Applications) auskennen, können Sie eine benutzerdefinierte Funktion (UDF) erstellen, um mehrere entsprechende Werte zu verketten. Diese Methode funktioniert in allen Excel-Versionen.

1. Halten Sie die Tasten „ALT + F11“ 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 im Modulfenster ein.

VBA-Code: SVERWEIS und Verkettung 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 diesen Code, kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in eine leere Zelle, in der Sie das Ergebnis anzeigen möchten, und ziehen Sie den Ausfüllkursor nach unten, um die Formel in andere Zellen zu kopieren. Alle entsprechenden Werte basierend auf einem bestimmten Datensatz wurden in einer Zelle mit Komma- und Leerzeichentrennzeichen zurückgegeben. Siehe Screenshot:

Das Verketten mehrerer entsprechender Werte in Excel kann durch verschiedene Methoden erreicht werden, jede mit ihren eigenen Vorteilen. Ob Sie integrierte Funktionen wie TEXTVERBINDEN und FILTER, Drittanbietertools wie Kutools oder benutzerdefinierte VBA-Lösungen bevorzugen – entscheidend ist, die Methode zu wählen, die am besten zu Ihrer Excel-Version, Ihrem Kenntnisstand und Ihren spezifischen Anforderungen passt. Wenn Sie mehr über Excel-Tipps und -Tricks erfahren möchten, bietet unsere Website Tausende von Tutorials.