Excel INDEX und VERGLEICH: Grundlegende und fortgeschrittene Suchvorgänge

In Excel ist das präzise Abrufen bestimmter Daten oft eine wiederkehrende Herausforderung. Während die Funktionen INDEX und VERGLEICH jeweils ihre eigenen Stärken haben, entfaltet ihre Kombination ein besonders leistungsstarkes Werkzeug für die Datensuche. Gemeinsam ermöglichen sie vielfältige Suchszenarien – von einfachen horizontalen und vertikalen Abfragen bis hin zu fortgeschrittenen Techniken wie Zwei-Wege-, groß-/kleinschreibungsabhängigen und mehrkriteriellen Suchen. Im Vergleich zu SVERWEIS bietet die Kombination aus INDEX und VERGLEICH deutlich erweiterte Möglichkeiten bei der Datensuche. In diesem Tutorial erkunden wir die vielseitigen Einsatzmöglichkeiten, die diese beiden Funktionen gemeinsam eröffnen.

• INDEX-Funktion
• VERGLEICH-Funktion
• INDEX und VERGLEICH kombinieren

• Zweiseitige Suche
• Suche nach links
• Groß-/Kleinschreibung berücksichtigende Suche
• Nächste Übereinstimmung
• Suche mit mehreren Kriterien
• Suche über mehrere Spalten hinweg
• Suche nach dem ersten nicht leeren Wert
• Suche nach dem ersten numerischen Wert
• Suche nach MAX- oder MIN-Zuordnungen
• Tipp: Passen Sie Ihre eigenen #NV-Fehlermeldungen an

So verwenden Sie INDEX und VERGLEICH in Excel

Bevor wir die Funktionen INDEX und VERGLEICH einsetzen, vergewissern wir uns zunächst, wie uns diese bei der Suche nach Werten unterstützen können.

Verwendung der INDEX-Funktion in Excel

Die INDEX-Funktion in Excel gibt den Wert an einer bestimmten Position innerhalb eines festgelegten Bereichs zurück. Ihre Syntax lautet wie folgt:

• Array (erforderlich) bezieht sich auf den Bereich, aus dem der Wert zurückgegeben werden soll.
• Zeilennummer(erforderlich, sofern)Spaltennummer nicht angegeben ist) bezieht sich auf die Zeilennummer des Arrays.
• Spaltennummer(optional, aber erforderlich, wenn)Zeilennummer weggelassen wird) bezieht sich auf die Spaltennummer des Arrays.

Um beispielsweise die Punktzahl von Jeff zu ermitteln – also des 6. Schülers in der Liste –, können Sie die INDEX-Funktion wie folgt verwenden:

=INDEX(C2:C11,6)

√ Hinweis: Der Bereich C2:C11 enthält die aufgelisteten Punktzahlen, während die Zahl 6 die Prüfungspunktzahl des 6. Schülers ermittelt.

Lassen Sie uns nun einen kleinen Test durchführen: Welchen Wert liefert die Formel =INDEX(A1:C1;2) zurück? — Richtig, sie gibt Geburtsdatum zurück, also den 2. Wert in der angegebenen Zeile.

Inzwischen wissen wir, dass die INDEX-Funktion perfekt mit horizontalen oder vertikalen Bereichen arbeitet. Doch was tun, wenn wir einen Wert aus einem größeren Bereich mit mehreren Zeilen und Spalten abrufen möchten? In diesem Fall müssen wir sowohl eine Zeilennummer als auch eine Spaltennummer angeben. Um beispielsweise Jeffs Punktzahl innerhalb des gesamten Tabellenbereichs statt nur einer einzelnen Spalte zu finden, können wir seine Punktzahl mithilfe einer Zeilennummer von 6 und einer Spaltennummer von 3 im Bereich A2 bis C11 wie folgt lokalisieren:

=INDEX(A2:C11,6,3)

Bei einer wirklich großen Datenbank mit zahlreichen Zeilen und Spalten ist es jedoch kaum praktikabel, die Formel mit exakten Zeilen- und Spaltennummern zu verwenden – genau hier kommt die Kombination mit der VERGLEICH-Funktion ins Spiel.

Verwendung der VERGLEICH-Funktion in Excel

Die VERGLEICH-Funktion in Excel liefert einen numerischen Wert zurück – und zwar die Position eines bestimmten Elements innerhalb des angegebenen Bereichs. Die Syntax der VERGLEICH-Funktion lautet wie folgt:

