Zusammenfassung
Um mehrere Übereinstimmungen mit separaten Zellen in separaten Zeilen zu extrahieren, können Sie eine Array-Formel verwenden, die auf INDEX und SMALL basiert. In dem gezeigten Beispiel lautet die Formel in E5:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Nachdem Sie die Formel in die erste Zelle eingegeben haben, ziehen Sie sie nach unten und quer, um die anderen Zellen auszufüllen.
Erläuterung
Hinweis: Diese Formel verwendet zwei benannte Bereiche: "Namen" bezieht sich auf C4: C11 und "Gruppen" beziehen sich auf B4: B11. Diese Namen sind auch im obigen Screenshot definiert.
Der Kern dieser Formel lautet: Wir verwenden die SMALL-Funktion, um eine Zeilennummer zu erhalten, die einer "n-ten Übereinstimmung" entspricht. Sobald wir die Zeilennummer haben, übergeben wir sie einfach an die INDEX-Funktionsfunktion, die den Wert in dieser Zeile zurückgibt.
Der Trick ist, dass SMALL mit einem Array arbeitet, das von IF in diesem Bit dynamisch erstellt wird:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Dieses Snippet testet die benannten Bereichs- "Gruppen" auf den Wert in E4. Wenn gefunden, wird eine "normalisierte" Zeilennummer aus einem Array von Zeilennummern zurückgegeben, die mit diesem Teil der Formel erstellt wurden:
ROW(names)-MIN(ROW(names))+1
Das Ergebnis ist ein Array, das Zeilennummern enthält, bei denen eine Übereinstimmung vorliegt, und FALSE, bei denen dies nicht der Fall ist. Das Array sieht ungefähr so aus:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Dieses Array geht in SMALL. Der k-Wert für SMALL (n-te) stammt aus einem wachsenden Bereich:
ROWS($E$5:E5)
Beim Kopieren in die Ergebnistabelle wird der Bereich erweitert, wodurch k (n-te) erhöht wird. Die SMALL-Funktion gibt jede übereinstimmende Zeilennummer zurück, die der INDEX-Funktion als row_num mit dem benannten Bereich "names" als Array übergeben wird.
Fehler behandeln
Wenn ROWS einen Wert für k zurückgibt, der nicht vorhanden ist, gibt SMALL einen # NUM-Fehler aus. Dies geschieht, nachdem alle Übereinstimmungen stattgefunden haben. Um den Fehler zu unterdrücken, verwenden wir IFERROR, um den Fehler abzufangen und eine leere Zeichenfolge ("") zurückzugeben.
