Das folgende Bild sagt eigentlich fast alles…

Abhängig von der Auswahl in Zelle D2 soll das entsprechende Team aus den Spalten A:B aufgelistet werden. Als Hilfsberechnungen zähle ich in Zelle F2, wie oft es den Eintrag gibt und in Zelle E2 wird ermittelt, in welcher Zelle zum ersten mal das in D2 ausgewählte Team auftritt.

Nun muss also der Bereich von Zeile E2 an eine Höhe von F2 Zellen haben. Im Beispiel läuft der Bereich von Zeile 2 bis Zeile 7, weil sich dort die Werte von Team 1 befinden.

=AGGREGAT(15;6;ZEILE(A:A)/(A:A=D2);1)
ermittelt dabei, welches die kleinste Zeilenzahl ist, in der zum ersten mal der in D2 eingestellte Wert steht.

Und nun die Herausforderung mit der Funktion INDIREKT. Mit INDIREKT können Zell- oder Bereichsbezüge konstruiert werden. Zum Beispiel in der Form =INDIREKT(„A“&ANZAHL2(A:A)).

Die Formel =WENN($F$2+4<ZEILE();"";INDIREKT("B"&($E$2+ZEILE()-5))) steht im Bereich E5:E18.

=WENN($F$2+4<ZEILE();"";... sorgt dafür, dass tatsächlich nur so viele Werte übernommen werden, wie in Zelle F2 ermittelt werden. Nämlich im Bereich der Zellen 5 bis 10 (hier 6 Werte). Alle übrigen Zellen sollen mit „“ leer bleiben.

Und nun wird mit ...;INDIREKT("B"&($E$2+ZEILE()-5))) dynamisch auf den Bereich verwiesen, der sich ab dem ersten Treffer aus E2 weiter nach unten befindet. Da der Bereich der Einträge hier erst in Zeile 5 startet, wird mit Zeile()-5 zunächst nichts hinzu addiert oder abgezogen. Bei Zeile 6 ergibt ZEILE() dann eine 6 und es wird 1 addiert usw.

Hier ist die Übungsdatei
Excel_441_Namen_auflisten_INDIREKT

Hier geht es zum Video
Videolink: http://youtu.be/5lOpwVA3P0c