• Suchwert (erforderlich) bezieht sich auf den Wert, der im Sucharray gesucht werden soll.
• Sucharray (erforderlich) bezeichnet den Zellbereich, in dem VERGLEICH suchen soll.
• Vergleichstyp(optional):1,0oder -1.
  • 1 (Standard): VERGLEICH findet den größten Wert, der kleiner als oder gleich dem Suchwert ist. Die Werte im Suchbereich müssen in aufsteigender Reihenfolge angeordnet sein.
  • 0: VERGLEICH findet den ersten Wert, der exakt dem Suchwert entspricht. Die Werte im Suchbereich können in beliebiger Reihenfolge vorliegen. (Wenn der Suchtyp auf 0 gesetzt ist, können Sie Platzhalterzeichen verwenden.)
  • –1 findet VERGLEICH den kleinsten Wert, der größer oder gleich dem Suchwert ist. Die Werte im Suchbereich müssen in absteigender Reihenfolge angeordnet sein.

Um beispielsweise die Position von Vera in der Spalte Namenslistezu ermitteln, können Sie die Funktion Formeln unterscheiden wie folgt verwenden:

=MATCH("Vera",A2:A11,0)

✓ Hinweis: Das Ergebnis „4“ zeigt an, dass der Name „Vera“ an vierter Stelle in der Liste steht.

Da wir nun die grundlegenden Anwendungen der INDEX- und VERGLEICH-Funktionen in Excel kennen, packen wir die Ärmel hoch und machen uns bereit, beide Funktionen zu kombinieren.

Kombination von INDEX und VERGLEICH in Excel

Im folgenden Beispiel wird veranschaulicht, wie wir die Funktionen INDEX und VERGLEICH kombinieren können:

Um Evelyns Punktzahl zu ermitteln – wobei die Prüfungspunktzahlen in der 3. Spalte stehen – können wir die VERGLEICH-Funktion nutzen, um die Zeilenposition automatisch zu bestimmen, ohne sie manuell zählen zu müssen. Anschließend verwenden wir die INDEX-Funktion, um den Wert an der Schnittstelle der ermittelten Zeile und der 3. Spalte abzurufen:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Da die Formel auf den ersten Blick etwas kompliziert wirken mag, gehen wir nun Schritt für Schritt alle ihre Bestandteile durch.

Die INDEX-Formel enthält drei Argumente:

• Zeilennummer:VERGLEICH(„Evelyn";A2:A11;0)liefert INDEX die Zeilenposition des Werts „Evelyn“ im Bereich A2:A11, nämlich5.
• Spaltennummer: 3 gibt die 3. Spalte an, in der INDEX die Punktzahl innerhalb des Arrays sucht.
• Array: A2:C11 weist INDEX an, den passenden Wert an der Schnittstelle der angegebenen Zeile und Spalte innerhalb des Bereichs von A2 bis C11 zurückzugeben. Das Ergebnis lautet schließlich 90.

In der obigen Formel haben wir einen fest codierten Wert verwendet, nämlich „Evelyn“. In der Praxis sind feste Werte jedoch unpraktisch, da sie jedes Mal angepasst werden müssten, sobald andere Daten gesucht werden – beispielsweise die Punktzahl eines anderen Schülers. In solchen Fällen können wir Zellbezüge nutzen, um dynamische Formeln zu erstellen. Daher ersetze ich in diesem Beispiel „Evelyn“ durch F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Beispiele für INDEX und Formeln unterscheiden

In diesem Abschnitt werden verschiedene Anwendungsfälle vorgestellt, in denen die Funktionen INDEX und VERGLEICH jeweils unterschiedliche Anforderungen erfüllen.

Zwei-Wege-Suche mit INDEX und VERGLEICH

Im vorherigen Beispiel kannten wir die Spaltennummer und verwendeten eine Formel, um die Zeilennummer zu ermitteln. Doch was ist, wenn wir die Spaltennummer ebenfalls nicht kennen?

In solchen Fällen können wir eine Zwei-Wege-Suche (auch Matrixsuche genannt) durchführen, indem wir zwei VERGLEICH-Funktionen verwenden: eine zur Ermittlung der Zeilennummer und eine weitere zur Bestimmung der Spaltennummer. Um beispielsweise Evelyns Punktzahl zu ermitteln, verwenden wir folgende Formel:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

Linksseitige Suche mit INDEX und VERGLEICH

Nun betrachten wir ein Szenario, in dem Sie Evelyns Klasse ermitteln müssen. Ihnen ist möglicherweise aufgefallen, dass sich die Klassenspalte links von der Namensspalte befindet – eine Konstellation, die über die Möglichkeiten der leistungsstarken Excel-Suchfunktion SVERWEIS hinausgeht.

Tatsächlich übertrifft die Kombination aus INDEX und VERGLEICH SVERWEIS gerade in der Fähigkeit, linksseitige Suchvorgänge durchzuführen.

Um Evelyns Klasse zu finden, verwenden Sie folgende Formel, um nach „Evelyn“ in B2:B11 zu suchen und den entsprechenden Wert aus A2:A11 abzurufen.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

Groß-/Kleinschreibungsabhängige Suche mit INDEX und VERGLEICH

Die VERGLEICH-Funktion unterscheidet standardmäßig nicht zwischen Groß- und Kleinschreibung. Wenn Ihre Formel jedoch zwischen Groß- und Kleinschreibung unterscheiden soll, erweitern Sie sie einfach durch die Einbindung der IDENTISCH-Funktion. Kombinieren Sie VERGLEICH mit IDENTISCH in einer INDEX-Formel, um eine groß-/kleinschreibungsabhängige Suche durchzuführen – wie im Folgenden dargestellt:

• Array bezieht sich auf den Bereich, aus dem der Wert zurückgegeben werden soll.
• Suchwert bezieht sich auf den Wert, der unter Berücksichtigung der Groß-/Kleinschreibung mit dem Sucharray verglichen werden soll.
• Sucharray bezieht sich auf den Zellbereich, in dem VERGLEICH den Suchwert vergleichen soll.

Um beispielsweise JIMMYs Prüfungspunktzahlzu ermitteln, verwenden Sie folgende Formel:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Hinweis: Dies ist eine Arrayformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss, außer in Excel 365, Excel 2021 und neueren Versionen.

Suche nach dem nächstgelegenen Wert mit INDEX und VERGLEICH

In Excel kommt es oft vor, dass Sie den nächstgelegenen oder am besten passenden Wert zu einem bestimmten Wert innerhalb eines Datensatzes ermitteln müssen – hier erweist sich die Kombination der Funktionen INDEX und VERGLEICH mit ABS und MIN als äußerst hilfreich.

• Array bezieht sich auf den Bereich, aus dem der Wert zurückgegeben werden soll.
• Sucharray bezieht sich auf den Wertebereich, in dem die nächstliegende Übereinstimmung zum Suchwert gesucht werden soll.
• Suchwert bezeichnet den Wert, für den die nächstliegende Übereinstimmung gesucht wird.

Um beispielsweise herauszufinden, wessen Punktzahl der Zahl 85 am nächsten liegt, verwenden Sie folgende Formel, um im Bereich C2:C11 nach der Punktzahl zu suchen, die 85 am nächsten kommt, und den entsprechenden Wert aus A2:A11 abzurufen.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Hinweis: Dies ist eine Arrayformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss, außer in Excel 365, Excel 2021 und neueren Versionen.

Mehrkriteriensuche mit INDEX und VERGLEICH

Um einen Wert zu finden, der mehrere Bedingungen erfüllt – also in zwei oder mehr Spalten gesucht werden muss – verwenden Sie die folgende Formel. Sie ermöglicht eine präzise Mehrkriteriensuche, indem Sie verschiedene Bedingungen über unterschiedliche Spalten festlegen und so gezielt denjenigen Wert ermitteln, der alle angegebenen Kriterien erfüllt.

√ Hinweis: Dies ist eine Arrayformel, die mit Strg + Umschalttaste + Eingabetaste eingegeben werden muss. Danach erscheinen geschweifte Klammern in der Formelleiste.

• Array bezieht sich auf den Bereich, aus dem der Wert zurückgegeben werden soll.
• (Suchwert = Sucharray) stellt eine einzelne Bedingung dar. Diese prüft, ob ein bestimmter Suchwert mit den Werten im Sucharray übereinstimmt.

Beispielsweise, um die Punktzahl von Coco aus Klasse A zu finden, deren Geburtsdatum 7/2/2008 ist, können Sie die folgende Formel verwenden:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX und VERGLEICH, um eine Suche über mehrere Spalten hinweg durchzuführen

Stellen Sie sich ein Szenario vor, in dem Sie mit mehreren Datenspalten arbeiten: Die erste Spalte dient als Schlüssel, um die Daten in den übrigen Spalten zu klassifizieren. Um die Kategorie oder Klassifizierung eines bestimmten Eintrags zu ermitteln, durchsuchen Sie die Datenspalten und verknüpfen sie mit dem entsprechenden Schlüssel in der Referenzspalte.

Beispielsweise: Wie können wir in der folgenden Tabelle den Schüler Shawn mithilfe von INDEX und VERGLEICH seiner zugehörigen Klasse zuordnen? Zwar ist dies mit einer Formel möglich, doch diese ist recht umfangreich und schwer verständlich – geschweige denn leicht zu merken oder einzugeben.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Hier kommt die Kutools für Excel-Funktion Index und Übereinstimmung mehrerer Spaltenäußerst praktisch: Sie vereinfacht diesen Vorgang und ermöglicht es Ihnen, bestimmte Einträge schnell und einfach ihren zugehörigen Kategorien zuzuordnen. Um dieses leistungsstarke Werkzeug freizuschalten und Shawn mühelos seiner Klasse zuzuordnen,laden Sie einfach das Add-In Kutools für Excel herunter und installieren Sie es, und führen Sie anschließend die folgenden Schritte aus:

1. Wählen Sie die Zielzelle aus, in der die zugehörige Klasse angezeigt werden soll.
2. Klicken Sie auf der Registerkarte Kutools auf Formelhelfer > Suchen und Verweisen > Index und Übereinstimmung mehrerer Spalten.
3. 
3. Führen Sie im erscheinenden Dialogfeld Folgendes aus:
   1. Klicken Sie auf die erste Schaltfläche neben Lookup_col, um die Spalte auszuwählen, die die Schlüsselinformation enthält, die Sie zurückgeben möchten – also die Klassennamen. (Hier können Sie nur eine einzelne Spalte auswählen.)
   2. Klicken Sie auf die zweite -Schaltfläche neben Table_rng, um die Zellen auszuwählen, deren Werte mit den Werten in der gewählten Lookup_col übereinstimmen sollen – also die Schülernamen.
   3. Klicken Sie auf die dritte Schaltfläche neben Lookup_value, um die Zelle auszuwählen, die den Namen des Schülers enthält, dessen Klasse Sie ermitteln möchten – in diesem Fall Shawn.
   4. Klicken Sie auf OK.
   5. 

Ergebnis

Kutools hat die Formel automatisch generiert, sodass Ihnen der Klassename von Shawn sofort in der Zielzelle angezeigt wird.

INDEX und VERGLEICH zur Suche nach dem ersten nicht leeren Wert

Um den ersten nicht leeren Wert aus einer Spalte oder Zeile abzurufen und dabei Fehler zu ignorieren, verwenden Sie eine Formel auf Basis der Funktionen INDEX und VERGLEICH. Möchten Sie Fehler in Ihrem Bereich hingegen nicht ignorieren, ergänzen Sie die Funktion ISTLEER.

• Ersten nicht leeren Wert in einer Spalte oder Zeile ermitteln (Fehler ignorieren):

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Ersten nicht leeren Wert in einer Spalte oder Zeile ermitteln (einschließlich Fehler):

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX und VERGLEICH zur Suche nach dem ersten numerischen Wert

Um den ersten numerischen Wert aus einer Spalte oder Zeile zu ermitteln, verwenden Sie eine Formel, die auf den Funktionen INDEX, VERGLEICH und ISTZAHL basiert.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX und VERGLEICH zur Zuordnung von MAX- oder MIN-Werten

Möchten Sie einen Wert abrufen, der dem maximalen oder minimalen Wert innerhalb eines Bereichs zugeordnet ist, kombinieren Sie einfach die Funktionen MAX oder MIN mit INDEX und VERGLEICH.

• INDEX und VERGLEICH zur Rückgabe eines Werts, der dem Maximalwert zugeordnet ist:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX und VERGLEICH zur Rückgabe eines Werts, der dem Minimalwert zugeordnet ist:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Die obigen Formeln enthalten zwei Argumente:
• • array bezeichnet den Bereich, aus dem Sie die zugehörigen Informationen abrufen möchten.
  • lookup_array stellt die Menge der Werte dar, die anhand bestimmter Kriterien untersucht oder durchsucht werden – beispielsweise nach dem Maximum oder Minimalwert.

Wenn Sie beispielsweise ermitteln möchten,wer die höchste Punktzahl hat, verwenden Sie die folgende Formel:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tipp: Passen Sie Ihre eigenen #NV-Fehlermeldungen an

Beim Einsatz der Excel-Funktionen INDEX und VERGLEICH wird der Fehlerwert #NV angezeigt, sobald kein passendes Ergebnis gefunden wird. So liefert beispielsweise die Suche nach der Punktzahl einer Schülerin namens Samantha in der folgenden Tabelle den Fehler #NV – denn sie ist im Datensatz nicht enthalten.

Um Ihre Tabellen benutzerfreundlicher zu gestalten, können Sie diese Fehlermeldung anpassen, indem Sie Ihre INDEX-Formeln unterscheiden-Formel in die WENNFEHLER-Funktion einbetten:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Das war der gesamte relevante Inhalt zu den Excel-Funktionen INDEX und VERGLEICH. Ich hoffe, dieses Tutorial war hilfreich für Sie! Wenn Sie weitere Excel-Tipps und -Tricks entdecken möchten,klicken Sie bitte hier, um Zugang zu unserer umfangreichen Sammlung mit über tausend Tutorials zu erhalten.