M365Con 2027 – die kostenlose Online-Konferenz · 11–22 Jan 2027 · 120 sessions m365con.net

Blog · Mirko Peters

INDEX MATCH in Excel: Formel, Beispiele und häufige Fehler

Von · · Aktualisiert

INDEX MATCH in Excel erklärt: die Formel, die Funktionsweise jeder einzelnen Funktion, Suchen nach links und in zwei Richtungen, mehrere Kriterien und häufige Fehler, dazu ein Vergleich mit SVERWEIS.

Automatisch mit KI aus dem Originalbeitrag übersetzt. Englisches Original lesen

Kopfgrafik: drei zentrale Punkte zu INDEX-MATCH-Suchen in Excel.

INDEX MATCH in Excel ist eine Suchformel aus zwei Funktionen: =INDEX(Ergebnisbereich;VERGLEICH(Suchwert;Suchbereich;0)). VERGLEICH ermittelt, wo Ihr Wert in einer Liste steht, und INDEX gibt das Element an derselben Position in einer anderen Liste zurück. Dieser Artikel richtet sich an Excel-Anwender, die SVERWEIS-Formeln schreiben und an deren Grenzen stoßen, oder die eine Arbeitsmappe mit vielen INDEX-MATCH-Formeln übernommen haben und diese verstehen möchten. Sie sehen jede Funktion für sich, wie beide zusammenarbeiten, wie Sie nach links suchen, eine Suche in zwei Richtungen durchführen und zwei Kriterien verwenden und was die Fehler bedeuten.

Das Wichtigste in Kürze

  • INDEX MATCH besteht aus zwei Funktionen in einer Formel: =INDEX(Ergebnisbereich;VERGLEICH(Suchwert;Suchbereich;0)). VERGLEICH ermittelt die Position, und INDEX gibt das Element an dieser Position zurück.
  • Verwenden Sie als dritten Parameter von VERGLEICH die 0 für eine genaue Übereinstimmung. Ohne Angabe, also mit dem Standardwert 1, geht VERGLEICH davon aus, dass die Daten aufsteigend sortiert sind.
  • Da Suchbereich und Ergebnisbereich getrennt sind, kann INDEX MATCH links von der Suchspalte suchen. SVERWEIS kann das nicht.
  • Bei zwei Bedingungen fügen Sie entweder die Spalten in einer Hilfsspalte zusammen oder verwenden eine Formel, die zwei Vergleiche innerhalb von VERGLEICH multipliziert.
  • Umschließen Sie die Formel mit WENN.NV, um #NV durch eine Meldung zu ersetzen. Andere Fehler werden weiterhin angezeigt, sodass echte Fehler nicht verborgen bleiben.

INDEX MATCH finden Sie in vielen älteren Arbeitsmappen, denn die Kombination funktionierte in jeder Version von Excel, lange bevor XLOOKUP existierte. Sie funktioniert auch heute noch, und genau das ist der Hauptgrund, sie zu lernen. Die Details unten stammen von den Microsoft-Seiten zu VERGLEICH, INDEX und SVERWEIS.

Was INDEX macht

INDEX gibt einen Wert von einer Position innerhalb eines Bereichs zurück. Die Microsoft-Seite beschreibt die Matrixform als INDEX(Matrix;Zeile_Nr;[Spalte_Nr]).

  • Matrix ist der Bereich, aus dem der Wert entnommen wird.
  • Zeile_Nr ist die Zeile innerhalb dieses Bereichs.
  • Spalte_Nr ist die Spalte innerhalb dieses Bereichs. Hat der Bereich nur eine Spalte, können Sie sie weglassen.

Die Zählung beginnt in der oberen linken Zelle des Bereichs, nicht im Tabellenblatt. Das Microsoft-Beispiel verwendet einen Block mit Apples und Bananas in der ersten Spalte sowie Lemons und Pears in der zweiten. =INDEX(A2:B3;2;2) gibt Pears zurück, und =INDEX(A2:B3;2;1) gibt Bananas zurück.

