Ein echtes Dream-Team: INDEX und VERGLEICH 37

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.



Schreibe einen Kommentar

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

37 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

  • Peter

    Hallo, Martin!
    Toller Artikel. Wie kann ich zusätzlich zu einer kombinierten Index/Vergleich-Formel noch einbauen, dass er mir den ausgebenen Wert (bei mir „m“ bzw. „w“) in „männlich“ bzw. „weiblich“ umwandelt?

    Mein Tabellenblatt zeigt mir verschiedene Personaldaten per DropDown-Auswahl (Feld C2) an. Formel:
    =INDEX(Personal!A2:K110;VERGLEICH($C$2;Namen;0);4)

    • Martin Weiß Autor des Beitrags

      Hallo Peter,

      dankeschön. Du könntest die Formel einfach um eine WENN-Funktion erweitern:
      =WENN(INDEX(Personal!A2:K110;VERGLEICH($C$2;Namen;0);4)=“m“;“männlich“;“weiblich“)

      Schöne Grüße,
      Martin

  • Sebastian

    Hallo Martin,

    besten dank für deine Seite. Deine Beiträge haben wir schon ein übers andere Mail geholfen. Ich habe jetzt jedoch eine komplexere Problemstellung.

    Wir haben einen Jahresurlaubskalender, wo in Spalte A der Name des Mitarbeiters steht und in Zeile 2 das Tagesdatum. Hat ein Mitarbeiter Urlaub an einem Tag ist entsprechend in seiner Spalte unterhalb des Datums ein „u“ eingetragen. ich würde jetzt gerne alle Tagesdaten, wo ein „u“ eingetragen wurde je Mitarbeiter ausgeben.
    Beispiel: Mitarbeiter Schmitz hat am 2., 3. und 4.Juni Urlaub, also auch in den beiden Feldern ein „u“ eingetragen. Wie muss ich die Formel aufbauen, damit ich zum Mitarbeiter die beiden Daten ausgegeben bekomme, sprich mir der 2.6.19, 3.6.19 und 4.6.19 ausgegeben wird. Das Sahnehäubchen wäre jetzt noch, wenn mir besagte Urlaubstage als Zeitraum, also 2.-4.6. angezeigt werden würden. Hast du eine Idee?

    Beste Grüße

    • Martin Weiß Autor des Beitrags

      Hallo Sebastian,

      so etwas lässt sich lösen, ist aber nicht mit ein, zwei Sätzen hier zu erklären. Aber ich werde genau dazu in einem der nächsten Blog-Artikel etwas schreiben. Daher muss ich dich noch um etwas Geduld bitten 🙂

      Schöne Grüße,
      Martin

  • Coco

    Vielen Dank, ist genial. Leider habe ich ein kleines Problem:

    Ich habe eine Überschrift, in unterer Formel ist das B2:O2
    =INDEX($B$2:$O$2;VERGLEICH(MIN(C4:C12&E4:E12);C4:C12&E4:E12;0))

    Aus den unteren Suchkriterien, soll es mir den tiefsten Betrag wiedergeben und dann die Überschrift, auf den es sich bezieht, wiedergeben.
    Bei einem Testlauf hat es geklappt, da das Suchkriterium sich auf die ganze Tabelle bezogen hat.

    Bei diesem sollen nur gewisse Zellen gesucht werden und irgendwie pack ich die Formel nicht. Auch mit den geschweiften Klammern passiert überhaupt nichts. Excel scheint zu denken, weil die geschweifte Klammer vor dem Gleichheitszeichen ist, dass es keine Formel ist. Und mit Strg (bei mir Ctrl) +Shift+Enter passiert nichts

    Vllt. habt ihr eine Idee?

      • Martin Weiß Autor des Beitrags

        Hallo Coco,

        kannst du vielleicht noch ein paar Beispiele geben, was in den Spalte C und E konkret drinsteht. Im Augenblick werde ich noch nicht so ganz schlau aus der Beschreibung…

        Schöne Grüße,
        Martin

        • Coco

          Danke für deine Antwort, Martin. Hoffe untenstehende Infos reichen.

          Die Spalten sehen so aus:
          In Spalte A stehen Daten wie z.B. 01.01.2019 – 07.01.2019 (die Daten interessieren nicht)
          In den nachfolgenden Spalten stehen Beträge:

          Spalte B in der 2. Zeile – Überschriften sind z.B. Firmennamen
          Die Beträge stehen in ab der 4. Zeile:
          1200

          Spalte C in der 2. Zeile
          1100

          Spalte D in der 2. Zeile
          1500

          Spalte E in der 2. Zeile
          1800

          Der Grund warum ich die Spalten selektieren muss ist, weil ich nur das Minimum alle zwei Spalten brauche. Leider müssen die Spalten in der Reihenfolge bleiben, sonst wäre es leichter einfach die ganze Daten zu markieren. Das hat nämlich ohne Probleme geklappt.

          Danke und Gruss

          • Martin Weiß Autor des Beitrags

            Hallo Coco,

            tut mir sehr leid, aber jetzt bin ich noch verwirrter als zuvor. Vielleicht steht ja ein anderer Leser weniger auf der Leitung…

            Schöne Grüße,
            Martin

  • Joschka

    Moin moin,

    ich komme mit der Kombination aus den beiden Funktionen leider nicht so recht weiter.

    Mein Vorhaben ist:

    Aus einer Zeile Bsp.: A1 bis A14 einen Wert zu suchen, wenn dieser Wert gefunden wurde, dann soll an anderer Stelle der Wert aus der Zelle links daneben ausgegeben werden. Das funktioniert aber leider nicht. Kombiniere ich Vergleich mit Index kommt immer ein Fehler. 🙁

    Besten Gruß

    Joschka

    • Martin Weiß Autor des Beitrags

      Hallo Joschka,

      das ist grundsätzlich genau das, was oben im Artikel beschrieben ist. Allerdings irritiert mich dein Beispiel ein wenig: Links von A1 bis A14 kann nichts mehr stehen…
      Aber vielleicht habe ich dich auch missverstanden.

      Schöne Grüße,
      Martin

  • Jürgen Luig

    Ich bin Neuling und benötige Hilfe. Kann man bei einer Kombination von Index und Vergleich in der Matrix die Überschriften von Zeile und Spalte gleich benennen? Beisp.: Spaltenüberschrift A2 bis A5: a,b,c,d, Zeilenüberschrift A2 bis A5 auch a,b,c,d. jeder Schnittpunkt von Zeile und Spalte ist mit einem unterschiedlichem Wert versehen. Zeilenüberschrift G1=a; Spaltenüberschrift G2=b;
    Formel: =index(A1:E5;vergleich(G1;A2:A5;0;vergleich(G2;A2:A5;0) das Ergebnis ist:“Sie haben zu viele Argumente für diese Funktion eingegeben“. Worin liegt mein Fehler? Hat das mit den gleichlautenden Zeilen- und Spaltenüberschriften zu tun? Oder sehe ich „den Wald vo lauter Bäumen nicht“.
    Mit freundlichen Grüßen
    Jürgen

    • Martin Weiß Autor des Beitrags

      Hallo Jürgen,

      ich komme leider mit deinen Zellenangaben nicht klar: Du schreibst „Spaltenüberschrift A2 bis A5“ und gleichzeitig „Zeilenüberschrift A2 bis A5“. Ich nehme mal an, die Spaltenüberschrift steht eher in B1 bis E1?
      So oder so fehlt zumindest eine Klammer in deiner Formel, bevor die zweite VERGLEICH-Funktion beginnt und zusätzlich am Ende der Formel. Also so:
      =index(A1:E5;vergleich(G1;A2:A5;0);vergleich(G2;A2:A5;0))

      Aber wenn die Tabelle so aussieht, wie ich vermute, müsste die Formel so lauten:
      =INDEX(A1:E5;VERGLEICH(G1;A1:A5;0);VERGLEICH(G2;A1:E1;0))

      Schöne Grüße,
      Martin

  • Stephan Buschor

    Ich bin verwundert, dass so was als Dream-Team hochstilisiert wird. Es ist schon eine Schande, dass Excel nicht in der Lage ist, so etwas triviales in einer Funktion zu gestalten. Das entspricht aber dem inkompetenten Verhalten von Microsoft und deren eindimensionalen Gedankengängen. Eine Matrixfunktion, welche mir den Wert aus einer Zielposition mit Rückgabe des Wertes um x Spalten und y Zeilen zurückgibt währe Hilfreich und ein echter Allrounder, denn mit so einer Funktion lassen sich praktisch alle Matrixabfragen bewerkstelligen. Doch dazu wird Excel wohl nie in der Lage sein.