Excel INDEX MATCH: Grundlegende und erweiterte Suchen

In Excel ist das präzise Abrufen spezifischer Daten oft eine häufige Notwendigkeit. Während die Funktionen INDEX und MATCH jeweils ihre eigenen Stärken haben, entfaltet ihre Kombination ein leistungsstarkes Werkzeugset für die Datensuche. Zusammen ermöglichen sie eine Vielzahl von Suchfunktionen, von einfachen horizontalen und vertikalen Suchen bis hin zu fortgeschrittenen Funktionen wie Zwei-Wege-, Groß-/Kleinschreibung- und Mehrkriterien-Suchen. Im Vergleich zu SVERWEIS bieten die Kombination von INDEX und MATCH erweiterte Suchmöglichkeiten. In diesem Tutorial tauchen wir in die Tiefe der Möglichkeiten ein, die sie gemeinsam erreichen können.

• INDEX-Funktion
• MATCH-Funktion
• INDEX und MATCH kombinieren

• Zwei-Wege-Suche
• Links-Suche
• Groß-/Kleinschreibung-sensitive Suche
• Nächstgelegene Übereinstimmung
• Suche mit mehreren Kriterien
• Suche über mehrere Spalten
• Suche nach erstem nicht-leeren Wert
• Suche nach erstem numerischen Wert
• Suche nach MAX- oder MIN-Zuordnungen
• Tipp: Passen Sie Ihre eigenen #N/A-Fehlermeldungen an

Wie man INDEX und MATCH in Excel verwendet

Bevor wir die Funktionen INDEX und MATCH verwenden, sollten wir sicherstellen, dass wir zuerst wissen, wie uns INDEX und MATCH beim Suchen von Werten helfen können.

Wie man die INDEX-Funktion in Excel verwendet

Die INDEX-Funktion in Excel gibt den Wert an einem bestimmten Ort in einem spezifischen Bereich zurück. Die Syntax der INDEX-Funktion ist wie folgt:

• array (erforderlich) bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
• row_num (erforderlich, es sei denn, column_num ist vorhanden) bezieht sich auf die Zeilennummer des Arrays.
• column_num (optional, aber erforderlich, wenn row_num weggelassen wird) bezieht sich auf die Spaltennummer des Arrays.

Zum Beispiel, um die Punktzahl von Jeff, dem6. Schüler auf der Liste, zu erfahren, können Sie die INDEX-Funktion so verwenden:

=INDEX(C2:C11,6)

√ Hinweis: Der Bereich C2:C11 ist, wo die Punktzahlen aufgelistet sind, während die Zahl 6 die Prüfungspunktzahl des 6. Schülers findet.

Hier machen wir einen kleinen Test. Für die Formel =INDEX(A1:C1,2), welchen Wert wird sie zurückgeben? --- Ja, sie wird das Geburtsdatum zurückgeben, den 2. Wert in der gegebenen Zeile.

Jetzt sollten wir wissen, dass die INDEX-Funktion perfekt mit horizontalen oder vertikalen Bereichen arbeiten kann. Aber was, wenn wir einen Wert in einem größeren Bereich mit mehreren Zeilen und Spalten zurückgeben müssen? Nun, in diesem Fall sollten wir sowohl eine Zeilennummer als auch eine Spaltennummer anwenden. Zum Beispiel, um Jeffs Punktzahl innerhalb des Tabellenbereichs anstelle einer einzelnen Spalte zu finden, können wir seine Punktzahl mit einer Zeilennummer von6 und einer Spaltennummer von3 in den Zellen von A2 bis C11 so lokalisieren:

=INDEX(A2:C11,6,3)

Für eine wirklich große Datenbank mit mehreren Zeilen und Spalten ist es jedoch sicherlich nicht bequem, die Formel mit einer genauen Zeilennummer und Spaltennummer anzuwenden. Und das ist, wenn wir die Verwendung der MATCH-Funktion kombinieren sollten.

Wie man die MATCH-Funktion in Excel verwendet

Die MATCH-Funktion in Excel gibt einen numerischen Wert zurück, den Ort eines bestimmten Elements im gegebenen Bereich. Die Syntax der MATCH-Funktion ist wie folgt:

