Ein echtes Dream-Team: INDEX und VERGLEICH 6

Mit der INDEX-Funktion lassen sich die Grenzen des SVERWEIS überwinden!
 

Eine bei vielen Anwendern oft verschmähte oder wenig verstandene Funktion führt völlig zu Unrecht ein ziemliches Schattendasein in der Excel-Welt: INDEX.

Dabei bietet diese unscheinbare Funktion das Potential, selbst die universelle SVERWEIS-Funktion blass aussehen zu lassen, wenn man ihr noch einen Partner zur Seite stellt.

Das neue Dream-Team heißt daher:

  • INDEX
  • VERGLEICH

Und so geht’s:

Als Beispiel dient mir heute eine Tabelle mit den DAX-Werten des vergangenen Monats:

Beispieltabelle: DAX-30

Beispieltabelle: DAX-30

INDEX

Die Index-Funktion gibt aus einer Tabelle (Matrix) einen bestimmten Wert zurück, den man über eine Zeilen- und Spaltennummer auswählt:

=INDEX(Matrix; Zeile; Spalte)

Im folgenden Beispiel liefert somit die Funktion den Wert in der 10. Zeile und 3. Spalte meiner DAX-Tabelle:

Einfache Anwendung

Einfache Anwendung

So weit noch ziemlich unspektakulär. Seine wahre Stärke spielt die INDEX-Funktion jedoch in Kombination mit der VERGLEICH-Funktion aus. Denn hier kann man die Grenzen von SVERWEIS überwinden.

VERGLEICH

Wie wir oben gesehen haben, liefert INDEX einen bestimmten Wert zurück. Die Funktion VERGLEICH hingegen liefert stattdessen eine Position.

=VERGLEICH(Suchkriterium; Matrix; Vergleichstyp)

Bleiben wir bei meiner DAX-Tabelle. Wenn ich z.B. den höchsten Tageshöchstwert ermittlt habe, dann zeigt mir die VERGLEICH-Funktion, in welcher Zeile sich dieser Wert befindet:

Position bestimmen mit VERGLEICH

Position bestimmen mit VERGLEICH

Zur Info: Der angegebene Vergleichstyp 0 besagt, dass ich nach einer exakten Übereinstimmung suche.

Damit weiß ich zwar, dass sich der absolute Höchstwert in Zeile 7 befindet. Ich möchte mir aber stattdessen gerne das Datum anzeigen lassen. Da sich die Datumsspalte aber links von meiner Tageshoch-Spalte befindet, hilft mir die SVERWEIS-Funktion nicht weiter. Denn die kann nur Werte ermitteln, die sich rechts von der Suchspalte befinden.

Jetzt schlägt die große Stunde für unser Dream-Team INDEX und VERGLEICH:

INDEX im Team mit VERGLEICH

INDEX im Team mit VERGLEICH

Was passiert hier?

Wie wir oben gelernt haben, gibt der zweite Parameter in der INDEX-Funktion die Zeilennummer an. Diese gebe ich aber nicht fix an, sondern ermittle sie mit Hilfe der VERGLEICH-Funktion aus dem vorangegangenen Beispiel. Als dritten Parameter für die Spaltennummer gebe ich die 1 an, da sich das Datum ja in der ersten Spalte befindet. Und erhalte damit das gewünschte Ergebnis, nämlich den 02.12.2013.

Wie man sieht, lässt sich mit der Kombination aus VERGLEICH und INDEX im Gegensatz zum SVERWEIS nicht nur nach rechts, sondern auch nach links suchen!

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.



Kommentar erstellen

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

6 Gedanken zu “Ein echtes Dream-Team: INDEX und VERGLEICH

    • Martin Weiß Beitragsautor

      Hallo Jörg,

      das kommt darauf an, welcher der beiden identischen Werte dann angezeigt werden soll. Die VERGLEICH-Funktion setzt ja eine sortierte Liste voraus (im Beispiel nach Datum sortiert). Dementsprechend sollte die Liste aufsteigend sortiert sein, wenn der datumsmäßig erste Wert gefunden werden soll bzw. absteigend, wenn der letzte Wert gewünscht wird.

      Grüße,
      Martin

  • Sebastian

    Hallo Martin,

    schön hast du das erklärt aber wie verhält es sich wenn man zur Bestimmung der korrekten Zeile zwei oder mehr Bedingungen braucht. Wie verknüpft man in der Index-Funktion zwei Vergleich-Funktionen?

    • Martin Weiß Beitragsautor

      Hallo Sebastian,

      das geht nur über eine Array-Formel, bei der die zwei (oder mehr) Suchkriterien sowie die zwei (oder mehr) zu durchsuchenden Spalten mit dem Ampersand-Zeichen verknüpft werden. Beispiel:

      {=INDEX(A5:A28;VERGLEICH(B2&C2;B5:B28&C5:C28;0))}

      In B2 und C2 stehen die beiden Suchkriterien, die Spalten B5:B28 und C5:C28 werden nach diesen Kriterien durchsucht. Und zum Schluß wird der Wert aus Spalte A5:A28 ausgegeben.

      WICHTIG: Die geschweiften Klammen dürfen nicht von Hand eingegeben werden. Stattdessen muss die Formel mit STRG+Umschalt+Enter abgeschlossen werden.

      Schöne Grüße,
      Martin

      • Oliver Lubich

        Hallo Martin,

        leider kommt es bei mir auch vor das Daten doppelt vorkommen und das mit den Suchkriterien wie du es erklärt hast, klappt bei mir nicht ganz. Und zwar ist bei mir das Suchkriterium ein Zeitraum, heißt: z.B. >=01.09.2016 und =%Datum%)*(A1:A20<=%Datum%))}

        Vergleichs-Wert:
        =VERGLEICH(MAX;B1:B20;0)

        INDEX:
        =Index(A1:A20;VERGLEICH(MAX;B1:B20;0);1)

        (Spalte A = Datum; Spalte D = Werte)

        Wie kriege ich meinen Zeitraum nun in die Index-Formel gebaut?

        Beim MAX-Wert wird mit der richtige Wert aus dem Zeitraum wiedergegeben. Nur beim Index wird wieder die ganze Tabelle, heißt Zeitraum von 01.01.2016 bis 30.09.2016, betrachtet und da kann ein Wert doppelt vorkommen.

        Ich hoffe du kannst helfen.

        Danke und Grüße
        Oliver