Wie gibt man mehrere Suchwertbereiche in einer Zelle, durch Kommas getrennt, zurück?
Beim Arbeiten mit Daten in Excel kommt es häufig vor, dass Sie mehrere übereinstimmende Werte finden und diese in einer einzigen Zelle – getrennt durch Kommas, Bindestriche oder andere Trennzeichen – anzeigen müssen. Während integrierte Funktionen wie „VLOOKUP“ und „INDEX-VERGLEICH“ hervorragend geeignet sind, um jeweils einen einzelnen Treffer zu liefern, erfordert das Zusammenführen mehrerer Ergebnisse in einer Zelle einen fortgeschritteneren Ansatz. In diesem Artikel stellen wir Ihnen einige effektive Methoden vor, um diese Aufgabe in Excel elegant zu meistern.
Mehrere Suchwertbereich in einer Zelle durch Kommas getrennt zurückgeben
Mehrere Suchwertbereich in einer Zelle durch Kommas getrennt mit den Funktionen TEXTJOIN und FILTER zurückgeben
Wenn Sie Excel 365, Excel 2021 oder eine neuere Version verwenden, ist die dynamische Arrayformel die einfachste Lösung.
Wenden Sie die folgende Formel in einer leeren Zelle an, um das Ergebnis anzuzeigen, und drücken Sie anschließend die Enter-Taste, um es zu erhalten.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zellen zu übertragen. Siehe Screenshot:
- FILTER(B2:B15; A2:A15=D2): Extrahiert alle Werte aus Spalte B, deren zugehörige Zellen in Spalte A dem Wert in Zelle D2 entsprechen.
- TEXTJOIN(", "; WAHR; …): Verknüpft die Ergebniswerte zu einer einzigen, durch Kommas getrennten Zeichenfolge.
Mehrere Suchwertbereich in einer Zelle durch Kommas getrennt mit Kutools für Excel zurückgeben
„Kutools für Excel“ ist ein Add-in, das komplexe Excel-Aufgaben mühelos vereinfacht. Mit der leistungsstarken „Ein-zu-viele-Suche“-Funktion fügen Sie alle übereinstimmenden Werte spielend leicht in einer einzigen Zelle mit einem selbst definierten Trennzeichen zusammen – ganz ohne komplizierte Formeln oder VBA-Programmierung.
Führen Sie nach der Installation von Kutools für Excel Folgendes aus:
Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Ein-zu-viele-Suche (gibt mehrere Ergebnisse zurück)“, um das Dialogfeld zu öffnen. Nehmen Sie im Dialogfeld die folgenden Einstellungen vor:
- Wählen Sie jeweils „Platzierungsbereich der Liste und Suchwertbereich“ in den Textfeldern aus;
- Wählen Sie den Tabellenbereich aus, den Sie verwenden möchten;
- Geben Sie jeweils Schlüsselspalte und Rückgabespalte aus den Dropdown-Listen „Schlüsselspalte“ und „Rückgabespalte“ an;
- Klicken Sie zum Abschluss auf die Schaltfläche „OK“.
Ergebnis:
Alle entsprechenden Zellen mit dem gleichen Wert wurden in einer einzigen Zelle zusammengefasst und durch Kommas getrennt. Siehe Screenshot:
Mehrere Suchwertbereich in einer Zelle durch Kommas getrennt mit einer benutzerdefinierten Funktion zurückgeben
Wenn Sie nicht über Excel 365 oder Excel 2021 verfügen, zeigt Ihnen dieser Abschnitt Schritt für Schritt, wie Sie eine benutzerdefinierte Funktion (UDF) erstellen und nutzen, um mehrere Suchwerte aus einem Bereich in einer einzigen Zelle – durch Kommas getrennt – zurückzugeben.
1. Drücken Sie gleichzeitig die Tasten „ALT“ + „F11“, 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: Mehrere Suchwertbereich in einer durch Kommas getrennten 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 anschließend den Code und schließen Sie das Modulfenster. Kehren Sie zu Ihrem Arbeitsblatt zurück, und geben Sie in eine leere Zelle – dort, wo Sie das Ergebnis erhalten möchten – die folgende Formel ein. Ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zellen zu übertragen. Siehe Screenshot:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
Zusammenfassend lässt sich sagen: Das Zurückgeben mehrerer Suchwerte in einer einzigen Zelle – durch Kommas getrennt – lässt sich je nach Excel-Version und Anforderungen effizient mit verschiedenen Methoden umsetzen. Wählen Sie einfach die Methode, die am besten zu Ihnen passt, optimieren Sie Ihre Datenanalyse und steigern Sie Ihre Produktivität! Wenn Sie weitere Excel-Tipps und -Tricks entdecken möchten,bietet unsere Website Tausende von Anleitungen.
Verwandte Artikel:
- VLOOKUP und mehrere Werte ohne Duplikate zurückgeben
- Beim Arbeiten mit Daten in Excel kommt es oft vor, dass Sie mehrere übereinstimmende Werte zu einem bestimmten Suchkriterium benötigen. Die Standardfunktion VLOOKUP liefert jedoch lediglich einen einzigen Wert. Falls mehrere Übereinstimmungen existieren und Sie diese ohne Duplikate in einer einzigen Zelle anzeigen möchten, stehen Ihnen alternative Methoden zur Verfügung, um dieses Ziel zu erreichen.
- Mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückgeben
- Normalerweise ist das Suchen eines bestimmten Werts und das Zurückgeben des zugehörigen Elements für die meisten von uns mithilfe der VLOOKUP-Funktion kinderleicht. Haben Sie jedoch jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien – wie im folgenden Screenshot gezeigt – zurückzugeben? In diesem Artikel stelle ich einige Formeln vor, mit denen Sie diese komplexe Aufgabe in Excel meistern können.
- VLOOKUP und den letzten übereinstimmenden Wert zurückgeben
- Wenn Sie eine Liste mit häufig wiederkehrenden Elementen haben und nun lediglich den letzten Wert ermitteln möchten, der mit Ihren angegebenen Daten übereinstimmt – wie gehen Sie dabei vor?
- VLOOKUP und passende Daten zwischen zwei Werten zurückgeben
- In Excel können wir die normale VLOOKUP-Funktion anwenden, um den zugehörigen Wert basierend auf einem vorgegebenen Datum zu erhalten. Manchmal möchten wir jedoch einen passenden Wert zwischen zwei Werten wie im folgenden Screenshot dargestellt suchen und zurückgeben. Wie lässt sich diese Aufgabe in Excel lösen?
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