Kurs
Einführung in Statistik mit Google Sheets
4 Std.
47.9K
Wenn man Excel zum ersten Mal lernt, wird oft VLOOKUP als Standardmethode zur Datenaufbereitung vorgestellt. VLOOKUP ist tatsächlich eine leistungsstarke Funktion, die viele Aufgaben rund um die Datenverarbeitung bewältigt, wie in Data Wrangling with VLOOKUP in Spreadsheets gezeigt. Im selben Tutorial haben wir auch die Grundlagen der Kombination INDEX-MATCH behandelt – eine weitere Methode für vertikale Suchen in Tabellen, die gegenüber VLOOKUP mehrere Vorteile bietet, allerdings mit etwas komplexerer Formel.
Diese Vorteile umfassen unter anderem:
- Dynamische Spaltenbezüge: Du kannst in deinem Datensatz sicher Spalten einfügen oder löschen, ohne dass die Suchfunktion beeinträchtigt wird.
- Höhere Verarbeitungsgeschwindigkeit bei großen Datensätzen: Bei sehr großen Tabellen ist INDEX-MATCH in der Regel deutlich schneller als VLOOKUP, da INDEX-MATCH nur die Zielspalte und die Suchspalte berücksichtigt.
- Position der Suchwerte: VLOOKUP kann keine Werte links der ersten ausgewählten Spalte im Tabellenbereich berücksichtigen. INDEX-MATCH kann auch horizontal suchen und hat diese Einschränkung nicht.
- Größe des Suchwerts: VLOOKUP kann keine Werte mit mehr als 255 Zeichen nachschlagen – INDEX-MATCH schon.
Außerdem erlaubt INDEX-MATCH das Abgleichen auf Basis mehrerer Spalten sowie die Groß-/Kleinschreibung zu berücksichtigen. Das ist sehr nützlich, wenn du mit IDs arbeitest, die sich nur durch Groß- und Kleinbuchstaben unterscheiden. In diesem Tutorial steigen wir deshalb tiefer in die erweiterten Funktionen der Kombination INDEX-MATCH ein und zeigen anhand von Beispielen, wann sie besonders hilfreich ist.
Die Grundlagen von INDEX-MATCH
In der einfachsten Form lässt sich INDEX-MATCH wie VLOOKUP einsetzen, um einfache vertikale Suchen anhand eines gemeinsamen Schlüssels durchzuführen. Die Grundstruktur der Formel sieht so aus:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: Der Bereich der Spalte, aus der du den Wert zurückgeben möchtest.
- lookup_value: Der Wert, nach dem du suchst (der Schlüssel).
- lookup_column_range: Der Spaltenbereich, der die zu matchenden Schlüssel enthält.
- match_type: Entweder 0 oder 1. 0 steht für exakte Übereinstimmung, 1 für ungefähre Übereinstimmung. Entspricht dem FALSE/TRUE-Flag in VLOOKUP.
Schauen wir uns nun anhand zweier Beispieltabel len an, wie INDEX-MATCH funktioniert. Die erste Tabelle enthält Namen sowie eindeutige Schlüssel. Die zweite Tabelle enthält dieselben Schlüssel (nicht zwingend in derselben Reihenfolge) und Beschreibungen. Unsere Aufgabe ist es, die Beschreibungen aus der zweiten Tabelle per INDEX-MATCH zu den Namen in der ersten Tabelle hinzuzufügen. Die Tabellen siehst du hier:
 |
 |
