Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in Excel zurückgeben (vollständige Anleitung)
Excel-Nutzer stoßen häufig auf Szenarien, in denen mehrere Werte extrahiert werden müssen, die gleichzeitig mehrere Kriterien erfüllen – und zwar so, dass alle übereinstimmenden Ergebnisse entweder in einer Spalte, einer Zeile oder zusammengefasst in einer einzigen Zelle angezeigt werden. Diese Anleitung stellt Methoden für alle Excel-Versionen vor sowie die neuere FILTER-Funktion, die in Excel 365 und Excel 2021 verfügbar ist.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer einzelnen Zelle zurückgeben
In Excel ist es eine häufige Herausforderung, mehrere übereinstimmende Werte basierend auf mehreren Kriterien innerhalb einer einzigen Zelle zu extrahieren – hier stellen wir zwei effiziente Methoden vor.
Methode 1: Verwendung der TEXTJOIN-Funktion (Excel365 / 2021,2019)
Um alle übereinstimmenden Werte mit Trennzeichen in einer einzigen Zelle zu erhalten, unterstützt Sie die TEXTJOIN-Funktion ideal.
Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dort hinein und drücken Sie die Eingabetaste (Excel 2021 und Excel 365) oder Strg+Umschalt+Eingabe in Excel 2019, um das Ergebnis zu erhalten:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) prüft, ob jede Zeile beide Bedingungen erfüllt: „Verkäufer gleich E2“ und „Monat gleich F2“. Ist das der Fall, liefert die Formel 1 – andernfalls 0. Das Sternchen * stellt sicher, dass beide Bedingungen gleichzeitig wahr sein müssen.
- WENN(...; $C$2:$C$21; „") gibt den Produktnamen zurück, wenn die Zeile übereinstimmt – andernfalls eine leere Zelle.
- TEXTVERKETTEN(", "; WAHR; ...) verknüpft alle nicht leeren Produktnamen in einer einzigen Zelle – getrennt durch „, “.
Methode 2: Verwendung von Kutools für Excel
Kutools für Excel bietet eine leistungsstarke, dabei aber dennoch einfach zu bedienende Lösung, mit der Sie mehrere Übereinstimmungen basierend auf mehreren Kriterien schnell abrufen und in einer einzigen Zelle kombinieren können – ganz ohne komplexe Formeln.
Führen Sie nach der Installation von Kutools für Excel Folgendes aus:
- Wählen Sie den Datenbereich aus, für den Sie alle zugehörigen Werte basierend auf Ihren Kriterien erhalten möchten.
- Klicken Sie dann auf Kutools > Vereinigen/Aufteilen > Erweiterte Zeilen zusammenführen, siehe Screenshot:
- Konfigurieren Sie im Dialogfeld Erweiterte Zeilen zusammenführen die folgenden Optionen:
- Wählen Sie die Spaltenüberschriften aus, die Ihre Suchkriterien enthalten (z. B. Verkäufer und Monat). Klicken Sie für jede ausgewählte Spalte auf „Primärschlüssel“, um sie als Suchbedingung festzulegen.
- Klicken Sie auf die Spaltenüberschrift, in der sich die zusammengeführten Ergebnisse befinden (z. B. „Produkt“). Wählen Sie im Bereich „Kombinieren“ Ihr bevorzugtes Trennzeichen (z. B. Komma, Leerzeichen oder ein benutzerdefiniertes Trennzeichen).
- Klicken Sie zum Abschluss auf die Schaltfläche „OK“.
Ergebnis: Kutools fasst sofort alle übereinstimmenden Werte pro eindeutiger Kriterienkombination in einer einzigen Zelle zusammen.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer Spalte zurückgeben
Wenn Sie mehrere übereinstimmende Datensätze aus einem Datensatz basierend auf mehreren Bedingungen extrahieren und in vertikaler Spaltenform anzeigen möchten, bietet Excel gleich mehrere leistungsstarke Lösungen.
Methode 1: Verwendung einer Arrayformel (für alle Versionen)
Sie können die folgende Arrayformel verwenden, um Ergebnisse vertikal in einer Spalte zurückzugeben:
1. Geben Sie die folgende Formel in eine leere Zelle ein oder fügen Sie sie dort ein:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Drücken Sie Strg+Umschalt+Eingabe, um das erste übereinstimmende Ergebnis zu erhalten. Wählen Sie anschließend die erste Formelzelle aus und ziehen Sie den Ausfüllknauf nach unten, bis eine leere Zelle erscheint. Daraufhin werden alle übereinstimmenden Werte wie im folgenden Screenshot dargestellt angezeigt:
- $A$2:$A$18=$E$2: Prüft, ob der Verkäufer dem Wert in Zelle E2 entspricht.
- $B$2:$B$18=$F$2: Prüft, ob der Monat dem Wert in Zelle F2 entspricht.
- * ist ein logischer UND-Operator (beide Bedingungen müssen erfüllt sein).
- ZEILE($C$2:$C$18)-ZEILE($C$2)+1: Erzeugt eine relative Zeilennummer für jedes Produkt.
- KKLEINSTE(...; ZEILE(1:1)): Gibt die n-kleinste übereinstimmende Zeile zurück (während die Formel nach unten gezogen wird).
- INDEX(...): Gibt das Produkt aus der passenden Zeile zurück.
- WENNFEHLER(...; „"): Gibt eine leere Zelle zurück, sobald keine weiteren Übereinstimmungen gefunden werden.
Methode 2: Verwendung der FILTER-Funktion (Excel365 / 2021)
Wenn Sie Excel 365 oder Excel 2021 nutzen, ist die FILTER-Funktion die ideale Lösung, um mehrere Ergebnisse anhand mehrerer Kriterien zu liefern – dank ihrer Einfachheit, Übersichtlichkeit und der Fähigkeit, dynamische Ergebnisse ohne komplexe Arrayformeln auszugeben.
Kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie manuell ein, und drücken Sie anschließend die Eingabetaste – alle passenden Datensätze werden basierend auf den mehreren Kriterien angezeigt.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) gibt alle Werte aus C2:C18 zurück, die beide Bedingungen erfüllen.
- (A2:A18=E2)*(B2:B18=F2): Logisches Array, das auf übereinstimmenden Verkäufer und Monat prüft.
- „Keine Übereinstimmung“: Optionale Meldung, die angezeigt wird, wenn keine Werte gefunden werden.
Mehrere übereinstimmende Werte basierend auf mehreren Kriterien in einer Zeile zurückgeben
Excel-Nutzer müssen oft mehrere Werte aus einem Datensatz extrahieren, die mehrere Bedingungen erfüllen, und diese horizontal – also in einer Zeile – darstellen. Das ist besonders nützlich für dynamische Berichte, Dashboards oder Übersichtstabellen, bei denen der vertikale Platz begrenzt ist. In diesem Abschnitt stellen wir zwei leistungsstarke Methoden vor.
Methode 1: Verwenden einer Arrayformel (für alle Versionen)
Herkömmliche Arrayformeln ermöglichen es, mehrere übereinstimmende Werte mithilfe der Funktionen INDEX, KKLEINSTE, WENN und SPALTE zu extrahieren. Im Gegensatz zur spaltenbasierten (vertikalen) Extraktion passen wir die Formel so an, dass die Ergebnisse zeilenweise ausgegeben werden.
1. Kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie manuell ein:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Drücken Sie Strg + Umschalt + Eingabe, um das erste übereinstimmende Ergebnis zu erhalten. Wählen Sie dann die erste Formelzelle aus und ziehen Sie die Formel nach rechts über die Spalten, um alle Ergebnisse zu erhalten.
- $A$2:$A$18=$E$2: Prüft, ob der Verkäufer übereinstimmt.
- $B$2:$B$18=$F$2: Prüft, ob der Monat übereinstimmt.
- *: Logisches UND – beide Bedingungen müssen erfüllt sein.
- ZEILE($C$2:$C$18)-ZEILE($C$2)+1: Legt die relative Anzahl der Zeilen fest.
- SPALTE(A1): Passt automatisch an, welche Übereinstimmung zurückgegeben wird – je weiter die Formel nach rechts gezogen wird.
- WENNFEHLER(...): Verhindert Fehlermeldungen, sobald alle Übereinstimmungen erschöpft sind.
Methode 2: Verwenden der FILTER-Funktion (Excel 365 / 2021)
Kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie ein, und drücken Sie die Eingabetaste. Alle übereinstimmenden Werte werden extrahiert und in einer Zeile angezeigt. Siehe Screenshot:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): Ruft übereinstimmende Werte aus Spalte C ab – basierend auf beiden Bedingungen.
- (A2:A18=E2)*(B2:B18=F2): Beide Bedingungen müssen erfüllt sein.
- MTRANS(...): Wandelt das von FILTER zurückgegebene vertikale Array in ein horizontales Array um.
🔚 Fazit
Das Abrufen mehrerer übereinstimmender Werte anhand mehrerer Kriterien in Excel lässt sich auf verschiedene Weise umsetzen – je nach gewünschter Darstellung der Ergebnisse: in einer Spalte, einer Zeile oder innerhalb einer einzelnen Zelle.
- Für Benutzer von Excel 365 oder Excel 2021 bietet die FILTER-Funktion eine moderne, dynamische und elegante Lösung, die Komplexität auf ein Minimum reduziert.
- Für Benutzer älterer Versionen bleiben Arrayformeln leistungsstarke Werkzeuge – erfordern jedoch etwas mehr Einrichtungsaufwand und Sorgfalt.
- Zusätzlich vereinfachen die TEXTVERKETTEN-Funktion oder Drittanbieter-Tools wie Kutools für Excel den Prozess erheblich, wenn Sie Ergebnisse in einer einzigen Zelle konsolidieren möchten oder eine Lösung ohne Code bevorzugen.
Wählen Sie die Methode, die am besten zu Ihrer Excel-Version und Ihrem bevorzugten Layout passt – und schon sind Sie bestens gerüstet, um Mehrfachkriterien-Suchen effizient und präzise durchzuführen. Wenn Sie weitere Excel-Tipps und -Tricks kennenlernen möchten, bietet unsere Website Tausende von Anleitungen, mit denen Sie Excel meistern können.
Weitere verwandte Artikel:
- Mehrere Suchwertbereich in einer durch Kommas getrennten Zelle zurückgeben
- In Excel können wir die VLOOKUP-Funktion nutzen, um den ersten übereinstimmenden Wert aus einer Tabelle zurückzugeben. Manchmal müssen jedoch alle übereinstimmenden Werte extrahiert und in einer einzigen Zelle durch ein bestimmtes Trennzeichen – wie etwa ein Komma oder einen Bindestrich – getrennt werden, wie im folgenden Screenshot dargestellt. Wie können wir mehrere übereinstimmende Werte in einer durch Kommas getrennten Zelle in Excel abrufen und zurückgeben?
- VLOOKUP und mehrere übereinstimmende Werte auf einmal im Google-Arbeitsblatt zurückgeben
- Die herkömmliche VLOOKUP-Funktion in Google Tabellen hilft Ihnen, den ersten passenden Wert anhand vorgegebener Daten zu finden und zurückzugeben. Manchmal möchten Sie jedoch – wie im folgenden Screenshot dargestellt – alle übereinstimmenden Werte mit VLOOKUP abrufen. Kennen Sie bewährte und einfach umsetzbare Methoden, um diese Aufgabe in Google Tabellen zu lösen?
- VLOOKUP und mehrere Werte aus einer Dropdown-Liste zurückgeben
- Wie rufen Sie in Excel mithilfe von VLOOKUP mehrere zugehörige Werte aus einer Dropdown-Liste ab, sodass beim Auswählen eines Elements sofort alle dazugehörigen Werte – wie im folgenden Screenshot gezeigt – angezeigt werden? In diesem Artikel stelle ich Ihnen die Lösung Schritt für Schritt vor.
- VLOOKUP und mehrere Werte vertikal in Excel zurückgeben
- Normalerweise können Sie mit der VLOOKUP-Funktion den ersten passenden Wert abrufen. Manchmal möchten Sie jedoch alle übereinstimmenden Datensätze basierend auf einem bestimmten Kriterium zurückgeben. In diesem Artikel zeige ich Ihnen, wie Sie mithilfe von VLOOKUP alle passenden Werte vertikal, horizontal oder in einer einzigen Zelle ausgeben können.
- VLOOKUP und passende Daten zwischen zwei Werten in Excel zurückgeben
- In Excel können Sie die herkömmliche VLOOKUP-Funktion nutzen, um basierend auf vorgegebenen Daten den zugehörigen Wert abzurufen. Manchmal möchten Sie jedoch mithilfe von VLOOKUP den passenden Wert zwischen zwei Werten ermitteln – so wie im folgenden Screenshot dargestellt. Wie lösen Sie diese Aufgabe in Excel?
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