Wie gibt man mehrere Suchwerte, durch Kommas getrennt, in einer Zelle zurück?

Beim Arbeiten mit Daten in Excel kann es vorkommen, dass Sie mehrere übereinstimmende Werte für eine Suche zurückgeben und diese in einer einzigen Zelle anzeigen müssen, getrennt durch Kommas, Bindestriche usw. Während die integrierten Suchfunktionen von Excel wie „SVERWEIS“ und „INDEX-VERGLEICH“ hervorragend zur Ermittlung einer einzelnen Übereinstimmung geeignet sind, erfordert das Kombinieren mehrerer Ergebnisse in einer Zelle einen fortgeschritteneren Ansatz. Dieser Artikel stellt einige nützliche Methoden zur Lösung dieser Aufgabe in Excel vor.

Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, zurückgeben

• Verwendung von TEXTVERBINDEN und FILTER (Excel 365 oder Excel 2021 und höher)
• Verwendung von Kutools für Excel (Alle Excel-Versionen)
• Verwendung einer benutzerdefinierten Funktion

Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit den Funktionen TEXTVERBINDEN und FILTER zurückgeben

Wenn Sie Zugriff auf Excel 365 oder Excel 2021 und spätere Versionen haben, ist die Verwendung der dynamischen Arrayformel die einfachste Methode.

Wenden Sie bitte die folgende Formel in eine leere Zelle an, um das Ergebnis auszugeben, und drücken Sie dann die Eingabetaste, um das Ergebnis zu erhalten.

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

Ziehen Sie dann den Ausfüllkursor nach unten, um die Formel in andere Zellen zu übertragen, siehe Screenshot:

Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit Kutools für Excel zurückgeben

„Kutools für Excel“ ist ein Add-In, das entwickelt wurde, um komplexe Aufgaben in Excel zu vereinfachen. Seine erweiterte „Ein-zu-viele-Suche“-Funktion ermöglicht es Ihnen, mühelos alle übereinstimmenden Werte in eine einzige Zelle mit einem angegebenen Trennzeichen zu extrahieren – keine komplizierten Formeln oder VBA-Codierung erforderlich.

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 bitte die folgenden Operationen an:

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

Ergebnis:

Alle entsprechenden Zellen mit demselben Wert wurden in eine Zelle kombiniert, die durch Kommas getrennt sind, siehe Screenshot:

Tipp: Wenn Sie ein anderes Trennzeichen zum Trennen der Daten verwenden möchten, können Sie auf „Optionen“ klicken und Ihr gewünschtes Trennzeichen auswählen. Darüber hinaus können Sie weitere Operationen an den Ergebnissen durchführen, wie z.B. Summieren, Mittelwertbildung und mehr.

Mehrere Suchwerte in einer Zelle, durch Kommas getrennt, mit einer benutzerdefinierten Funktion zurückgeben

Wenn Sie nicht über Excel 365 oder Excel 2021 verfügen, wird Sie dieser Abschnitt Schritt für Schritt durch die Erstellung und Verwendung einer UDF führen, um mehrere Suchwerte in einer einzigen Zelle, durch Kommas getrennt, zurückzugeben.

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: Mehrere Suchwerte in eine durch Kommas getrennte Zelle zurückgeben

Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
    'Updateby Extendoffice
    Dim result As String
    Dim i As Long
    result = ""
    For i = 1 To lookupRange.Rows.Count
        If lookupRange.Cells(i, 1).Value = lookupValue Then
            result = result & returnRange.Cells(i, 1).Value & delimiter
        End If
    Next i
    If Len(result) > 0 Then
        JoinLookupValues = Left(result, Len(result) - Len(delimiter))
    Else
        JoinLookupValues = ""
    End If
End Function

3. Speichern Sie diesen Code und schließen Sie das Modulfenster, kehren Sie zu Ihrem Arbeitsblatt zurück und geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis zurückgeben möchten. Ziehen Sie dann den Ausfüllkursor nach unten, um diese Formel in andere Zellen zu übertragen, siehe Screenshot:

=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")

Zusammengefasst kann das Zurückgeben mehrerer Suchwerte in einer einzigen Zelle, durch Kommas getrennt, effizient mit verschiedenen Methoden erreicht werden, abhängig von Ihrer Excel-Version und Ihren Anforderungen. Durch die Auswahl der Methode, die am besten zu Ihren Anforderungen passt, können Sie Ihre Datenanalyse optimieren und die Produktivität steigern. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials.

Verwandte Artikel: