Ich habe dir in meinen letzten beiden Artikeln die Funktionen INDEX und VERGLEICH als eigenständige Funktionen vorgestellt.

Die beiden Artikel findest du im Abschnitt Formeln oder direkt, wenn du den beiden Links hier folgst.

Die Funktion VERGLEICH vorgestellt

Die Funktion INDEX vorgestellt

Wie du gemerkt hast, haben diese beiden allein, kein besonders großes Anwendungsgebiet.

Das ändert sich schlagartig, wenn man die beiden kombiniert. Dann erhält man eine äußerst hilfreiche und vielseitige verkettet Funktion.

Mit der verketteten Funktion aus INDEX und VERGLEICH kannst du auf Basis von Suchkriterien einen anderen Wert innerhalb eines Bereiches finden und wiedergeben. Damit kannst du beispielsweise vollständig die Funktionen SVERWEIS und WVERWEIS ersetzen.

In diesem Artikel zeige ich dir, wie die Kombination aus INDEX und VERGLEICH aufgebaut ist, welche Vorteile sie bietet und wie du sie am besten in der Praxis einsetzen kannst.

Na dann mal los.

Aufbau der Verketteten Funktionen INDEX und VERGLEICH

Die Kombination aus INDEX und VERGLEICH ist keine Eigenständige Funktion. Sie ist eine so genannte Verkettete Funktion. Als verkettet Funktion, werden Funktionen bezeichnet, wo eines oder mehrere Argumente der ersten Funktion durch eine andere Funktion ersetzt werden.

Mehr zu verketteten oder verschachtelten Funktionen findest du in diesem Artikel.

Wie müssen wir nun diese Verkettung aufbauen? Betrachten wir dazu als erstes die Bestandteile der INDEX Funktion.

Diese benötigt, wie ich dir in diesem Artikel beschrieben habe, einen Bereich indem sich der wieder zu gebende Wert befindet und die Angabe, wie viele Spalten und Zeilen wir uns in diesem Bereich bewegen wollen. Diese Angaben müssen Zahlen sein, und genau da kommt die Funktion VERGLEICH ins Spiel.

Du kannst dir in diesem Artikel den Aufbau nochmal im Detail anschauen aber kurz zusammengefasst, macht VERGLEICH nichts anderes als die Position eines Suchkriteriums in einem Bereich als Zahl wieder zu geben.

Fügt man diese beiden Funktionen nun zusammen, so definiert die Funktion VERGLEICH, wie viele Zeilen oder Spalten sich die Funktion INDEX bewegen soll, um den Wert zu finden. Ähnlich einem GPS gibt die VERGLEICH Funktion die Koordinaten an die INDEX Funktion weiter.

Im ersten Moment klingt das bestimmt etwas verwirrend, vielleicht hilft dir folgendes Beispiel wo du beide Funktionen in Action erlebst.

Dabei siehst du auch gleich zwei Vorteil der INDEX und VERGLEICH Kombination zu SVERWEIS.

Erstens suchen wir einen Wert in Spalte D und geben das Ergebnis aus Spalte B zurück, was mit SVERWEIS so nicht möglich ist und zweitens muss der Bereich, der das Suchkriterium und den Wiedergabe Wert enthält, nicht zusammenhängend sein.

In diesem Beispiel ist der Bereich, der unser Suchkriterium enthält in der INDEX Funktion als Matrix definiert. Die VERGLEICH Funktion sagt der INDEX Funktion, in welcher Zeile sich der Wiedergabewert befindet. Mit Hilfe des Suchkriteriums direkt in der Funktion oder in Zelle B14 als Verweis.

Wenn wir schon dabei sind, kommen wir doch gleich zu weiteren Vorteile im Vergleich zur Funktion SVERWEIS.

Vorteil von INDEX und VERGLEICH zu SVERWEIS

