Excel: Den 2., 3. oder n-ten Treffer finden (ohne Array-Formel)

Hier ist ein ausführliches Tutorial, wie du in Excel den 2., 3. oder n-ten Treffer einer Suche findest, ohne die komplizierten Array-Formeln (STRG+UMSCHALT+ENTER) zu verwenden.

Standardfunktionen wie SVERWEIS oder VERGLEICH haben ein Problem: Sie geben immer nur den ersten gefundenen Wert zurück. Wenn du jedoch eine Liste von Verkäufen hast und den zweiten oder dritten Kauf eines bestimmten Kunden finden möchtest, stoßen diese Funktionen an ihre Grenzen.

Hier sind die zwei besten Methoden, um dies ohne Array-Formeln zu lösen.

Methode 1: Die AGGREGAT-Funktion (Profi-Lösung)

Die AGGREGAT-Funktion ist seit Excel 2010 verfügbar. Sie ist extrem leistungsfähig, da sie Berechnungen über Arrays (Datenbereiche) durchführen kann, ohne dass du die Formel als Array-Formel abschließen musst.

Die Logik

Wir lassen Excel eine Liste aller Zeilennummern erstellen, in denen das Suchkriterium vorkommt. Mit der Funktion KKLEINSTE (innerhalb von AGGREGAT) wählen wir dann die 2., 3. oder n-te Zeilennummer aus und geben den Wert dieser Zeile mit INDEX aus.

Die Formel

Angenommen:

• Spalte A: Namen (Suchbereich)
• Spalte B: Betrag (Ergebnisbereich)
• Zelle D1: Der gesuchte Name
• Zelle D2: Welcher Treffer gesucht wird (z.B. "2" für den zweiten Treffer)

=INDEX(B:B; AGGREGAT(15; 6; ZEILE(A:A)/(A:A=D1); D2))

Erklärung der Parameter:

1. 15: Steht für die Funktion KKLEINSTE.
2. 6: Diese Option sagt Excel, dass Fehlerwerte ignoriert werden sollen. Das ist entscheidend!
3. ZEILE(A:A)/(A:A=D1): Hier passiert die Magie. Excel prüft jede Zeile. Wenn der Name übereinstimmt, wird die Zeilennummer durch 1 (WAHR) geteilt. Wenn nicht, wird sie durch 0 (FALSCH) geteilt, was einen Fehler (#DIV/0!) erzeugt. Da wir Option 6 gewählt haben, ignoriert Excel alle Zeilen, die nicht passen.
4. D2: Hier gibst du an, den wievielten Treffer du möchtest (das "k" von KKLEINSTE).

Methode 2: Die Hilfsspalten-Methode (Einfache Lösung)

Wenn dir die AGGREGAT-Formel zu komplex ist, kannst du das Problem mit einer einfachen Hilfsspalte lösen. Dies ist oft übersichtlicher für andere Nutzer der Tabelle.

Schritt 1: Hilfsspalte erstellen

Erstelle eine neue Spalte links von deinen Daten (z. B. eine neue Spalte A). Wir kombinieren den Suchbegriff mit der Häufigkeit seines Vorkommens.

Gib in Zelle A2 folgende Formel ein und ziehe sie nach unten:

=B2 & ZÄHLENWENN($B$2:B2; B2)

(Wenn in B2 "Max" steht, ergibt die Formel beim ersten Mal "Max1", beim zweiten Mal "Max2" usw.)

Schritt 2: Normaler SVERWEIS

Jetzt kannst du ganz einfach einen normalen SVERWEIS nutzen, um den n-ten Treffer zu finden.

Um den 2. Treffer von "Max" zu finden, suchst du einfach nach "Max2":

=SVERWEIS("Max2"; A:C; 3; FALSCH)

Oder dynamisch mit einer Zelle (D1 = Name, D2 = Treffernummer):

=SVERWEIS(D1 & D2; A:C; 3; FALSCH)

Methode 3: Für Office 365 & Excel 2021 (INDEX & FILTER)

Wenn du eine moderne Excel-Version nutzt, ist die Lösung noch einfacher. Die Funktion FILTER erzeugt zwar ein Array, wird aber wie eine normale Formel eingegeben (Dynamic Arrays).

Die Formel

=INDEX(FILTER(B:B; A:A=D1); D2)

Erklärung:

1. FILTER(B:B; A:A=D1): Gibt eine Liste aller Werte aus Spalte B zurück, bei denen in Spalte A der gesuchte Name steht.
2. INDEX(...; D2): Pickt sich aus dieser gefilterten Liste das n-te Element (z.B. das 2.) heraus.

Zusammenfassung: Welche Methode soll ich wählen?

Tipp: Wenn du die AGGREGAT-Lösung nutzt, stelle sicher, dass der Bereich (A:A) nicht die gesamte Spalte umfasst, wenn du sehr viele Daten hast, da dies die Berechnung verlangsamen kann. Nutze lieber definierte Bereiche wie $A$2:$A$1000.