• lookup_value (erforderlich) bezieht sich auf den Wert, der im lookup_array abgeglichen werden soll.
• lookup_array (erforderlich) bezieht sich auf den Bereich von Zellen, in dem Sie möchten, dass MATCH sucht.
• match_type (optional): 1, 0 oder -1.
  • 1(Standard), MATCH wird den größten Wert finden, der kleiner oder gleich dem lookup_value ist. Die Werte im lookup_array müssen in aufsteigender Reihenfolge angeordnet sein.
  • 0, MATCH wird den ersten Wert finden, der genau dem lookup_value entspricht. Die Werte im lookup_array können in beliebiger Reihenfolge sein. (Für die Fälle, in denen der Match-Typ auf0 gesetzt ist, können Sie Platzhalterzeichen verwenden.)
  • -1, MATCH wird den kleinsten Wert finden, der größer oder gleich dem lookup_value ist. Die Werte im lookup_array müssen in absteigender Reihenfolge angeordnet sein.

Zum Beispiel, um die Position von Vera in der Namensliste zu erfahren, können Sie die MATCH-Formel so verwenden:

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

√ Hinweis: Das Ergebnis „4“ zeigt an, dass der Name „Vera“ an der4. Position der Liste ist.

Jetzt, da wir die grundlegenden Verwendungen der INDEX- und MATCH-Funktionen in Excel kennen, krempeln wir die Ärmel hoch und machen uns bereit, die beiden Funktionen zu kombinieren.

Wie man INDEX und MATCH in Excel kombiniert

Bitte sehen Sie sich das folgende Beispiel an, um herauszufinden, wie wir die INDEX- und MATCH-Funktionen kombinieren können:

Um Evelyns Punktzahl zu finden, mit dem Wissen, dass die Prüfungspunktzahlen in der 3. Spalte sind, können wir die MATCH-Funktion verwenden, um die Zeilenposition automatisch zu bestimmen, ohne sie manuell zählen zu müssen. Anschließend können wir die INDEX-Funktion verwenden, um den Wert an der Schnittstelle der identifizierten Zeile und der3. Spalte abzurufen:

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

Nun, da die Formel etwas kompliziert aussehen mag, gehen wir jeden Teil davon durch.

Die INDEX-Formel enthält drei Argumente:

• row_num: MATCH("Evelyn",A2:A11,0)liefert INDEX die Zeilenposition des Wertes "Evelyn" im Bereich A2:A11, was ist5.
• column_num:3 gibt die3. Spalte an, damit INDEX die Punktzahl innerhalb des Arrays lokalisiert.
• array: A2:C11 weist INDEX an, den übereinstimmenden Wert an der Schnittstelle der angegebenen Zeile und Spalte innerhalb des Bereichs von A2 bis C11 zurückzugeben. Schließlich erhalten wir das Ergebnis90.

In der obigen Formel haben wir einen fest codierten Wert, "Evelyn", verwendet. In der Praxis sind fest codierte Werte jedoch unpraktisch, da sie bei jeder Suche nach anderen Daten, wie der Punktzahl eines anderen Schülers, geändert werden müssten. In solchen Szenarien können wir Zellreferenzen verwenden, um dynamische Formeln zu erstellen. Zum Beispiel werde ich in diesem Fall "Evelyn" in F2 ändern:

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

Beispiele für INDEX- und MATCH-Formeln

In diesem Teil werden wir über verschiedene Umstände sprechen, in denen die INDEX- und MATCH-Funktionen verwendet werden, um unterschiedliche Bedürfnisse zu erfüllen.

INDEX und MATCH zur Anwendung einer Zwei-Wege-Suche

Im vorherigen Beispiel kannten wir die Spaltennummer und verwendeten eine MATCH-Formel, um die Zeilennummer zu finden. Aber was, wenn wir uns auch über die Spaltennummer unsicher sind?

In solchen Fällen können wir eine Zwei-Wege-Suche, auch bekannt als Matrixsuche, durchführen, indem wir zwei MATCH-Funktionen verwenden: eine, um die Zeilennummer zu finden, und die andere, um die Spaltennummer zu bestimmen. Zum Beispiel, um Evelyns Punktzahl zu erfahren, sollten wir die Formel verwenden:

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

INDEX und MATCH zur Anwendung einer Links-Suche

Betrachten wir nun ein Szenario, in dem Sie Evelyns Klasse bestimmen müssen. Sie haben vielleicht bemerkt, dass die Klassenspalte links von der Namensspalte positioniert ist, eine Situation, die die Fähigkeiten einer anderen leistungsstarken Excel-Suchfunktion, SVERWEIS, übersteigt.

Tatsächlich ist die Fähigkeit, Links-Suchen durchzuführen, einer der Aspekte, in denen die Kombination von INDEX und MATCH SVERWEIS übertrifft.

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

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

