Die INDEX-Formel in Excel ist ein unglaublich vielseitiges und leistungsstarkes Werkzeug, das in unzähligen Excel-Formeln, insbesondere in fortgeschrittenen Anwendungen, zu finden ist. Doch was genau leistet INDEX? Kurz gesagt, ruft INDEX den Wert an einer bestimmten Position innerhalb eines Bereichs ab. Stellen Sie sich zum Beispiel eine Tabelle mit den Planeten unseres Sonnensystems vor. Wenn Sie den Namen des 4. Planeten, Mars, mit einer Formel abrufen möchten, können Sie INDEX wie folgt verwenden:
=INDEX(B3:B11;4)Hierbei gibt INDEX den Wert in der 4. Zeile des angegebenen Bereichs zurück.
Was aber, wenn Sie den Durchmesser des Mars mit INDEX abrufen möchten? In diesem Fall können wir sowohl eine Zeilennummer als auch eine Spaltennummer angeben und einen größeren Bereich bereitstellen. Die folgende INDEX-Formel verwendet den gesamten Datenbereich in B3:D11, mit einer Zeilennummer von 4 und einer Spaltennummer von 2:
=INDEX(B3:D11;4;2)
Verwenden der INDEX-Funktion, um den Durchmesser des 4. Planeten abzurufenINDEX ruft den Wert in Zeile 4, Spalte 2 ab.
Zusammenfassend lässt sich sagen, dass INDEX einen Wert an einer bestimmten Stelle in einem Zellbereich basierend auf seiner numerischen Position abruft. Wenn der Bereich eindimensional ist, müssen Sie nur eine Zeilennummer angeben. Ist der Bereich zweidimensional, benötigen Sie sowohl die Zeilen- als auch die Spaltennummer.
An dieser Stelle denken Sie vielleicht: “Und was soll das? Wie oft kennt man tatsächlich die Position von etwas in einer Tabelle?”
Genau richtig. Wir brauchen eine Methode, um die Position der gesuchten Elemente zu finden.
Hier kommt die VERGLEICH-Funktion ins Spiel.
Die VERGLEICH-Funktion: Positionen dynamisch finden
Die VERGLEICH-Funktion ist für einen einzigen Zweck konzipiert: die Position eines Elements in einem Bereich zu finden. Wir können VERGLEICH beispielsweise verwenden, um die Position des Wortes “Pfirsich” in einer Liste von Früchten zu ermitteln:
=VERGLEICH("Pfirsich";B3:B9;0)VERGLEICH gibt hier 3 zurück, da “Pfirsich” das 3. Element ist. Die VERGLEICH-Funktion ist übrigens nicht groß- und kleinschreibungsempfindlich.
VERGLEICH kümmert sich nicht darum, ob ein Bereich horizontal oder vertikal angeordnet ist, wie Sie unten sehen können:
=VERGLEICH("Pfirsich";C4:I4;0)
Verwenden von VERGLEICH, um die Position in einem horizontalen Bereich zu findenDas gleiche Ergebnis mit einem horizontalen Bereich: VERGLEICH gibt 3 zurück.
Wichtig: Das letzte Argument in der VERGLEICH-Funktion ist der Vergleichstyp. Der Vergleichstyp ist entscheidend und steuert, ob die Übereinstimmung exakt oder annähernd ist. In vielen Fällen möchten Sie Null (0) verwenden, um ein genaues Übereinstimmungsverhalten zu erzwingen. Der Vergleichstyp ist standardmäßig auf 1 gesetzt, was eine ungefähre Übereinstimmung bedeutet. Es ist daher wichtig, einen Wert anzugeben.
INDEX und VERGLEICH gemeinsam nutzen: Dynamische Suchen in Excel
Nachdem wir die Grundlagen von INDEX und VERGLEICH behandelt haben, stellt sich die Frage, wie wir die beiden Funktionen in einer einzigen Formel kombinieren? Betrachten Sie die unten stehenden Daten, eine Tabelle mit einer Liste von Vertriebsmitarbeitern und deren monatlichen Umsatzzahlen für Januar, Februar und März.
Umsätze pro Vertriebsmitarbeiter pro Monat
Nehmen wir an, wir möchten eine Formel schreiben, die die Umsatzzahl für Februar eines bestimmten Vertriebsmitarbeiters zurückgibt. Aus der obigen Diskussion wissen wir, dass wir INDEX eine Zeilen- und Spaltennummer geben können, um einen Wert abzurufen. Um beispielsweise die Februar-Umsatzzahl für Frantz zurückzugeben, geben wir den Bereich C3:E11 mit Zeile 5 und Spalte 2 an:
=INDEX(C3:E11;5;2) // gibt 5194 $ zurückAber wir wollen offensichtlich keine Zahlen fest verdrahten. Stattdessen möchten wir eine dynamische Suche.
Wie werden wir das erreichen? Natürlich mit der VERGLEICH-Funktion. VERGLEICH eignet sich perfekt, um die benötigten Positionen zu finden. Schritt für Schritt lassen wir die Spalte als 2 fest codiert und machen die Zeilennummer dynamisch. Hier ist die überarbeitete Formel, wobei die VERGLEICH-Funktion anstelle von 5 in INDEX verschachtelt ist:
=INDEX(C3:E11;VERGLEICH("Frantz";B3:B11;0);2)Einen Schritt weiter gehen wir, indem wir den Wert aus H2 in VERGLEICH verwenden:
=INDEX(C3:E11;VERGLEICH(H2;B3:B11;0);2)VERGLEICH findet “Frantz” und gibt 5 an INDEX für die Zeile zurück.
Zusammenfassend:
INDEXbenötigt numerische Positionen.VERGLEICHfindet diese Positionen.VERGLEICHist inINDEXverschachtelt.
Kommen wir nun zur Spaltennummer.
Zwei-Wege-Suche mit INDEX und VERGLEICH
Oben haben wir die VERGLEICH-Funktion verwendet, um die Zeilennummer dynamisch zu finden, die Spaltennummer haben wir jedoch fest codiert. Wie können wir die Formel vollständig dynamisch gestalten, sodass wir die Umsätze für einen beliebigen Vertriebsmitarbeiter in einem beliebigen Monat zurückgeben können? Der Trick besteht darin, VERGLEICH zweimal zu verwenden – einmal, um eine Zeilenposition zu erhalten, und einmal, um eine Spaltenposition zu erhalten.
Aus den obigen Beispielen wissen wir, dass VERGLEICH sowohl mit horizontalen als auch mit vertikalen Arrays gut funktioniert. Das bedeutet, dass wir die Position eines bestimmten Monats mit VERGLEICH leicht finden können. Diese Formel gibt beispielsweise die Position von März zurück, die 3 ist:
=VERGLEICH("Mär";C2:E2;0) // gibt 3 zurückAber natürlich wollen wir keine Werte fest codieren, also aktualisieren wir das Arbeitsblatt, um die Eingabe eines Monatsnamens zu ermöglichen, und verwenden VERGLEICH, um die benötigte Spaltennummer zu finden. Der folgende Screenshot zeigt das Ergebnis:
Dynamische Suche mit INDEX und VERGLEICHEine vollständig dynamische Zwei-Wege-Suche mit INDEX und VERGLEICH.
=INDEX(C3:E11;VERGLEICH(H2;B3:B11;0);VERGLEICH(H3;C2:E2;0))Die erste VERGLEICH-Formel gibt 5 als Zeilennummer an INDEX zurück, und die zweite VERGLEICH-Formel gibt 3 als Spaltennummer an INDEX zurück. Sobald VERGLEICH ausgeführt wurde, vereinfacht sich die Formel zu:
=INDEX(C3:E11;5;3)und INDEX gibt korrekt 10.525 $ zurück, die Umsatzzahl für Frantz im März.
Tipp: Sie könnten die Datenüberprüfung nutzen, um Dropdown-Menüs zur Auswahl von Vertriebsmitarbeiter und Monat zu erstellen und somit die Benutzerfreundlichkeit weiter zu erhöhen.
Linke Suche: Ein Vorteil gegenüber SVERWEIS
Einer der entscheidenden Vorteile von INDEX und VERGLEICH gegenüber der SVERWEIS-Funktion ist die Fähigkeit, eine “linke Suche” durchzuführen. Das bedeutet einfach eine Suche, bei der sich die ID-Spalte rechts von den Werten befindet, die Sie abrufen möchten, wie im folgenden Beispiel zu sehen:
Linke Suche mit INDEX und VERGLEICH
Dies erweitert die Flexibilität Ihrer Datenanalysen erheblich.
INDEX und VERGLEICH mit mehreren Kriterien
Eines der kniffligsten Probleme in Excel ist eine Suche, die auf mehreren Kriterien basiert. Mit anderen Worten, eine Suche, die gleichzeitig in mehr als einer Spalte übereinstimmt. Eine elegante Lösung für diese Probleme ist die Verwendung von Boolescher Logik, einer Technik zur Handhabung von WAHR- und FALSCH-Werten als 1en und 0en. Diesen Ansatz sehen Sie unten, wo wir INDEX und VERGLEICH sowie Boolesche Logik verwenden, um einen Preis basierend auf drei Werten zu finden: Artikel, Farbe und Größe:
INDEX und VERGLEICH mit mehreren Kriterien
Hinweis: Dies ist eine Matrixformel und muss in Excel 2019 und älteren Versionen mit STRG + UMSCHALT + EINGABE eingegeben werden. Für andere nützliche Werkzeuge, wie einen kostenlosen DVD Ripper, gibt es ebenfalls vielfältige Möglichkeiten, die Produktivität zu steigern.
Groß- und Kleinschreibung bei der Suche beachten
Die VERGLEICH-Funktion selbst ist nicht groß- und kleinschreibungsempfindlich. Sie können jedoch die EXAKT-Funktion zusammen mit INDEX und VERGLEICH verwenden, um eine Suche durchzuführen, die Groß- und Kleinschreibung berücksichtigt, wie unten gezeigt:
Groß- und Kleinschreibung beachtende Suche mit INDEX und VERGLEICH
Hinweis: Dies ist eine Matrixformel und muss in Excel 2019 und älteren Versionen mit STRG + UMSCHALT + EINGABE eingegeben werden.
Die nächstgelegene Übereinstimmung finden
Ein weiteres Beispiel, das die Flexibilität von INDEX und VERGLEICH unterstreicht, ist das Problem, die nächstgelegene Übereinstimmung zu finden. Im folgenden Beispiel verwenden wir die MIN-Funktion zusammen mit der ABS-Funktion, um einen Suchwert und ein Sucharray innerhalb der VERGLEICH-Funktion zu erstellen. Im Wesentlichen verwenden wir VERGLEICH, um die kleinste Differenz zu finden. Dann verwenden wir INDEX, um die zugehörige Fahrt aus Spalte B abzurufen.
Nächstgelegene Übereinstimmung mit INDEX und VERGLEICH finden
Hinweis: Dies ist eine Matrixformel und muss in Excel 2019 und älteren Versionen mit STRG + UMSCHALT + EINGABE eingegeben werden.
INDEX und XVERGLEICH: Die nächste Generation der Suchfunktionen
Die aktuelle Version von Excel enthält die XVERGLEICH-Funktion, die ein verbessertes Äquivalent zur VERGLEICH-Funktion darstellt. Wie die VERGLEICH-Funktion führt XVERGLEICH eine Suche durch und gibt eine numerische Position zurück. Auch wie VERGLEICH kann XVERGLEICH Suchen in vertikalen oder horizontalen Bereichen durchführen, unterstützt sowohl ungefähre als auch genaue Übereinstimmungen und ermöglicht Wildcards (* ?) für Teilübereinstimmungen. Aber XVERGLEICH bietet noch weitere Funktionen. Die 5 wichtigsten Unterschiede zwischen XVERGLEICH und VERGLEICH sind:
XVERGLEICHwählt standardmäßig eine genaue Übereinstimmung, währendVERGLEICHstandardmäßig eine ungefähre Übereinstimmung wählt.XVERGLEICHkann den nächstgrößeren oder den nächstkleineren Wert finden.XVERGLEICHkann eine umgekehrte Suche durchführen (d.h. von hinten nach vorne suchen).XVERGLEICHerfordert keine sortierten Werte bei der Durchführung einer ungefähren Übereinstimmung.XVERGLEICHkann eine binäre Suche durchführen, die speziell auf Geschwindigkeit optimiert ist.XVERGLEICHkann eine Regex-Suche durchführen, eine leistungsstarke Methode zum Abgleichen komplexer Textmuster.
Können Sie also einfach XVERGLEICH in einer INDEX– und VERGLEICH-Formel anstelle von VERGLEICH verwenden? Ja, absolut. Die Verwendung von XVERGLEICH anstelle der VERGLEICH-Funktion “verbessert” die Formel, um die oben aufgeführten Vorteile zu nutzen.
VERGLEICH durch XVERGLEICH ersetzen
Für Probleme mit genauen Übereinstimmungen ist XVERGLEICH ein direkter Ersatz für die VERGLEICH-Funktion. Sie können einfach “VERGLEICH” in “XVERGLEICH” ändern, wie unten gezeigt:
=VERGLEICH(Wert;Array;0) // genaue Übereinstimmung
=XVERGLEICH(Wert;Array;0) // genaue ÜbereinstimmungHinweis: Da XVERGLEICH standardmäßig eine genaue Übereinstimmung wählt, ist die Null oben nicht erforderlich. Wenn Sie jedoch VERGLEICH im Modus für genaue Übereinstimmungen in XVERGLEICH konvertieren, können Sie die Null beibehalten, wenn Sie möchten.
Bei ungefähren Übereinstimmungen unterscheidet sich das Verhalten von XVERGLEICH, wenn der Vergleichstyp auf 1 gesetzt ist:
=VERGLEICH(Wert;Array;1) // genaue Übereinstimmung oder nächstkleinerer Wert
=XVERGLEICH(Wert;Array;1) // genaue Übereinstimmung oder nächstgrößerer WertZusätzlich erlaubt XVERGLEICH -1 für den Vergleichstyp, was bei VERGLEICH nicht verfügbar ist:
=XVERGLEICH(Wert;Array;-1) // genaue Übereinstimmung oder nächstkleinerer WertHinweis: Die VERGLEICH-Funktion bietet das Argument für den Suchmodus überhaupt nicht an. XVERGLEICH kann auch so konfiguriert werden, dass eine umgekehrte Suche und eine binäre Suche durchgeführt werden. Für eine vollständige Beschreibung aller Optionen, die mit XVERGLEICH verfügbar sind, lesen Sie bitte die entsprechende Dokumentation.
Fazit: Meistern Sie die INDEX FORMEL EXCEL für fortgeschrittene Datenverwaltung
Die INDEX-Formel, insbesondere in Kombination mit VERGLEICH oder der neueren XVERGLEICH-Funktion, ist ein unverzichtbares Werkzeug für jeden Excel-Anwender, der über grundlegende Suchen hinausgehen möchte. Sie bietet eine Flexibilität und Leistung, die traditionelle Funktionen wie SVERWEIS oft nicht erreichen können, insbesondere bei der Bewältigung komplexer Szenarien wie linker Suchen, mehrfacher Kriterien oder der Berücksichtigung von Groß- und Kleinschreibung.
Indem Sie diese leistungsstarken Funktionen beherrschen, erweitern Sie Ihre Fähigkeiten in der Datenanalyse und -verwaltung erheblich. Sie können dynamischere und robustere Tabellen erstellen, die sich an wechselnde Anforderungen anpassen und Ihnen präzise Ergebnisse liefern. Investieren Sie Zeit in das Üben dieser Formeln – die anfängliche Lernkurve mag steiler sein als bei einfacheren Funktionen, doch die Vorteile in Bezug auf Effizienz und Datenintegrität sind immens. Tauchen Sie ein in die Welt der INDEX-Formel und heben Sie Ihre Excel-Fähigkeiten auf das nächste Level!