Indexabgleich in Excel: Eine bessere Art, Daten zu suchen

INDEX MATCHEine Kombination aus den beiden Excel-Funktionen „ INDEX() “ und „ MATCH() “ ist eine Alternative zur häufig verwendeten Funktion „ VLOOKUP() “.

In diesem Handbuch zeige ich dir, wie „ INDEX() ” und „ MATCH() ” einzeln und zusammen funktionieren, wie du vertikale und horizontale Lookups machst und welche Best Practices es für die Verwendung von „ INDEX MATCH ” gibt, und zwar mit praktischen Beispielen.

Was ist INDEX MATCH in Excel?

INDEX MATCH ist eine kurze Art, um zu sagen, dass die beiden Funktionen ineinander stecken. Das ist so ähnlich wie das Konzept der „ INDEX(MATCH()) “. Schauen wir mal, wie beide einzeln und zusammen funktionieren.

INDEX()-Funktion in Excel

Die Funktion „ INDEX() “ in Excel gibt einen Wert oder einen Verweis auf einen Wert aus einem Bereich oder einer Tabelle zurück. Du kannst es auf zwei Arten benutzen: als Array und als Referenz.

Array-Form

Du kannst die Array-Form verwenden, wenn du mit einer festen Tabelle oder einem festen Array arbeitest. In diesem Format holt „ INDEX() “ einen Wert raus, der auf der von dir angegebenen Zeilen- und Spaltennummer basiert. Die Syntax ist:

INDEX(array, row_num, [column_num])

Schauen wir uns diese Argumente mal genauer an:

• array (erforderlich) ist der Bereich oder das Array von Werten, mit denen du arbeitest.

• row_num (erforderlich) ist die Zeilennummer, aus der ein Wert zurückgegeben werden soll.

• column_num (optional) ist die Spaltennummer, aus der ein Wert zurückgegeben werden soll.

Wir müssen mindestens eine der beiden Optionen angeben, entweder „ row_num “ oder „ column_num “, damit Excel den Wert finden kann.

Hier sind aber ein paar kurze Hinweise, die du bei der Arbeit mit dieser Syntax beachten solltest:

• Wenn das Array nur eine Zeile oder Spalte hat, kannst du „ row_num “ oder „ column_num “ einfach weglassen.

• Wenn sowohl „ row_num “ als auch „ column_num “ enthalten sind, gibt „ INDEX “ den Wert zurück, bei dem sie sich überschneiden.

• Wenn du „ row_num “ oder „ column_num “ auf „ 0 “ setzt, gibt Excel die ganze Spalte oder Zeile zurück (du musst das als Array-Formel eingeben, um das Ergebnis zu sehen).

Referenzformular

Du kannst das Referenzformular verwenden, wenn du mit mehreren Bereichen arbeitest und angeben musst, welcher Bereich betrachtet werden soll.

Die Syntax ist:

INDEX(reference, row_num, [column_num], [area_num])

Schauen wir uns diese Argumente mal genauer an:

• reference (erforderlich) wird verwendet, um auf einen oder mehrere Zellbereiche zu verweisen.

• row_num (erforderlich) ist die Zeilennummer im ausgewählten Bereich.

• column_num (optional) ist die Spaltennummer im ausgewählten Bereich.

• area_num (optional) sagt Excel, welchen Bereich es nehmen soll, wenn es mehrere gibt.

Hier sind noch ein paar wichtige Punkte, die du beachten solltest:

• Wenn „ area_num “ weggelassen wird, nimmt Excel den ersten Bereich als Standard.

• In diesem Formular kannst du Bereiche aus verschiedenen Blättern nicht mischen, sonst kommt eine Fehlermeldung.

• Wenn sowohl Zeile als auch Spalte weggelassen werden, wird der gesamte angegebene Bereich zurückgegeben.

MATCH()-Funktion in Excel