INDEX und MATCH zur Anwendung einer groß-/kleinschreibung-sensitiven Suche

Die MATCH-Funktion ist von Natur aus nicht groß-/kleinschreibung-sensitiv. Wenn Sie jedoch möchten, dass Ihre Formel zwischen Groß- und Kleinbuchstaben unterscheidet, können Sie sie durch die Integration der EXACT-Funktion verbessern. Durch die Kombination der MATCH-Funktion mit EXACT in einer INDEX-Formel können Sie dann effektiv eine groß-/kleinschreibung-sensitive Suche durchführen, wie unten gezeigt:

• array bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
• lookup_value bezieht sich auf den Wert, der abgeglichen werden soll, unter Berücksichtigung der Groß-/Kleinschreibung der Zeichen, im lookup_array.
• lookup_array bezieht sich auf den Bereich von Zellen, in dem Sie möchten, dass MATCH mit lookup_value vergleicht.

Zum Beispiel, um JIMMYs Prüfungspunktzahl zu erfahren, verwenden Sie die folgende Formel:

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

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Eingabe eingeben müssen, außer in Excel365, Excel2021 und neueren Versionen.

INDEX und MATCH zur Suche nach der nächstgelegenen Übereinstimmung

In Excel können Sie auf Situationen stoßen, in denen Sie die nächstgelegene oder nächstbeste Übereinstimmung zu einem bestimmten Wert innerhalb eines Datensatzes finden müssen. In solchen Szenarien kann die Verwendung einer Kombination von INDEX- und MATCH-Funktionen zusammen mit den ABS- und MIN-Funktionen äußerst hilfreich sein.

• array bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
• lookup_array bezieht sich auf den Bereich von Werten, in dem Sie die nächstgelegene Übereinstimmung zu lookup_value finden möchten.
• lookup_value bezieht sich auf den Wert, dessen nächstgelegene Übereinstimmung gefunden werden soll.

Zum Beispiel, um herauszufinden, wessen Punktzahl am nächsten an85 liegt, verwenden Sie die folgende Formel, um die nächstgelegene Punktzahl zu85 in C2:C11 zu suchen 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 Array-Formel, die Sie mit Strg + Umschalt + Eingabe eingeben müssen, außer in Excel365, Excel2021 und neueren Versionen.

INDEX und MATCH zur Anwendung einer Suche mit mehreren Kriterien

Um einen Wert zu finden, der mehreren Bedingungen entspricht und Sie über zwei oder mehr Spalten suchen müssen, verwenden Sie die folgende Formel. Die Formel ermöglicht Ihnen eine Mehrkriterien-Suche, indem Sie verschiedene Bedingungen über verschiedene Spalten spezifizieren, um den gewünschten Wert zu finden, der alle angegebenen Kriterien erfüllt.

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Eingabe eingeben müssen. Ein Paar geschweifte Klammern wird dann in der Formelleiste angezeigt.

• array bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
• (lookup_value=lookup_array) stellt eine einzelne Bedingung dar. Diese Bedingung überprüft, ob ein bestimmtes lookup_value mit den Werten im lookup_array übereinstimmt.

Zum Beispiel, um die Punktzahl von Klasse A's Coco zu finden, dessen Geburtsdatum der7.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 MATCH zur Anwendung einer Suche über mehrere Spalten

Stellen Sie sich ein Szenario vor, in dem Sie mit mehreren Datenspalten arbeiten. Die erste Spalte fungiert als Schlüssel zur Klassifizierung der Daten in den anderen Spalten. Um die Kategorie oder Klassifizierung für einen bestimmten Eintrag zu bestimmen, müssen Sie eine Suche über die Datenspalten durchführen und sie mit dem relevanten Schlüssel in der Referenzspalte verknüpfen.

Zum Beispiel, in der Tabelle unten, wie können wir den Schüler Shawn mit seiner entsprechenden Klasse abgleichen, indem wir INDEX und MATCH verwenden? Nun, Sie können es mit einer Formel erreichen, aber die Formel ist ziemlich umfangreich und kann schwer zu verstehen sein, geschweige denn zu merken und zu tippen.

=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 Funktion Index und Vergleich mehrerer Spalten von Kutools für Excel ins Spiel. Sie vereinfacht den Prozess und macht es schnell und einfach, spezifische Einträge mit ihren entsprechenden Kategorien abzugleichen. Um dieses leistungsstarke Werkzeug freizuschalten und Shawn mühelos mit seiner Klasse abzugleichen, laden Sie einfach das Kutools für Excel-Add-In herunter und installieren Sie es und gehen Sie dann wie folgt vor:

1. Wählen Sie die Zielzelle aus, in der Sie die übereinstimmende Klasse anzeigen möchten.
2. Klicken Sie auf der Kutools-Registerkarte auf Formelhelfer > Suche & Referenz > Index und Vergleich mehrerer Spalten.
3. 
3. Im Popup-Dialogfeld gehen Sie wie folgt vor:
   1. Klicken Sie auf die1. Schaltfläche neben Suchspalte um die Spalte auszuwählen, die die Schlüsselinformationen enthält, die Sie zurückgeben möchten, d.h. die Klassennamen. (Sie können hier nur eine einzelne Spalte auswählen.)
   2. Klicken Sie auf die2. Schaltfläche neben Tabellenbereich um die Zellen auszuwählen, die die Werte in der ausgewählten Suchspalteabgleichen, d.h. die Schülernamen.
   3. Klicken Sie auf die3. Schaltfläche neben Suchwert um die Zelle auszuwählen, die den Namen des Schülers enthält, den Sie mit seiner Klasse abgleichen möchten, in diesem Fall Shawn.
   4. Klicken Sie auf OK.
   5. 

Ergebnis

Kutools hat die Formel automatisch generiert, und Sie sehen sofort den Klassennamen von Shawn in der Zielzelle angezeigt.

INDEX und MATCH zur Suche nach erstem nicht-leeren Wert

Um den ersten nicht-leeren Wert, der Fehler ignoriert, aus einer Spalte oder einer Zeile abzurufen, können Sie eine Formel basierend auf den Funktionen INDEX und MATCH verwenden. Wenn Sie jedoch die Fehler aus Ihrem Bereich nicht ignorieren möchten, fügen Sie die ISBLANK-Funktion hinzu.

• Ersten nicht-leeren Wert in einer Spalte oder Zeile abrufen, Fehler ignorierend:

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

• Ersten nicht-leeren Wert in einer Spalte oder Zeile abrufen, einschließlich Fehler:

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

  

INDEX und MATCH zur Suche nach erstem numerischen Wert

Um den ersten numerischen Wert aus einer Spalte oder einer Zeile abzurufen, verwenden Sie die Formel basierend auf den Funktionen INDEX, MATCH und ISNUMBER.

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

INDEX und MATCH zur Suche nach MAX- oder MIN-Zuordnungen

Wenn Sie einen Wert abrufen müssen, der mit dem maximalen oder minimalen Wert innerhalb eines Bereichs verbunden ist, können Sie die MAX- oder MIN-Funktion zusammen mit den Funktionen INDEX und MATCH verwenden.

• INDEX und MATCH zur Abrufung eines Wertes, der mit dem maximalen Wert verbunden ist:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX und MATCH zur Abrufung eines Wertes, der mit dem minimalen Wert verbunden ist:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• Es gibt zwei Argumente in den obigen Formeln:
• • array bezieht sich auf den Bereich, aus dem Sie die zugehörigen Informationen zurückgeben möchten.
  • lookup_array stellt die Menge von Werten dar, die untersucht oder nach spezifischen Kriterien durchsucht werden sollen, d.h. maximale oder minimale Werte.

Zum Beispiel, wenn Sie bestimmen 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 #N/A-Fehlermeldungen an

Beim Arbeiten mit den INDEX- und MATCH-Funktionen von Excel können Sie auf den #N/A-Fehler stoßen, wenn kein übereinstimmendes Ergebnis vorhanden ist. Zum Beispiel, in der Tabelle unten, wenn versucht wird, die Punktzahl eines Schülers namens Samantha zu finden, erscheint ein #N/A-Fehler, da sie nicht im Datensatz vorhanden ist.

Um Ihre Tabellenkalkulationen benutzerfreundlicher zu gestalten, können Sie diese Fehlermeldung anpassen, indem Sie Ihre INDEX MATCH-Formel in die IFNA-Funktion einwickeln:

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

Oben ist der gesamte relevante Inhalt zu den INDEX- und MATCH-Funktionen in Excel. Ich hoffe, Sie finden das Tutorial hilfreich. Wenn Sie mehr Excel-Tipps und -Tricks erkunden möchten, klicken Sie bitte hier, um auf unsere umfangreiche Sammlung von über tausend Tutorials zuzugreifen.