Zwei Regeln von derselben Seite sind wichtig. Liegt Zeile_Nr oder Spalte_Nr außerhalb des Bereichs, gibt INDEX einen #BEZUG!-Fehler zurück. Setzen Sie Zeile_Nr oder Spalte_Nr auf 0, gibt INDEX die gesamte Spalte bzw. Zeile zurück. In aktuellen Versionen von Microsoft 365 bestätigen Sie die Formel wie gewohnt mit der Eingabetaste. In älteren Versionen geben Sie sie als Matrixformel mit Strg+Umschalt+Eingabe ein.

Allein benötigt INDEX die Position bereits. Hier kommt VERGLEICH ins Spiel.

Was VERGLEICH macht

VERGLEICH durchsucht einen Bereich und gibt eine Position zurück. Microsoft gibt die Syntax als VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp]) an. Die Funktion gibt nicht das Element selbst zurück, sondern nur dessen relative Position im Bereich.

Das dritte Argument bestimmt die Art des Vergleichs:

  • 0 findet die erste exakte Übereinstimmung. Der Bereich kann in beliebiger Reihenfolge vorliegen. Diese Option werden Sie am häufigsten verwenden.
  • 1, das auch der Standardwert ist, wenn Sie das Argument weglassen, findet den größten Wert, der kleiner oder gleich dem Suchwert ist. Der Bereich muss aufsteigend sortiert sein.
  • -1 findet den kleinsten Wert, der größer oder gleich dem Suchwert ist. Der Bereich muss absteigend sortiert sein.

Die Beispiele von Microsoft verwenden die Werte 25, 38, 40 und 41 in B2:B5. =MATCH(39,B2:B5,1) liefert 2, die Position von 38, dem nächstkleineren Wert. =MATCH(41,B2:B5,0) liefert 4. Und =MATCH(40,B2:B5,-1) liefert #NV, weil diese Werte nicht absteigend sortiert sind.

Ein paar weitere Verhaltensweisen aus der Seite: Der Textvergleich ignoriert die Groß- und Kleinschreibung, also sind „apple“ und „APPLE“ identisch. Bei Vergleichstyp 0 und einem Text als Suchwert steht ? für ein beliebiges einzelnes Zeichen und * für eine beliebige Zeichenfolge. Eine Tilde davor sucht nach dem Zeichen selbst. Findet MATCH nichts, liefert es #NV.

Der Standardwert 1 führt häufig zu Fehlern. Lassen Sie das dritte Argument weg, geht MATCH stillschweigend von sortierten Daten aus und kann ohne Fehlermeldung eine falsche Position liefern. Bei fast allen Suchvorgängen nach IDs, Namen oder Codes sollten Sie die 0 eingeben.

Alles zusammen

Das Muster ist immer gleich: MATCH steht innerhalb von INDEX und liefert die Zeilennummer.

Ablauf in drei Schritten: MATCH findet die Position, INDEX gibt den Wert zurück.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Angenommen, ein Blatt enthält Mitarbeiter-IDs in A2:A100, Namen in B2:B100 und Abteilungen in C2:C100. Sie geben in F2 eine ID ein und möchten die Abteilung erfahren.

  1. Klicken Sie auf die Zelle für das Ergebnis, zum Beispiel G2.
  2. Geben Sie =INDEX(C2:C100, ein, um festzulegen, woher die Antwort stammt.
  3. Ergänzen Sie MATCH(F2,A2:A100,0)), um festzulegen, wo die ID zu finden ist.
  4. Drücken Sie Eingabe.

Die fertige Formel lautet =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH findet F2 in A2:A100 und liefert zum Beispiel 14. INDEX gibt dann den 14. Eintrag in C2:C100 zurück.

Halten Sie die Bereiche gleich lang und mit derselben Startzeile. Durchsucht MATCH A2:A100, während INDEX C3:C101 liest, zeigt die Position auf die falsche Zeile, und das Ergebnis ist stillschweigend falsch. Verwenden Sie für beide dieselben Start- und Endzeilen und fixieren Sie sie mit Dollarzeichen, $A$2:$A$100, wenn Sie die Formel nach unten kopieren wollen.