Die MATCH() Funktion hilft dir, die Position eines Werts in einer Zeile oder Spalte zu finden. Anstatt den Wert selbst zurückzugeben, wird dir gesagt, wo er sich in der Liste befindet.

Die Syntax ist:

MATCH(lookup_value, lookup_array, [match_type])

Schauen wir uns diese Argumente mal genauer an:

• lookup_value (erforderlich) ist der Wert, den du suchst. Das kann eine Zahl, Text, ein logischer Wert (TRUE/FALSE) oder ein Verweis auf eine Zelle sein.

• lookup_array (erforderlich) ist der Bereich der Zellen, die Excel durchsucht.

• match_type (optional) legt fest, wie Excel nach Übereinstimmungen sucht. Die Standardeinstellung ist „ 1. “ Hier findet „ 1 “ den größten Wert, der kleiner oder gleich „ lookup_value “ ist (aufsteigende Reihenfolge erforderlich). „ 0 “ findet die exakte Übereinstimmung (beliebige Reihenfolge). „ -1 “ findet den kleinsten Wert, der größer oder gleich „ lookup_value “ ist (absteigende Reihenfolge erforderlich).

Die Funktion „ MATCH “ unterscheidet nicht zwischen Groß- und Kleinschreibung, also werden Groß- und Kleinbuchstaben gleich behandelt. Wenn du mit Text arbeitest und Platzhalterzeichen wie „ * “ (beliebige Anzahl von Zeichen) oder „ ? “ (ein einzelnes Zeichen) verwenden möchtest, musst du „ match_type “ auf „ 0 “ setzen. Weil das der einzige Modus ist, der die Suche mit Platzhaltern unterstützt.

Wie funktionieren INDEX() und MATCH() zusammen in Excel?

Eine der häufigsten Anwendungen von „ MATCH() “ ist die Kombination mit der Funktion „ INDEX() “. Während „ MATCH() “ die Position eines Werts findet, holt „ INDEX() “ den Wert an dieser Position. 

Wenn du „ MATCH() “ in „ INDEX() “ einbaust, bekommst du eine Formel, die dynamisch und robust ist, wenn sich Spalten oder Zeilen im Laufe der Zeit verschieben. 

Ich hab zum Beispiel einen Beispiel-Datensatz. Und wenn ich wissen will, was in derSpalte B unter„ ” dasselbe ist wie in der Spalte A unter „Banana”, kann ich „ INDEX MATCH ” so zusammen verwenden:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))

Hier findet „ MATCH("Banana", A2:A5, 0) “ die Position von „Banana“ in Spalte A und „ INDEX(B2:B5, ...) “ gibt den Wert aus derselben Zeile in Spalte B zurück.

INDEX MATCH im Vergleich zu VLOOKUP(): Welches solltest du nehmen?

INDEX MATCH kann Aufgaben erledigen, die VLOOKUP() nicht kann. Die Entscheidung hängt aber davon ab, wie flexibel und leistungsstark deine Excel-Tabellen sein sollen. Vergleichen wir sie mal.

Flexibilität der Spalten und Datenintegrität

Einer der größten Nachteile von „ VLOOKUP() ” ist, dass man sich auf feste Spaltennummern verlassen muss.auf feste Spaltenanzahl. Wenn du Spalten einfügst oder verschiebst, kann deine Formel kaputtgehen.

Nimm mal dieses Beispiel:

Wenn eine neue Spalte zwischen Spalte A und Spalte B, verschiebt sich die dritte Spalte und die Formel liefert falsche Ergebnisse, wie du in der Abbildung unten sehen kannst.

=VLOOKUP("Apple", A2:C5, 2, FALSE)

INDEX MATCH vermeidet dieses Problem, indem es mit festen Bereichen statt mit fest programmierten Spaltenpositionen arbeitet:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))