Wie du siehst, fehlt in der ersten Tabelle die Beschreibung. Verbinden wir nun den Namen mit seiner Beschreibung per INDEX-MATCH:
Ganz einfach, oder? Wie im Video zu sehen, wählst du zuerst im INDEX-Teil den column_range aus, der die gewünschten Daten enthält. Hier ist das die Spalte Description (M2:M8). Im zweiten Schritt wählst du in MATCH den lookup_value, also die Zelle B2. Anschließend fügst du in MATCH den lookup_column_range (L2:L8) hinzu und setzt den match_type auf 0, da wir eine exakte Übereinstimmung des lookup_value wollen. Danach nur noch ENTER drücken und die Formel nach unten ziehen – schon sind die Namen mit den Beschreibungen verknüpft.
INDEX-MATCH kann – wie VLOOKUP – auch Tabellen auf verschiedenen Blättern verbinden. Ein Beispielvideo findest du hier:
Hinweis: In den Videos siehst du, dass die gewählten Bereiche häufig mit $-Zeichen fixiert sind. So bleiben die Bereiche gleich, wenn du die Formeln vertikal oder horizontal ziehst. Diese Schreibweise nutze ich im gesamten Tutorial.
Bis hierhin verhält sich alles sehr ähnlich wie ein einfaches VLOOKUP, nur mit etwas mehr Aufwand. Wie eingangs erwähnt, hat der Weg über INDEX-MATCH jedoch mehrere Vorteile. Einer der wichtigsten sind dynamische Spaltenbezüge. Du kannst also Spalten in dem Bereich, in dem du suchst, hinzufügen, ohne die Suche selbst zu beeinträchtigen.
Fügen wir in unserem Beispiel eine neue Spalte ein und verwenden VLOOKUP, müssten wir die Formel in der linken Tabelle anpassen, damit sie wieder auf die Beschreibung verweist, die nun Spalte 3 wäre. Mit INDEX-MATCH und dynamischen Spaltenbezügen ist das nicht nötig. Der Unterschied wird im folgenden Video deutlich:
Das Beispiel zeigt, dass sich die Spalte mit INDEX-MATCH automatisch anpasst, wenn die neue Spalte mit den Raumschifftypen eingefügt wird, während die Spalte mit VLOOKUP das nicht tut. Dieser Vorteil spielt besonders bei großen Excel-Tabellen, etwa in Umsatzberichten, seine Stärke aus. Stell dir vor, mehrere Excel-Dashboards greifen auf eine gemeinsame Datentabelle zu. Nutzt du VLOOKUP und musst eines Tages in der Mitte eine neue Spalte einfügen, müsstest du alle VLOOKUP-Aufrufe in sämtlichen Dashboards anpassen. Hast du die Dashboards hingegen mit INDEX-MATCH aufgebaut, bleibt dir dieser Aufwand erspart.
Erweitertes INDEX-MATCH: Groß-/Kleinschreibung beachten
Dynamische Spaltenbezüge sind sehr nützlich, doch INDEX-MATCH kann noch mehr. So lässt sich die Basissyntax erweitern, um Groß-/Kleinschreibung zu berücksichtigen. Dazu fügen wir in MATCH eine EXACT-Funktion ein. Die detaillierte Syntax sieht so aus:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: Der relevante Tabellenbereich, der sowohl die Suchspalte als auch die gewünschte Zielspalte enthält.
- lookup_value: Der Wert, den du nachschlagen möchtest (der Schlüssel)
- lookup_column_range: Der Bereich der Spalte, die die zu matchenden Schlüssel enthält
- match_type: Entweder 0 oder 1. 0 steht für exakte, 1 für ungefähre Übereinstimmung. Entspricht dem FALSE/TRUE-Flag in VLOOKUP
- desired_column_number: Entspricht der Spaltenindexnummer in VLOOKUP. Das ist die Indexnummer der Spalte mit den gewünschten Daten.
Solche case-sensitiven Suchen sind hilfreich, wenn Schlüssel Buchstaben enthalten, die sich nur durch Groß- oder Kleinschreibung unterscheiden (z. B. zwei unterschiedliche Keys: "00567UUp" und "00567UUP"). Solche Situationen findet man zum Beispiel im weit verbreiteten Salesforce-CRM beim Abgleich von Account- oder Opportunity-IDs. Im folgenden Video nutzen wir eine angepasste Version unserer Beispieltabel len, um die Funktionsweise zu zeigen.
Hinweis: Die obige Funktionskombination steht in geschweiften Klammern. Das kennzeichnet eine Matrixformel, die mit Strg+Umschalt+Eingabe abgeschlossen werden muss.
Die Syntax ist etwas lang, aber das Ergebnis ist wie erwartet: Die Werte werden wie bei normalem INDEX-MATCH nachgeschlagen, nur eben unter Berücksichtigung der Groß-/Kleinschreibung. Den entscheidenden Part übernimmt EXACT: Die Funktion liefert ein Array aus TRUE und FALSE. An den Positionen mit TRUE liegt eine exakt gleiche Schreibweise vor. MATCH ermittelt anschließend die Position des ersten TRUE.
Wichtig: Du musst die Formel mit Strg+Umschalt+Eingabe bestätigen, sonst erhältst du einen #N/A-Fehler, der schwer zu finden sein kann.
Erweitertes INDEX-MATCH: Abgleich nach mehreren Kriterien
Case-sensitive Suchen in Excel sind schon cool, aber noch stärker ist der Abgleich nach mehreren Kriterien. Mehrere Bedingungen zu berücksichtigen, kennt man von Funktionen wie COUNTIFS oder SUMIFS – bei Nachschlagefunktionen ist es eher ungewöhnlich, kann aber sehr hilfreich sein. Beispiel: In einer HR-Datei möchtest du das Gehalt einer Person anhand von Vor- UND Nachname nachschlagen. Nimmst du nur eine der beiden Spalten, könntest du das falsche Gehalt erwischen, weil es mehrere Personen mit demselben Vor- oder Nachnamen gibt. Mit beiden Kriterien zusammen triffst du viel wahrscheinlicher die richtige Person.
So sieht die Struktur von INDEX-MATCH mit mehreren Kriterien aus:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
Im Wesentlichen enthält die Formel dieselben Bausteine wie das normale INDEX-MATCH, kann aber nun $n$ Suchwerte mit jeweils passendem Suchbereich berücksichtigen. Falls du die Komponenten auffrischen möchtest, scrolle zur Basissyntax oben. Der Grund für die "1" in MATCH: Die Formel erzeugt Arrays aus 1 und 0, die die Übereinstimmungen für jedes Kriterium repräsentieren. MATCH liefert dann die Position der ersten 1.
Wie bei der case-sensitiven Variante steht auch diese Formel in geschweiften Klammern. Du musst sie als Matrixformel mit Strg+Umschalt+Eingabe abschließen.
Im folgenden Video wählen wir Raumschiffe aus, die einer bestimmten Spezies angehören und eine vorgegebene maximale Warp-Geschwindigkeit haben:
Voilà! Jetzt kannst du nach mehreren Schlüsseln abgleichen. In diesem kleinen Beispiel nutze ich nur zwei Kriterien – den Key und die maximale Warp-Geschwindigkeit –, theoretisch kannst du aber beliebig weitere Bedingungen ergänzen. Zurück zum HR-Beispiel: Du könntest zusätzlich die Stellenbezeichnung heranziehen, um das Risiko eines falschen Gehalts weiter zu senken.
Fazit
Glückwunsch! Du kennst nun INDEX-MATCH und weißt, was sich damit jenseits eines einfachen vertikalen Nachschlags alles anstellen lässt. Wenn du irgendwann komplexe Excel-Reports betreust, wird diese Funktionskombination vermutlich dein bester Freund. Wie viele andere habe ich in der Schule VLOOKUP gelernt. Als ich Excel ernsthaft einsetzte – etwa für case-sensitive Nachschläge von Salesforce-Account-IDs –, merkte ich jedoch, dass INDEX-MATCH auf lange Sicht oft die bessere Wahl ist, auch wenn das Schreiben der Formel etwas aufwendiger und fehleranfälliger sein kann. Probier es beim nächsten vertikalen Nachschlag in Excel aus – vielleicht geht es dir dann wie mir.
Wenn du noch tiefer einsteigen willst, schau dir gerne den Data Analysis with Spreadsheets Course von DataCamp an.