Generische Formel
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Zusammenfassung
Um die Position der n-ten Übereinstimmung zu ermitteln (z. B. den 2. Übereinstimmungswert, den 3. Übereinstimmungswert usw.), können Sie eine Formel verwenden, die auf der SMALL-Funktion basiert. Im gezeigten Beispiel lautet die Formel in G5:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Diese Formel gibt die Position des zweiten Auftretens von "rot" in der Liste zurück.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Diese Formel verwendet den benannten Bereich "Liste", der der Bereich B5: B11 ist.
Der Kern dieser Formel ist die SMALL-Funktion, die einfach den n-ten kleinsten Wert in einer Liste von Werten zurückgibt, die Zeilennummern entsprechen. Die Zeilennummern wurden von der IF-Anweisung "gefiltert", die die Logik für eine Übereinstimmung anwendet. IF vergleicht von innen nach außen alle Werte im benannten Bereich "list" mit dem Wert in B5, wodurch ein Array wie das folgende erstellt wird:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Der "Wert wenn wahr" ist eine Menge relativer Zeilennummern, die durch diesen Code erstellt wurden:
ROW(list)-MIN(ROW(list))+1
Das Ergebnis ist ein Array wie folgt:
(1;2;3;4;5;6;7)
Auf dieser Seite finden Sie eine vollständige Erklärung.
Bei einem logischen Test, der ein Array von Ergebnissen zurückgibt, fungiert die IF-Funktion als Filter - nur Zeilennummern, die einer Übereinstimmung entsprechen, überleben, der Rest gibt FALSE zurück. Das von IF zurückgegebene Ergebnis sieht folgendermaßen aus:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Die Zahlen 1, 5 und 7 entsprechen der Position von "rot" in der Liste.
Schließlich gibt SMALL das n-te kleinste Element in dieser Liste zurück und ignoriert FALSE-Werte. Im Beispiel enthält F5 2, sodass SMALL den zweitkleinsten Wert zurückgibt: 5.
Holen Sie sich den zugehörigen Wert
Sobald Sie die relative Position der n-ten Übereinstimmung haben, können Sie diese Position mit der INDEX-Funktion verwenden, um einen zugeordneten Wert zurückzugeben.
