Tutorials

Mehrere Treffer in Excel zu einem Suchkriterium extrahieren und in separaten Zeilen ausgeben

Melden

Dieses Tutorial zeigt dir, wie du in Excel mehrere Treffer zu einem Suchkriterium extrahierst und diese in separaten Zeilen untereinander ausgibst.

Früher war dies nur mit extrem komplizierten Matrix-Formeln möglich. Heute gibt es zwei Wege, die wesentlich einfacher sind: die FILTER-Funktion (für Microsoft 365 & Excel 2021) und Power Query (für ältere Versionen).

Methode 1: Der moderne Weg (FILTER-Funktion)

Verfügbar in: Microsoft 365, Excel 2021 oder Web-Version.

Angenommen, du hast eine Liste mit Projekten (Spalte B) und Mitarbeitern (Spalte A). Du möchtest alle Projekte anzeigen, die "Max" zugeordnet sind.

Schritt-für-Schritt:

  1. Klicke in die Zelle, in der die Liste starten soll (z. B. D2).

  2. Gib die folgende Formel ein:

    =FILTER(B2:B10; A2:A10="Max"; "Kein Treffer")
    • B2:B10: Der Bereich, aus dem die Daten extrahiert werden sollen (die Ergebnisse).
    • A2:A10="Max": Die Bedingung (Suche in Spalte A nach dem Namen "Max").
    • "Kein Treffer": Optionaler Text, falls nichts gefunden wird.

  3. Drücke Enter. Excel erstellt automatisch eine "Überlauf"-Liste und füllt alle gefundenen Zeilen untereinander aus.

Methode 2: Power Query (Die Profi-Lösung für alle Versionen)

Ideal, wenn du mit sehr großen Datenmengen arbeitest oder Daten aus externen Quellen nutzt.

  1. Markiere deine Datentabelle.
  2. Gehe zum Reiter Daten und klicke auf Aus Tabelle/Bereich. (Bestätige ggf., dass deine Tabelle Überschriften hat).
  3. Der Power Query Editor öffnet sich.
  4. Klicke auf den Pfeil im Spaltenkopf der Spalte, nach der du filtern möchtest (z. B. "Mitarbeiter").
  5. Wähle den gewünschten Namen aus (z. B. "Max") und klicke OK.
  6. Gehe oben links auf Schließen & Laden.
  7. Excel erstellt ein neues Tabellenblatt mit genau den gefilterten Zeilen.

Vorteil: Wenn sich deine Quelldaten ändern, klickst du einfach rechts auf die neue Tabelle und wählst "Aktualisieren".

Methode 3: Die "Oldschool"-Matrixformel

Verfügbar in: Excel 2010, 2013, 2016, 2019.

Wenn du eine ältere Excel-Version nutzt, hilft die INDEX-Kombination. Diese Formel ist komplex, aber effektiv.

Angenommen: Suchkriterium steht in E1, Namen in A2:A10, Daten in B2:B10.

  1. Kopiere diese Formel in Zelle E2:
    =WENNFEHLER(INDEX($B$2:$B$10; AGGREGAT(15; 6; (ZEILE($A$2:$A$10)-ZEILE($A$2)+1)/($A$2:$A$10=$E$1); ZEILE(A1))); "")
  2. Ziehe die Formel am Ausfüllkästchen (unten rechts an der Zelle) nach unten.

Erklärung der Logik:

  • $A$2:$A$10=$E$1: Prüft, ob der Name übereinstimmt.
  • AGGREGAT(15; 6; ...): Findet die n-t kleinste Zeilennummer, die die Bedingung erfüllt (Fehler werden ignoriert).
  • ZEILE(A1): Dient als Zähler. Beim Runterziehen wird daraus A2, A3 etc., um den 1., 2. und 3. Treffer zu holen.

Profi-Tipps für dein Excel-Dashboard:

  • Dynamisches Suchfeld: Verweise in der FILTER-Formel nicht auf einen festen Text wie "Max", sondern auf eine Zelle (z. B. $G$1). So kannst du den Namen in G1 ändern und die Liste passt sich sofort an.
  • Sortierung direkt einbauen: Kombiniere den Filter mit der SORT-Funktion: =SORT(FILTER(B2:B10; A2:A10="Max"))
  • Mehrere Kriterien: Möchtest du nach "Max" UND "Status Abgeschlossen" filtern? Nutze das Sternchen * als "UND"-Verknüpfung: =FILTER(A2:C10; (A2:A10="Max") * (C2:C10="Abgeschlossen"))

Zusammenfassung: Welchen Weg soll ich wählen?

  • Du hast M365? Nutze immer FILTER(). Es ist schnell und einfach.
  • Du hast ein altes Excel? Nutze Power Query, wenn du nicht gerne mit komplizierten Formeln arbeitest.
  • Die Matrix-Formel ist nur nötig, wenn die Datei auf sehr alten Rechnern ohne Power-Query-Installation laufen muss.
0