Kürzlich hatte ich die Ehre und das Vergnügen, auf den Excel-Kompetenztagen in Fulda als Trainer aufzutreten (eine Veranstaltung, die ich jedem ambitionierten Excel-Anwender nur empfehlen kann). Im Kurs über die Verweisfunktionen war unter anderem die INDEX-Funktion ein Thema. Versierte Excel-Anwender nutzen diese Funktion in Kombination mit VERGLEICH gerne als den besseren SVERWEIS.
Deutlich weniger bekannt ist, dass INDEX in zwei Versionen auftritt. Als Matrixversion (das ist die „übliche“ Variante) und als Bezugsversion (das ist die eher unbekannte Variante). Letztere ermöglicht es, mit nur einer Formel gleich mehrere Bereiche auswerten zu können.
Wozu man das brauchen kann und wie das funktioniert, kannst du in diesem Artikel lesen.
Und so geht’s:
Eine kleine Auffrischung
Mit der INDEX-Funktion wird üblicherweise der Wert zurückgeliefert, der sich an einer bestimmten Position – angegeben durch eine Zeile und/oder Spalte – innerhalb einer Matrix oder Tabelle befindet.
INDEX(Matrix;Zeile;Spalte)
So liefert im folgenden Beispiel die Formel =INDEX(B3:D5;3;2) den Preis in Zeile 3 und Spalte 2 innerhalb der angegebenen Matrix zurück:
Die INDEX-Funktion in der einfachen Variante
Wenn man das noch mit der VERGLEICH-Funktion kombiniert, lassen sich die Zeilen- und Spaltennummern auch dynamisch ansteuern.
INDEX und VERGLEICH kombiniert
Wenn du mehr über die Kombination aus INDEX und VERGLEICH erfahren möchtest, dann empfehle ich dir die folgenden Artikel:
Ein echtes Dream-Team: INDEX und VERGLEICH
Das Dreamteam aufgebohrt: INDEX + VERGLEICH mit mehreren Kriterien
Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2)
Soviel zur generellen Funktionsweise von INDEX. Aber ich habe ja von einer eher unbekannten Variante gesprochen. Und die kommt jetzt (die Beispieldatei dazu kannst du dir übrigens hier herunterladen).
INDEX in der Bezug-Version
Die INDEX-Funktion kann auch noch mit einem vierten Parameter „Bereich“ verwendet werden:
INDEX(Bezug;Zeile;Spalte;Bereich)
Dieser Parameter kommt dann zum Einsatz, wenn man mehrere Bezüge verwenden möchte. Sprich: wenn nicht nur ein, sondern gleich mehrere Tabellenbereiche durchsucht werden sollen.
Zur Veranschaulichung habe ich die kleine Preisliste aus dem ersten Beispiel etwas umgebaut und um ein drittes Kriterium erweitert:
Aufgabe mit drei Variablen
In den Zellen B9:B11 möchte ich Land, Ausführung und Produkt auswählen können und dann für diese Kombination den Preis erhalten. Wenn man die Tabelle ansieht, besteht sie eigentlich aus drei Tabellenbereichen, nämlich für jedes Produkt ein eigener Bereich.
Die Tabelle besteht aus drei Bereichen
Dann machen wir uns mal an die Preisermittlung. Zwecks besserer Nachvollziehbarkeit verwende ich erst einmal ein paar Hilfszellen, um die jeweiligen Zeilen- und Spaltennummern für unsere INDEX-Funktion zu bestimmen.
Annäherung über Hilfszellen
Die erste VERGLEICH-Funktion in D9 sollte leicht nachvollziehbar sein und liefert die Zeilennummer. Der dritte Parameter 0 steht übrigens für die exakte Übereinstimmung beim Vergleich und sollte daher nicht vergessen werden.
Auch der zweite VERGLEICH in D10 ist leicht zu durchschauen und liefert die Spaltennummer. Da alle drei Produktbereiche jeweils zwei Spalten haben (Standard und Premium) und ich ja immer nur die relative Spaltennummer 1 oder 2 benötige, kann ich mich auf das erste Produkt beschränken.
Bleibt der dritte VERGLEICH, mit dem ich die laufende Nummer für den Produktbereich bestimmen möchte. Da die Produkte in Zeile 2 nicht in unmittelbar nebeneinander liegenden Spalten stehen, habe ich im Bereich H9:H11 eine kleine zusammenhängende Produktliste erstellt, die ich mit der VERGLEICH-Funktion durchsuche.
Mit diesen Hilfsergebnissen kann ich mich jetzt in Zelle B13 an die INDEX-Funktion machen. Dabei ist bei der Eingabe des ersten Parameters noch eine Besonderheit zu beachten. Da ja drei Bereiche angesprochen werden sollen (Produkt 1 – Produkt 3), müssen diese drei Bereiche in Klammern gesetzt werden, damit sie für den ersten Parameter akzeptiert werden. Für alle weiteren Parameter beziehe ich mich dann auf meine Hilfszellen:
=INDEX((B4:C6;D4:E6;F4:G6);D9;D10;D11)
INDEX in der Bezugsversion (mit Hilfszellen)
War doch gar nicht so schlimm, oder?
Wer auf die Hilfszellen verzichten möchte, muss nur die drei VERGLEICH-Funktionen in die INDEX-Formel packen. Das sieht dann so aus:
=INDEX((B4:C6;D4:E6;F4:G6);VERGLEICH(B9;A4:A6;0);VERGLEICH(B10;B3:C3;0);VERGLEICH(B11;H9:H11;0))
INDEX in der Bezugsversion (ohne Hilfszellen)
Wie du siehst, können auch in einer eigentlich bekannten Funktion wie INDEX noch ein paar Geheimnisse schlummern.
Oder kanntest du diese Variante schon und hast nur müde gegähnt? Lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich glaube, das ist genau das was ich schon lange gesucht habe. Ich habe aktuell eine Problemstellung, bei der ich 6 verschiedene Tabellenbereiche durchsuchen muß und bisher relativ aufwendig mit benannten Bereichen in Kombination mit der Funktion „Bereich.Verschieben“ gearbeitet habe. Ich werde diese Funktion gleich mal ausprobieren, ob die bei mir auch funktioniert.
Noch eine grundsätzliche Frage: Kann ich anstatt Zellbereiche anzugeben (B6:C6) auch Namen eintragen (z.B. Produkt 1)?
Hallo Andreas,
ja, Namen funktionieren grundsätzlich auch, sofern du über den Namensmanager welche festgelegt hast.
Schöne Grüße,
Martin
Hallo Martin,
alternativ dazu, weil es einer 3. VERGLEICH/)-Funktion hier nicht wirklich bedarf folgende Formel:
=INDEX(A:G;VERGLEICH(B9;A:A;0);VERGLEICH(B11;2:2;0)+(B10=“Premium“))
Gruß Werner
.. , – …
Hallo Werner,
das stimmt, für mein Beispiel funktioniert diese Formel auch sehr gut. Vielen Dank für diese Variante.
Schöne Grüße,
Martin
INDEX in der Bezugsversion, auf jeden Fall ein interessantes Thema. Danke Martin für den Beitrag.
In aller Bescheidenheit: Ich habe mich im Jahr 2016 mit 2 Beitragen auch an dieser Funktion versucht.
Hallo Gerhard,
nur keine falsche Bescheidenheit. Ja, die Bezugsversion gibt es eigentlich schon immer, aber meiner Erfahrung nach ist sie immer noch nur bei wenigen Anwendern bekannt.
Ich verlinke für alle interessierten Leser gleich mal zu deinen Artikeln:
https://clevercalcul.wordpress.com/2016/07/12/27-formeln-zur-index-bezugsversion-in-excel-teil-1/
https://clevercalcul.wordpress.com/2016/07/19/27-formeln-zur-index-bezugsversion-in-excel-teil-2/
Schöne Grüße,
Martin
Hallo Martin,
eine super Funktion, die mir bislang auch noch nicht geläufig war und die ich DEFINITIV irgendwann demnächst einsetzen werde.
Wenn man anstatt der drei Bereiche sechs angibt, könnte man auch noch auf die Produktliste in H9:H11 verzichten:
=INDEX((B4:C6;B4:C6;D4:E6;D4:E6;F4:G6;F4:G6);VERGLEICH(B9;A4:A6;0);VERGLEICH(B11;B3:C3;0);VERGLEICH(B10;B2:G2;0))
Dann findet Excel eben Bereich 1, 3 und 5 statt Bereich 1, 2 und 3.
Gruß,
Ralph
Aaaargh – Ausführung und Produkt vertauscht.
Korrekte Formel: =INDEX((B4:C6;B4:C6;D4:E6;D4:E6;F4:G6;F4:G6);VERGLEICH(B9;A4:A6;0);VERGLEICH(B10;B3:C3;0);VERGLEICH(B11;B2:G2;0))
Hallo Ralph,
auch eine schöne Variante, ich bin begeistert!
Schöne Grüße,
Martin
Bei einer Liste mit 350 Spalten wird die Bezugsliste dann aber ganz schön lang (so breite Listen kommen im Controlling nicht selten vor) 🙂
Diese Variante kannte ich tatsächlich nocht nicht. Ich werde sie auf alle Fälle im Hinterkopf behalten.
Spannend finde ich die Index-Formel auch bei der Rückgabe als Bezug anstelle von Wert in Kombination mit dem Doppelpunkt in der Form von Index(Bezug, Zeile,Spalte):Index(Bezug; Zeile; Spalte), mit der dynamisch und sehr performant ganze Bereiche flexibel aufsummiert werden können =Summe(Index(Bezug, Zeile,Spalte):Index(Bezug, Zeile,Spalte))
Hallo Marcel,
ja, wenn man einmal gesehen hat, wie INDEX auch funktionieren kann, dann tun sich plötzlich ganz neue Möglichkeiten auf. Wie zum Beispiel dein Beispiel mit der Summe.
Schöne Grüße,
Martin
Hallo,
der Bereich „Produkt“ ist in der Verweis-Funktion aus einer separaten Aufstellung. (H9:H11). Besteht die Möglichkeit eines Zeilenbereichs? Also in diesem Fall (B2:G2)
Hallo K.-H.
das Problem dabei ist, dass die Produkte nicht unmittelbar nebeneinander liegen, sondern immer eine Spalte dazwischen liegt. Daher müsste man das in der Formel entsprechend berücksichtigen, damit der gelieferte Wert auch mit dem Bereich übereinstimmt:
=INDEX((B4:C6;D4:E6;F4:G6);VERGLEICH(B9;A4:A6;0);VERGLEICH(B10;B3:C3;0);(VERGLEICH(B11;B2:G2;0)+1)/2)
Schöne Grüße,
Martin
Hallo Martin,
wer mit M365 arbeitet kann die Produkte mit der Sequenzfunktion in einen zusammenhängenden Bereich schreiben und entsprechend mit der INDEX-Funktion auslesen.
Bei deinem Beispiel würde die Funktion wie folgt aufgebaut:
=INDEX(B2:G2;;SEQUENZ(3;;;2))
Am besten die Funktion in einen Bereichsnamen schreiben und ensprechend auslesen, dann ist auch die Produkteliste nicht mehr als Hilfsliste erforderlich.
Freundliche Grüße aus dem Münsterland
Hallo Hannes,
das ist eine sehr gute Idee! SEQUENZ dafür zu nutzen, darauf wäre ich nie gekommen.
Vielen Dank für die schöne Ergänzung!
Martin
Guter Artikel zur Indexfunktion. Dass es zweite Variante gibt war mir neu. Gut zu wissen, man lernt immer dazu.
Danke für den Beitrag
LG
Frank R.
Gern geschehen!
Schöne Grüße,
Martin
Hallo,
Mich plagt seit 2 Tagen ein Excelproblem:
Ich habe 2 Tabellen mit jeweils Personen a,b und c. In Tabelle 1 steht zu jeder Person ein Zeitraum (Spalte A: Personen; Spalte B: Datum von; Spalte C: Datum bis). In Tabelle 2 stehen zu jeder Person unterschiedlichste Daten (Spalte A: Personen; Spalte B: Datum) – ich will mir aber nur die anzeigen lassen, die in den Zeitraum von Tabelle 1 (zwischen B und C) fallen.
Ein Sverweis zeigt mir nur den ersten Wert an und mit Index kann ich nicht auf 2 Kriterien Rücksicht nehmen (Person und Zeitraum).
bin ratlos
Hallo David,
ein kniffeliges Problem, für das ich spontan auch keine Formellösung habe. Ich würde es stattdessen mit Power Query umsetzen: Tabelle1 einlesen und so transformieren, dass für jedes Datum im Zeitraum von/bis ein eigener Datensatz entsteht. Geht z.B. mit einer benutzerdefinierten Spalte: {Number.From([Datum von])..Number.From([Datum bis])}
Danach kannst du Tabelle1 und Tabelle2 über einen inneren Join zusammenführen, somit bleiben nur noch die relevanten Personen aus Tabelle2 übrig.
Schöne Grüße,
Martin
Meine Frage war, ob man auch mit Indizes rechnen kann, z.B., wenn ich in einer Tabelle S0 (S Null, O ist also eine Zahl und tiefer gestellt). In der nächsten Tabelle soll S(0+1), also S1 stehen, die 1 wieder als Indize, also tiefer gestellt.
Hallo Eugen,
eine Fortschreibung ist natürlich grundsätzlich möglich, dafür gibt es verschiedene Möglichkeiten (mit Hilfe einer Formel, Datenreihen fortsetzen etc.)
Eine automatische Tieferstellung der Indexzahl ist jedoch nicht möglich. Zumindest nicht ohne VBA-Programmierung.
Schöne Grüße,
Martin