Zusammenfassung
Um mehrere Übereinstimmungen mit separaten Zellen in separaten Spalten zu extrahieren, können Sie eine Array-Formel verwenden, die auf INDEX und SMALL basiert. In dem gezeigten Beispiel lautet die Formel in F5:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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 C5: C11 und "Gruppen" beziehen sich auf B5: B11. Diese Namen sind auch im obigen Screenshot definiert.
Der Kern dieser Formel lautet: Wir verwenden die SMALL-Funktion, um eine Zeilennummer zu generieren, 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=$E5,ROW(names)-MIN(ROW(names))+1)
Dieses Snippet testet den benannten Bereich "Gruppen" auf den Wert in E5. Wenn gefunden, wird eine Zeilennummer aus einem Array relativer Zeilennummern zurückgegeben, die erstellt wurden mit:
ROW(names)-MIN(ROW(names))+1
Das Endergebnis ist ein Array, das Zahlen enthält, bei denen eine Übereinstimmung vorliegt, und FALSE, bei denen nicht:
(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:
COLUMNS($E$5:E5)
Beim Kopieren über 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 COLUMNS einen Wert für k zurückgibt, der nicht existiert, gibt SMALL einen # NUM-Fehler aus. Dies geschieht, nachdem alle Übereinstimmungen stattgefunden haben. Um den Fehler zu unterdrücken, schließen wir die Formel in die IFERROR-Funktion ein, um Fehler abzufangen und eine leere Zeichenfolge ("") zurückzugeben.