Selbst wenn Spalten neu angeordnet werden, bleibt diese Formel wirksam, was sie zuverlässiger und einfacher zu pflegen macht. Hier wird eine Spalte vor Spalte B eingefügt; die Formel wird automatisch aktualisiert und die Ergebnisse bleiben korrekt. Das ist die Magie eines „ INDEX MATCH “.

Suchrichtung und Flexibilität

VLOOKUP() kann nur von links nach rechts suchen. Das heißt, deine Suchspalte muss immer die erste in deinem Bereich sein. Wenn der Wert, den du brauchst, links in der Suchspalte steht, kann „ VLOOKUP() “ ihn nicht finden.

Wenn wir aber nach dem Namen der Frucht suchen, um die Farbe rechts zu finden, klappt das hier. Wenn wir zum Beispiel die Farbe einer Banane aus unserer Liste suchen, kriegen wir das richtige Ergebnis: 

Mit „ INDEX MATCH “ gibt's diese Einschränkung nicht. Du kannst in jede Richtung suchen: links, rechts, oben oder unten. Es unterstützt auch horizontale Suchvorgänge, damit du deine Daten besser organisieren kannst. 

Probieren wir mal „ INDEX MATCH ” bei diesem Beispiel aus und schauen, wie es sowohl nach links als auch nach rechts suchen kann.

Für die Suche nach links kannst du die folgende Formel verwenden, die dir das richtige Ergebnis liefert: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))

Für die richtige Suche kannst du die folgende Formel verwenden, die dir das richtige Ergebnis liefert: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))

Leistung und Effizienz in Excel

VLOOKUP() durchsucht die ganze Tabelle nach einer Übereinstimmung und holt einen Wert in einem Schritt. Aber „ INDEX MATCH “ macht das in zwei Schritten: „ MATCH() “ sucht die Zeile oder Spalte und „ INDEX() “ holt den Wert raus. 

Dadurch wird es schneller und speichereffizienter, vor allem in komplizierten oder umfangreichen Arbeitsmappen.

Hier ist eine Tabelle, die dir einen besseren Überblick darüber gibt, welche Funktion welche Funktionalität unterstützt:

Wann benutzt man INDEX MATCH?

Du kannst „ INDEX MATCH ” verwenden, wenn:

• Du arbeitest mit großen Datensätzen, bei denen es auf Geschwindigkeit ankommt.
• Dein Modell ist dynamisch, Spalten können hinzugefügt oder neu angeordnet werden.
• Du musst Werte links oder in horizontalen Bereichen nachschlagen.

Wenn deine Excel-Dateien klein und einfach sind, kann „ VLOOKUP() “ vielleicht trotzdem helfen. Für fortgeschrittene Modellierung und Berichterstellung ist „ INDEX MATCH “ aber die sicherere Wahl.

So benutzt du INDEX MATCH Schritt für Schritt

Angenommen, du hast zwei Spalten: Früchte und Verkauf. Du willst den Verkaufswert für „Apple“ herausfinden.

Dazu gibst du die kombinierte Formel „ INDEX MATCH “ ein und drückst die Taste „Enter“. Enter:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Hier findet „ MATCH("Apple", A2:A6, 0) “ den Eintrag „Apple“ in der Liste „Fruits“ (Obst) im Verzeichnis„ “. Und „ INDEX(B2:B6, ...) “ nutzt diese Position, um den entsprechenden Verkaufswert aus der Spalte„ Sales“ zurückzugeben.

Du siehst, dass die Formel 12000 zurückgibt, was dem Umsatzwert von Apple entspricht. Das ist also dein Ergebnis.

INDEX MATCH-Fehler vermeiden

„ INDEX MATCH ” ist zwar eine gute Kombination, kann aber trotzdem Fehler auslösen, wenn sie nicht richtig eingerichtet ist. Hier erfährst du, wie du die häufigsten Probleme beheben und Fehler vermeiden kannst, die deine Formeln kaputt machen könnten.

#N/A-Fehler und wie man sie behebt

