Ein kleines Geheimnis in der INDEX-Funktion 16

In der INDEX-Funktion schlummert mehr, als vielen Anwendern bekannt ist
 

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

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

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

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

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

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)

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)

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!
 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

16 Gedanken zu “Ein kleines Geheimnis in der INDEX-Funktion

  • Andreas Esper

    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)?

    • Martin Weiß Autor des Beitrags

      Hallo Andreas,

      ja, Namen funktionieren grundsätzlich auch, sofern du über den Namensmanager welche festgelegt hast.

      Schöne Grüße,
      Martin

  • neopa C

    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
    .. , – …

    • Martin Weiß Autor des Beitrags

      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

  • Gerhard Pundt

    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.

  • Ralph Deisenberger

    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

    • Ralph Deisenberger

      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))

    • Marcel

      Bei einer Liste mit 350 Spalten wird die Bezugsliste dann aber ganz schön lang (so breite Listen kommen im Controlling nicht selten vor) 🙂

  • Marcel

    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))

    • Martin Weiß Autor des Beitrags

      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

  • K.-H. Kaiser

    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)

    • Martin Weiß Autor des Beitrags

      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

      • Hannes

        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

        • Martin Weiß Autor des Beitrags

          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