Du wirst jetzt bestimmt denken, warum du dir eine zusätzliche Funktion aneignen solltest, wenn du bisher doch mit SVERWEIS ganz gut zurechtgekommen bist. Naja, abgesehen davon, dass es nie verkehrt ist etwas Neues zu lernen, hat die Kombination von INDEX und VERGLEICH viele Vorteile gegenüber SVERWEIS.

Hier nun ein paar Gründe, warum du dich genauer mit INDEX und VERGLEICH auseinandersetzen solltest, vielleicht kann ich dich ja damit überzeugen.

Alle diese Vorteile hat die Funktion natürlich auch gegenüber HVERWEIS da diese aber bei weitem nicht so oft eingesetzt wird, habe ich SVERWEIS als Vergleich herangezogen.

Kleiner Spoiler vorab, INDEX und VERGLEICH können alles, was SVERWEIS kann und noch mehr.

Vorteile von INDEX und VERGLEICH gegenüber den VERWEIS Funktionen.

  • Such- und Wiedergabebereich können getrennt sein

Bei SVERWEIS musst du immer den gesamten Bereich miteinschließen, auch wenn du viele Zellen dazwischen gar nicht abfragen möchtest. Bei INDEX und VERGLEICH können die Such- und Wiedergabebereiche getrennt definiert werden, einzige Anforderung ist, sie müssen dieselbe Größe haben.

  • Du ersparst dir das leidige Zählen

Du kennst es bestimmt, das leidige Abzählen der Spalten, wenn du SVERWEIS verwendest. Das gehört der Vergangenheit an. Mit INDEX und VERGLEICH musst du nur den Such- und Wiedergabebereich wählen oder du suchst mit einer weiteren VERGLEICH Funktion die richtige Spalte ganz automatisch.

  • Im Suchbereich können jederzeit Zeilen und Spalten eingefügt werden

Wenn du bei SVERWEIS eine Spalte zwischen Such- und Wiedergabespalte eingefügt hast, hast du dir deine Formel zerschossen, da das Argument für den Spalten-Index in der SVEREWEIS Funktion nicht automatisch mit geändert wurde. Dieses Problem hast du mit INDEX und VERGLEICH nicht, da du auf eine Spaltenüberschrift referenzierst und da ist es egal, an welcher Position sich diese befindet.

  • Suche entweder links oder rechts

Mit INDEX und VERGLEICH kannst du entweder links oder rechts vom Suchkriterium suchen. Bei SVERWEIS oder HVERWEIS bist du limitiert auf eine Richtung.

  • Ersetzt SVERWEIS und HVERWEIS in einem

Du kannst sowohl in Zeilen als auch in Spalten nach dem Ergebnis suchen und ersetzt somit SVERWEIS und HVERWEIS durch eine Formel.

  • Sie Sortierreihenfolge in den Bereichen ist egal

SVERWEIS kann nur Daten in aufsteigender Reihenfolge korrekt verarbeiten. Bei INDEX und VERGELICH ist die Reihenfolge nicht wichtig.

  • Geschwindigkeit

Bei großen Datenbereichen ist INDEX und VERGLEICH schneller, da es im besten Fall nur den Such- und Wiedergabebereich durchlaufen muss.

SVERWEIS gehört aber längst nicht zum alten Eisen

Trotzdem gibt es keine Grund SVERWEIS oder HVERWEIS  nicht zu verwenden, wenn es in der Situation angemessen ist. Das sind zwei exzellente Funktionen mit eigenen Vorteilen. Ich gehe darauf näher in diesem Artikel ein (SVERWEIS und HVEREWEIS), möchte dir aber auch hier die Vorteile kurz aufzeigen, dann kannst du für dich entscheiden, welche die richtige Funktion für deinen Zweck ist. Das ist am Ende auch das wichtigste, dass du damit klarkommst und die größte Zeitersparnis für dich rausholst.

  • SVERWEIS ist einfach einzugeben
  • Sie ist gerade für Anfänger leicht zu erlernen und daher ein guter Einstieg
  • SVERWEIS ist einfacher zu verstehen, gerade wenn andere deine Datei nutzen kommt es so zu weniger Missverständnissen
  • Sie ist eine der meist genutzten Funktionen und ist daher eine must-know Funktion

Du siehst, viele Dinge, die mit den VERWEIS Funktionen nicht möglich sind, kannst du mit INDEX und VERGLEICH Problemlos erledigen. Trotzdem haben auch die VERWEIS Funktionen ihre Berechtigung. Du solltest die richtige Funktion anhand deiner Bedürfnisse wählen und verwenden.

Da kommen wir auch gleich zum nächsten Punkt. Nachfolgend zeige ich dir ein paar Einsatzbeispiele aus der Praxis in denen du die INDEX und VERGLEICH Kombination gut einsetzen kannst.

In diesen Praxisnahen Fällen kannst du INDEX und VERGLEICH einsetzen

Ich habe versucht alle möglichen Einsatzgebiete hier anhand eines Beispiels aufzuzeigen.

Lade dir dazu am besten die Übungsdatei herunter, so kannst du den Beispielen am besten folgen und ziehst den größten Nutzen aus dem Artikel. Die Übungsdatei findest du hier: Übungsdatei INDEX und VERGLEICH

Ich weiß aus Erfahrung, dass wenn du eine Lösung für ein Problem benötigst, in einem Blog Artikel nie exakt die Lösung dabei ist, die du suchst.

Leider kann ich hier nicht unendlich viele Kombinationen anführen, um für jedes Problem die richtige Lösung aufzuzeigen. Ich kann dir aber folgendes anbieten.

Melde dich mit deinem Problem bei mir und wir lösen es gemeinsam, das bedeutet ich erkläre dir wie du dein Problem löst, erstelle für dich die jeweilige Formel und zeige dir, wie du es zukünftig allein lösen kannst.

Dazu schreib mir einfach eine Nachricht.

Du kannst mich kontaktieren, wenn du folgendem Link folgst: Ich löse dein Excel Problem

Aber jetzt zu den versprochenen Beispielen.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

Ersetzten der Argumente für mehr Suchkriterien

Wie bereits erwähnt, ist das Ergebnis der VERGLEICH Funktionen, die Koordinaten für die INDEX Funktion, um das Wiedergabekriterium zu finden.

Das kann entweder ein Argument sein bei eindimensionalen Bereichen oder auch zwei bei mehrdimensionalen Matrizen oder Bereichen.

Was macht nun die Formel in Zelle B17?

=INDEX(B4:E8;VERGLEICH(B16;D4:D8;0);VERGLEICH(B15;B3:E3;0))

Die erste VERGLEICH Funktion durchläuft dabei den Bereich D4:D8 und gibt die Position des Suchkriteriums aus Zelle B16 als Zahl wieder.

=INDEX(B4:E8;4;VERGLEICH(B15;B3:E3;0))

Die zweite VERGLEICH Funktion macht das gleiche für das Suchkriterium aus Zelle B15.

=INDEX(B4:E8;4;1)

Durch die erste Zahl weiß nun die INDEX Funktion in welcher Zeile innerhalb des Bereiches sich das Ergebnis befindet, nämlich in der 4ten und durch die zweite, in welcher Spalte, nämlich in der 1ten. Die Funktion gibt nun als Ergebnis den Zellinhalt aus dem Schnittpunkt der 4ten Zeile und 1ten Spalte wieder.

=4

Diese unterschiedlichen Bereiche können in der Funktion INDEX und VERGLEICH verwendet werden

Als nächstes möchte ich dir zeigen, wie du unterschiedliche Bereiche in der Funktion verwenden kannst.

Die Funktion der Formel ist analog dem vorigen Kapitel, lediglich die Bereiche sind unterschiedlich. Beachte dabei, dass wenn der Bereich der INDEX Funktion mehrdimensional ist, du unbedingt Spalte und Zeile angeben solltest. Das siehst du in der Formel in Zelle B17.