Nach links suchen und der Unterschied zu VLOOKUP

Die VLOOKUP-Seite von Microsoft beschreibt die Syntax als VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Sie besagt, dass der Suchwert in der ersten Spalte des Bereichs stehen muss, und dass das Weglassen von range_lookup eine Annäherungssuche ergibt, die sortierte Daten voraussetzt. Die Seite empfiehlt außerdem, XLOOKUP zu verwenden.

Hier dieselbe Aufgabe auf beide Arten, mit IDs in Spalte A und Namen in Spalte B:

  • =VLOOKUP(F2,A2:B100,2,FALSE)
  • =INDEX(B2:B100,MATCH(F2,A2:A100,0))

INDEX MATCH hat zwei echte Vorteile. Erstens kann es nach links suchen. Stehen die Namen in Spalte B und möchten Sie die ID aus Spalte A zurück, tauschen Sie einfach die Bereiche: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP kann das nicht, weil der Suchwert in der ersten Spalte stehen muss. Zweitens verweist es direkt auf die Ergebnisspalte, statt zu zählen. Bei VLOOKUP bedeutet die 2 in der Formel „zweite Spalte des Bereichs“. Fügen Sie innerhalb des Bereichs eine Spalte ein, zeigt die Zahl auf eine andere Spalte. Bei INDEX MATCH passt sich der Verweis wie jeder andere Verweis an.

Sie werden auch lesen, dass INDEX MATCH schneller sei. Die Seiten von Microsoft stellen diese Behauptung nicht auf, und ein Unterschied spielt nur in sehr großen Arbeitsmappen eine Rolle. Wählen Sie es daher nicht wegen der Geschwindigkeit, sondern wegen der Flexibilität.

Zweidimensionale Suchen

Eine zweidimensionale Suche findet den Wert, an dem sich eine Zeile und eine Spalte schneiden, zum Beispiel den Umsatz eines Produkts in einem Monat. Verwenden Sie zwei MATCH-Funktionen, eine für die Zeile und eine für die Spalte:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Hier enthält B2:M20 die Zahlen, A2:A20 die Produktnamen, B1:M1 die Monatsüberschriften, und in P1 und Q1 stehen das gewünschte Produkt und der gewünschte Monat. Die erste MATCH-Funktion liefert die Zeile, die zweite die Spalte, und INDEX gibt die Zahl zurück, an der sich beide treffen. Beachten Sie, dass der Rückgabebereich in B2 beginnt und nicht in A1, damit die Positionen zu den Beschriftungsbereichen passen: Beide MATCH-Bereiche beginnen ebenfalls in derselben Zeile bzw. Spalte wie die Zahlen.

Abgleich anhand zweier Kriterien

MATCH nimmt genau einen Suchwert entgegen. Wenn ein Datensatz nur durch zwei Felder eindeutig bestimmt ist, etwa Kategorie und Status, haben Sie zwei Möglichkeiten.

Eine Hilfsspalte. Fügen Sie eine Spalte hinzu, die die beiden Felder verbindet, zum Beispiel =A2&"|"&B2 in D2, und kopieren Sie die Formel nach unten. Suchen Sie dann nach dem verbundenen Text:

Vergleich der Suchansätze SVERWEIS und INDEX MATCH.

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

Das Trennzeichen ist wichtig. Ohne es würden "AB" plus "C" und "A" plus "BC" beide zu "ABC". Die Hilfsspalte ist leicht zu lesen und zu prüfen und funktioniert in jeder Version.