Der Fehler „ #N/A “ bedeutet, dass Excel den gesuchten Wert nicht finden konnte. Ein paar häufige Gründe dafür könnten sein:

• Tippfehler, zusätzliche Leerzeichen oder falscher Text (z. B. „apple” statt „apples”) "Apple")

• Kein Wert im Suchbereich

• Falsch match_type, zum Beispiel 1 statt 0 für eine exakte Übereinstimmung.

• Versteckte Zeichen (wie z. B. geschützte Leerzeichen) in den Daten

Es gibt also zwei Möglichkeiten, das Problem zu beheben:

Verwende TRIM() , um führende oder nachfolgende Leerzeichen zu entfernen:

=MATCH(TRIM("Apple"), A2:A6, 0)

Füge IFERROR() hinzu, um einen Ersatzwert anstelle einer Fehlermeldung anzuzeigen:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")

#REF!-Fehler in INDEX MATCH-Formeln

Der Fehler „ #REF! “ zeigt normalerweise auf einen defekten Verweis hin – oft wegen:

• Die Anzahl der Zeilen in den Bereichen „ INDEX() “ und „ MATCH() “ passt nicht zusammen.

• In deiner Formel verwendete Zeilen oder Spalten wurden gelöscht

• Formeln kopieren und einfügen, ohne Zellverweise zu aktualisieren

Aber es gibt ein paar einfache Möglichkeiten, das Problem zu beheben: 

• Stell sicher, dass die Bereiche „ INDEX() “ und „ MATCH() “ gleich groß sind:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

• Sperr Bereiche mit absoluten Verweisen (wie $A$2:$A$6), damit Formeln beim Kopieren und Einfügen stabil bleiben.

• Lass das Löschen von Zellen, die Formeln brauchen, lieber sein.

Langsame Leistung und große Datensätze

Wenn du mit Tausenden von Zeilen arbeitest, können nicht optimierte Formeln in der Daten INDEX MATCH Excel verlangsamen.

So kannst du das optimieren:

• Suchbereiche einschränken, anstatt ganze Spalten zu referenzieren:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))

• Vermeide volatile Funktionen wie OFFSET(), INDIRECT()und NOW() in der Nähe deiner Suchvorgänge.

• Verwende Excel-Tabellen oder benannte Bereiche, um alles übersichtlich zu halten und die Leistung zu verbessern.

Umgang mit Überlauffehlern in Excel 365

Excel 365 hat dynamische Arrays, die Ergebnisse in benachbarte Zellen „auslaufen“ lassen können. Wenn bei diesem Vorgang ein Problem auftritt, bekommst du eine #SPILL!-Fehler.

Ein paar häufige Gründe könnten sein:

• Angrenzende Zellen sind nicht leer.
• Die Formel gibt mehrere Werte in einen blockierten oder zusammengeführten Zellbereich zurück.
• Du benutzt ein dynamisches Array, wo nur ein einziger Wert gebraucht wird.

Aber diese Probleme sind machbar. So kannst du sie überwinden:

• Lösche die Zellen unterhalb oder neben der Formel.
• Trenne alle Zellen im Überlaufbereich.
• Wenn du nur einen Wert haben willst, erzwinge ein einzelnes Ergebnis mit „ @ “:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Abschließende Gedanken

Wenn du bisher auf „ VLOOKUP(), “ gesetzt hast, ist es jetzt an der Zeit, auf etwas Stabileres umzusteigen. Du kannst mit ein paar einfachen Suchvorgängen beginnen, indem du in einer Kopie deiner Arbeitsdatei „ INDEX MATCH “ eingibst. Probier mal, die neue Formel dort einzusetzen, wo Spaltenänderungen Fehler verursachen oder wo deine Datenstruktur Flexibilität braucht. Wenn du erst mal siehst, wie sauber das Programm mit diesen Fällen umgeht, wirst du kaum noch zurück wollen.