Im zweiten Beispiel in Zelle B17 siehst du auch die Verwendung von einem Suchkriterium rechts vom Wiedergabewert, was einer der großen Vorteile von INDEX und VERGLEICH ist.

So suchst du in mehreren Bereichen

Ein weiteres interessantes Beispiel wie du die Kombination aus INDEX und VERGLEICH einsetzen kannst, findest du in diesem Artikel hier. Dabei kannst du auch wählen, aus welchem Bereich der Wiedergabewert stammen soll.

So suchst du nach ungefähren Daten in einem Bereich

Oft ist es der Fall, dass du ein bestimmtes Suchkriterium nicht kennst oder nur ungefähr kennst. Das könnte bei dem folgenden Beispiel der Fall sein, hier möchten wir wissen, welcher Verkäufer am nächsten zu unserem Umsatzziel im Juni herangekommen ist.

Hierzu allerdings zwei Anmerkungen.

  • Die Liste muss in der richtigen Reihenfolge sortiert sein, da du sonst ein falsches Ergebnis erhalten würdest.
  • Über die Funktion INDIREKT(„Verkaufszahlen[„&B16&„]“) definieren wir die richtige Spalte für unseren Monat, da diese ja variieren kann.

TIPP: Möchtest du mehr zur Funktion INDIREKT erfahren, dann ist dieser Artikel für dich interessant: Die Funktion INDIREKT

So suchst du die letzte Zahl oder die letzte gefüllte Zelle in einem Bereich

Abschließend möchte ich dir noch die Möglichkeiten zeigen, wie du die letzte Zahl in einem Bereich und die letzte nicht leere Zelle in einem Bereich findest. Das kann für dich auf alle Fälle sinnvoll sein.

Hier werden in den VERGLEICH Funktionen die Werte mit einem möglichst großen Endwert verglichen und der nächst kleinere wird wiedergegeben.

Weitere Beispiel zum Einsatz von INDEX und VERGLEICH

Folgende sehr interessante Artikel möchte ich dir auf keinen Fall vorenthalten. Die Ersteller der Artikel haben fundiertes Wissen im Bereich Excel und deren Inhalte können dir helfen, deine Ziele zu erreichen.

Mehrfache Suchkriterien

Verbindung mit anderen Funktionen

Mehrfache Suchkriterien Variante 2

Du solltest jetzt ein ungefähres Gefühl davon haben, wie und wozu du INDEX und VERGLEICH einsetzen kannst. Natürlich gibt es noch jede Menge weitere Anwendungsmöglichkeiten und Verkettungen mit anderen Funktionen, die wichtigsten hast du aber jetzt kennen gelernt.

Ich schlage dir vor, ab jetzt jede SVERWEIS Funktion dadurch zu ersetzen. Dadurch lernst du am schnellsten mit der Funktion umzugehen.

Darauf solltest du beim Einsatz von INDEX und VERGLEICH achten

Bitte achte auf folgende Punkte, wenn du mit der INDEX und VERGLEICH Kombination arbeitest, um Fehler zu vermeiden.

  • Die Bereiche der INDEX und VERGLEICH Funktionen müssen dieselbe Größe haben
  • Achte auf Leerzeichen, diese können schnell zu falschen Ergebnissen führen und sind nur schwer zu entdecken
  • Beachte auch alles, was bei den einzelnen Funktionen wichtig ist, hier die Links zu den Artikeln VERGLEICH und INDEX

Fazit

Die Kombination aus INDEX und VEGLEICH ist sehr flexibel und kann für viele Anwendungen eingesetzt werden. Eigentlich könnte sie SVERWEIS komplett ersetzen.

Außerdem ist sie nicht so Fehleranfällig wie die VERWEIS Funktionen, was gerade Anfängern zugutekommen würde. Auch bei Änderungen des Bereiches, hat SVERWEIS oder HVERWEIS Nachteile.

Du solltest dich auf alle Fälle mit der Kombination INDEX und VERGLEICH vertraut machen, um auch komplexere Probleme lösen zu können.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.