Eine Matrixformel. Ohne Hilfsspalte können Sie zwei Vergleiche miteinander multiplizieren:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Jeder Vergleich liefert WAHR oder FALSCH. Durch die Multiplikation entsteht nur dort eine 1, wo beide Bedingungen wahr sind, und MATCH sucht nach der ersten 1. Diese Vorgehensweise steht nicht auf Microsofts Seiten zu INDEX und VERGLEICH, testen Sie sie daher an einem kleinen Bereich. In älteren Excel-Versionen müssen Sie die Formel unter Umständen mit Strg+Umschalt+Eingabe abschließen, so wie Microsoft es für Matrixergebnisse von INDEX beschreibt. In aktuellen Versionen von Microsoft 365 genügt die Eingabetaste.

Platzhalter und nächstliegende Werte

Mit dem Vergleichstyp 0 und Text akzeptiert MATCH Platzhalter. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) gibt das Element neben dem ersten Eintrag zurück, der mit „Jan“ beginnt. Das ist nützlich für Teilcodes. Halten Sie das Muster präzise, denn MATCH stoppt beim ersten Treffer.

Für Stufen wie Steuerklassen oder Provisionsstaffeln verwenden Sie bewusst den Standardvergleichstyp 1, wobei die Schwellenwerte aufsteigend sortiert sein müssen. Tragen Sie 0, 1000 und 5000 in A2:A4 ein und die Sätze in B2:B4, dann gibt =INDEX(B2:B4,MATCH(E2,A2:A4,1)) den Satz der größten Schwelle zurück, die E2 nicht überschreitet. Dies ist einer der wenigen Fälle, in denen die sortierte Standardeinstellung die richtige Wahl ist.

Fehler beheben

  • #NV. MATCH hat nichts gefunden. Suchen Sie nach Tippfehlern, nachgestellten Leerzeichen und Zahlen, die als Text gespeichert sind, denn „100“ und 100 sind unterschiedlich. Prüfen Sie, ob der Vergleichstyp 0 ist, und ob bei den Vergleichstypen 1 oder -1 die Daten tatsächlich sortiert sind.
  • #BEZUG!. INDEX wurde nach einer Zeile oder Spalte außerhalb des Bereichs gefragt. Das passiert auch, wenn Sie eine Zeile oder Spalte löschen, die die Formel verwendet. Prüfen Sie, ob der MATCH-Bereich und der INDEX-Bereich dieselbe Größe haben.
  • Ein falsches Ergebnis ohne Fehlermeldung. Meist fehlt die 0 in MATCH, oder die Bereiche beginnen in unterschiedlichen Zeilen.
  • Ein falsches Ergebnis nach dem Kopieren nach unten. Die Bereiche haben sich verschoben, weil sie nicht fixiert waren. Ergänzen Sie $ in den Bereichen.

Um einen Fehler durch eine Meldung zu ersetzen, gibt die Funktion IFNA von Microsoft einen Wert Ihrer Wahl zurück, wenn eine Formel #NV liefert, und gibt alle anderen Fehler unverändert weiter:

=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Nicht gefunden")

Verwenden Sie bei Suchvorgängen IFNA statt IFERROR. IFERROR blendet jeden Fehler aus, auch einen #BEZUG!, der durch einen defekten Bereich entsteht. Dadurch sieht ein echter Fehler wie ein gewöhnliches „Nicht gefunden“ aus. IFNA erfasst nur den Fall, mit dem Sie rechnen. Microsoft führt IFNA für Excel 2016 und neuer auf.

INDEX und VERGLEICH oder XLOOKUP

Wenn alle, die Ihre Datei öffnen, eine aktuelle Version von Microsoft 365 nutzen, erledigt XLOOKUP dieselbe Aufgabe mit einer einzigen Funktion und einer integrierten Meldung für nicht gefundene Werte. Wir behandeln es in unserem Leitfaden zu XLOOKUP in Excel. Die eigene XLOOKUP-Seite von Microsoft ist jedoch nicht eindeutig, was Excel 2016 und 2019 betrifft, und sie führt Excel für das Web nicht auf. INDEX und VERGLEICH funktionieren in all diesen Versionen. Die Wahl ist also eine praktische: Verwenden Sie XLOOKUP in Dateien für aktuelle Versionen und INDEX und VERGLEICH, wenn die Datei an Personen mit älteren Versionen weitergegeben wird oder wenn Sie eine Arbeitsmappe pflegen, die sie bereits verwendet.

