Ein kleines Geheimnis in der INDEX-Funktion 22

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Martin Weiß Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

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

  • Avatar-Foto
    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)?

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

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

      Schöne Grüße,
      Martin

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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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.

  • Avatar-Foto
    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

    • Avatar-Foto
      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))

    • Avatar-Foto
      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) 🙂

  • Avatar-Foto
    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))

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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)

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    David

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Eugen Beyer

    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.

    • Avatar-Foto
      Martin Weiß

      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