Ein echtes Dream-Team: INDEX und VERGLEICH 23

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!

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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. Erforderliche Felder sind mit * markiert.

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

    • Martin Weiß Autor des Beitrags

      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ß Autor des Beitrags

      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

  • Thorsten Geise

    Hallo Martin,
    einen für mich wichtigen Vorteil der Kombination INDEX und VERGLEICH, kann ich gar nicht hoch genung loben:
    Füge ich in eine Tabelle die mit SVERWEIS durchsucht wird neu Spalten (oder bei der Suche in Zeilen neue Zeilen ein), dann darf ich nicht vergessen die Spalten oder die Zeilen – Anzahl anzupassen.
    Bei der Kombination INDEX und VERGLEICH bin ich dieser Aufgabe enthoben!
    Viele Grüße
    Thorsten Geise
    -und Danke noch mal für den Hinweis zur Verwendung von SUMMEWENN. Ich habe es abel letztendlich so gelöst, dass ich die Tabelle in eine „intelligente“ Tabelle umgewandelt habe und die Stundenauswertung über eine PIVOTE-Tabelle durchführe. Das hat den Charm, dass ich nun noch andere beliebige Auswertungen schnell ausführen kann.
    Danke für Deine Arbeit!.

    • Martin Weiß Autor des Beitrags

      Hallo Thorsten,

      das mit der Anpassung der Spaltennummer ist ein guter Hinweis, so etwas wird tatsächlich häufig vergessen.
      Und Pivot-Tabellen sind natürlich generell eine gute Wahl 🙂

      Schöne Grüße,
      Martin

  • Lutz

    Du bist geil !
    Ich suche nach Tagen nach einer scheinbar nicht auffindbaren lösung für ein ziemlich bescheuertes, teures und nerviges problem
    vergleich und index lösen es mit ultra kurzen zeilencode

    ich danke dir!

    ich liebe Dich ! :-*

    • Martin Weiß Autor des Beitrags

      Hi Lutz,

      gern geschehen. Und wer hätte gedacht, dass Excel mal zu einer solch schönen Liebeserklärung führt 🙂

      Schöne Grüße,
      Martin

  • Andreas Roessler

    super – Martin, guter Tip, komme aber auch nicht weiter, hab ein ganz anderes Problem:
    ich stelle mit grad eine Tabelle für meine Standard-Therapie (COPD/Asthma) zusammen, habe eine kleine Verweisliste (6 Medikamente und jeweils die Dosierung dazu – JETZT aber sollte nur bei einem Medikament ein dropdownfeld
    zur Dosierung „entstehen“, für die anderen nicht – geht das überhaupt,
    falls nicht, wie kriegt man das mit dem Datum in dem Verweisfeld für dieses spezielle Medikament hin (ab 6. Tag 1 Tablette, ab 12 Tag 3/4 Tablette) ausgehend vom Startdatum der Behandlung? Danke Ihnen/Dir im Voraus

  • Judith

    Hallo ihr Fließigen,

    ich arbeite gerade auch mit Index und Vergleich. Ich frage ab, ob in der Spalte B mein Wert X steht. Wenn das so ist, dann soll er die Zellwerte rechts daneben addieren. Funktioniert auch wunderbar. Allerdings steht der Wert X öfter in Spalte B und alle Werte, die daneben stehen, sollen addiert werden und nicht nur die vom zuerst gefundenen. Wisst ihr wie ich das lösen kann?

    Meine aktuelle Formel: =SUMME(INDEX(A:O;VERGLEICH(R7;B:B;0);))

    Wäre toll, wenn ihr hier eine Lösung hättet.

    Viele Grüße,
    Judith

    • Martin Weiß Autor des Beitrags

      Hallo Judith,

      ich kenne zwar den Aufbau Deiner Tabelle nicht, aber das hört sich für mich eher nach einem Fall für eine SUMMEWENN-Funktion an.

      Schöne Grüße,
      Martin

  • Susanne Dinges

    Hallo,
    ich habe gehofft mein aktuelles Excel-Problem mit einer Index/Vergleich Formel zu lösen, bekomme aber kein Ergebnis. Es sieht wie folg aus: Ich habe 2 Suchkriterien. Spalte A enthält Tarif Bezeichnungen und Spalte B Verbrauchsstufen. D. h. jeder Tarif hat ähnliche zum Teil die gleichen Verbrauchsstufen (Tarif A z.B. ab 0 ab 5.000 ab 50.000; Tarif B ab 0, ab 5.000 ab 150.000). Jeder Tarifs-/Verbrauchstsufenkombination ist ein Preis zu geordnet, welcher ausgegeben werden soll. Ich habe nun als zwei Suchkriterien Tarif-Name (Feld B34) und einen konkreten Verbrauch wie z.B. 27.106 (Feld B33).
    Meine gebaute Formel sieht folgendermaßen aus:
    {=INDEX(C3:C21;VERGLEICH(A26&B26;A3:A21&B3:B21))}
    Ausgegeben bekomme ich allerdings nur den Wert in der obersten Zeile. Mit Vergleichstyp -1 (größer als) kommt #NV.
    Ich habe nun schon das halbe Internet durchforstet und komme zu keinem Ergebnis.

    Evtl. kannst Du mir helfen oder hast einen anderen Ansatz zur Problembewältigung für mich.

    Vielen Dank im Voraus.
    LG Susanne

    • Martin Weiß Autor des Beitrags

      Hallo Susanne,

      eine interessante Aufgabenstellung! Die Kombination der Spalten A und B in der VERGLEICH-Funktion bringt Dich nicht weiter, da der gesuchte exakte Wert in Spalte B nicht vorhanden ist (zumindest wenn ich es richtig verstanden habe).

      Ich würde es mit einem SVERWEIS probieren, der als Matrix einen dynamischen Bereich über die BEREICH.VERSCHIEBEN-Funktion verwendet. Das könnte so aussehen:

      Die Formel in B35 lautet:
      =SVERWEIS(B33;BEREICH.VERSCHIEBEN(A1;VERGLEICH(B34;A1:A7;0)-1;1;ZÄHLENWENN(A1:A7;B34);2);2;WAHR)
      Mit der VERGLEICH-Funktion wird die Startzeile für den Tarif gesucht.
      ZÄHLENWENN berechnet die Anzahl der Zeilen innerhalb des Tarifs.

      Voraussetzung ist lediglich, dass die Tabelle nach Tarif und Verbrauchsstufen sortiert ist.

      Schöne Grüße,
      Martin

  • Mirko siewert

    moin Herr Weiß ,

    auf meiner unendlicher Suche bin ich jetzt auf Ihre Seite Gestoßen und möchte Sie höflichst um Hilfe bitten.

    Mein Problem :
    Ich speichere mir tägl. Den akt. Konto Auszug in eine Tabelle und ziehe mir die jeweiligen Daten die ich benötige .
    solange wie an einem Tag nur eine Transaktion gelaufen ist passt das auch alles . aber sobald es noch eine oder sogar noch mehrere gab wird es schwierig .kann Excel erkennen das er den ersten wert zu dem Datum schon wiedergegeben hat und mir automatisch den nächsten gegeben ?

    wäre echt froh von Dir zu hören

    Mfg Mirko

    • Martin Weiß Autor des Beitrags

      Hallo Mirko,

      ich bleibe jetzt auch einfach mal beim „Du“ 🙂
      Das von dir beschriebene Szenario ist nicht ganz einfach zu lösen. Die normalen Excel-Funktionen (SVERWEIS, INDEX etc.) greifen immer auf den ersten Wert zurück. Hier muss man schon ziemlich tief in die Trickkiste greifen, um das zu umgehen. Das ist auch nicht mit ein, zwei Sätzen beschrieben und hängt immer auch vom konkreten Aufbau der Tabellen ab.

      Als Tipp kann ich dir daher nur folgenden Artikel nennen:
      https://www.tabellenexperte.de/besser-als-sverweis-alle-werte-finden/

      Vielleicht hilft das ja ein wenig weiter.

      Schöne Grüße,
      Martin

  • Marc

    Hallo Martin,

    danke für diese Ausführliche Erklärung, aber irgendwie löst diese mein Problem nicht.
    Ich habe zwei Tabellen mit Artikelnummern die aus Zahlen und Buchstaben bestehen, diese möchte ich Vergleichen und bei Übereinstimmung den Wert einer bestimmten Spalte (in diesem Fall der Preis) ausgeben lassen.
    Die Artikelnummern können als Beispiele so aussehen:
    A1925E6
    1133HC
    oder auch einfach nur 1138

    Leider habe ich es nicht geschafft dein Beispiel auf auf meien Tabelle anzuwenden.
    Hast du vielelicht eine nTip pfür mich?

    VG
    Marc

    • Martin Weiß Autor des Beitrags

      Hallo Marc,

      das ist aus der Ferne natürlich schwer zu beurteilen. Ein genereller Stolperstein (auch beim SVERWEIS!) in diesem Zusammenhang sind unterschiedliche Formatierungen. Wenn, wie in deinem Beispiel, die Artikelnummer 1138 in einer Tabelle als Zahl formatiert ist und in der anderen als Text, wird die Formel nicht funktionieren.

      Schöne Grüße,
      Martin

      • Marc

        Guten Morgen,
        die Formatierung ist in allen Zellen gleich, ich denke mal das es wirklich an der Kombination aus Zahlen und Buchstaben liegt.
        Bis jetzt bin ich auf noch keine Lösung gestoßen.

        VG
        Marc