Häufige Fehler

  • Die 0 in MATCH wegzulassen und sich auf den Standardwert zu verlassen.
  • Bereiche unterschiedlicher Größe oder mit unterschiedlichen Startzeilen für INDEX und MATCH zu verwenden.
  • Zu vergessen, Bereiche mit $ zu fixieren, bevor Sie die Formel nach unten kopieren.
  • Kriterien ohne Trennzeichen in einer Hilfsspalte zu verbinden.
  • Alles in IFERROR zu packen, sodass fehlerhafte Formeln wie fehlende Daten aussehen.
  • INDEX MATCH gibt alle Treffer zurück? Nein, nur den ersten.

Verwandte Themen zum Vertiefen

  • XLOOKUP. Die neuere Suchfunktion, mit integrierter Suchrichtung und Argument für den Fall, dass nichts gefunden wird.
  • Mehrere Ergebnisse auf einmal. INDEX MATCH gibt nur den ersten Treffer zurück. Für alle Treffer in Versionen mit dieser Funktion ist die Funktion FILTER das richtige Werkzeug.
  • Tabellen und benannte Bereiche. Strukturierte Verweise und Namen machen Suchen leichter lesbar und lassen den Bereich mit Ihren Daten mitwachsen.
  • Leistung bei großen Tabellenblättern. Verwenden Sie begrenzte Bereiche statt ganzer Spalten und halten Sie die Bereiche möglichst eng.
  • Suchen mit Berücksichtigung der Groß- und Kleinschreibung. MATCH ignoriert die Groß- und Kleinschreibung. Für eine Suche, die diese berücksichtigt, brauchen Sie daher einen anderen Ansatz. Testen Sie jede Umgehungslösung, bevor Sie sich darauf verlassen.

Nächster Schritt

Schreiben Sie eine Ihrer VLOOKUP-Formeln als INDEX MATCH um und vergleichen Sie das Ergebnis anhand einiger bekannter Werte. Probieren Sie danach dieselbe Formel mit einer Suche nach links aus. Wenn Sie beides getestet haben, wissen Sie, wann INDEX MATCH in Excel das bessere Werkzeug für die Aufgabe ist.

Quellen

FAQ

Wie funktioniert INDEX MATCH in Excel?

VERGLEICH sucht in einem Bereich nach Ihrem Suchwert und gibt dessen Position zurück, zum Beispiel 5 für das fünfte Element. INDEX verwendet diese Zahl dann, um das Element an derselben Position in einem anderen Bereich zurückzugeben. Zusammen ergibt sich =INDEX(C2:C100;VERGLEICH(F2;A2:A100;0)). Die Formel sucht F2 in Spalte A und gibt den Wert aus Spalte C zurück.

Ist INDEX MATCH besser als SVERWEIS?

Es ist flexibler. SVERWEIS verlangt, dass der Suchwert in der ersten Spalte seines Bereichs steht, und zählt Spalten über eine Nummer. INDEX MATCH arbeitet dagegen mit zwei getrennten Bereichen. Dadurch kann die Suche nach links erfolgen, und eingefügte Spalten wirken sich nicht aus. Für eine einfache Suche nach rechts funktionieren beide. XLOOKUP ist in neueren Versionen eine dritte Option.

Warum gibt INDEX MATCH #NV zurück?

VERGLEICH gibt #NV zurück, wenn kein Treffer gefunden wird. Prüfen Sie auf Tippfehler, zusätzliche Leerzeichen, als Text gespeicherte Zahlen und den Vergleichstyp. Verwenden Sie 0 für eine genaue Übereinstimmung. Beim Standardwert 1 muss der Suchbereich aufsteigend sortiert sein, bei -1 absteigend.

Entitäten in diesem Artikel

Themen dieses Artikels:

Microsoft Excel – Analyse- und BI-Werkzeug von Microsoft

Verwandte Artikel

Weitere Artikel zu Microsoft Excel:

RSS