In Excel ist es häufig erforderlich, bestimmte Daten genau abzurufen. Während die Funktionen INDEX und MATCH jeweils ihre eigenen Stärken haben, wird durch ihre Kombination ein leistungsstarkes Toolset für die Datensuche freigeschaltet. Zusammen ermöglichen sie eine Reihe von Suchfunktionen, von einfachen horizontalen und vertikalen Suchvorgängen bis hin zu erweiterten Funktionen wie bidirektionaler, Groß-/Kleinschreibung berücksichtigender Suche und Suche nach mehreren Kriterien. Die Kombination von INDEX und MATCH bietet im Vergleich zu VLOOKUP erweiterte Funktionen und ermöglicht eine größere Auswahl an Datensuchoptionen. Lassen Sie uns in diesem Tutorial in die Tiefe der Möglichkeiten eintauchen, die sie gemeinsam erreichen können.

• INDEX-Funktion
• MATCH-Funktion
• Kombinieren Sie INDEX und MATCH

• Zweiseitige Suche
• Linke Suche
• Groß-/Kleinschreibung beachten
• Engste Übereinstimmung
• Suche mit mehreren Kriterien
• Suche über mehrere Spalten hinweg
• Suche nach dem ersten nicht leeren Wert
• Suche nach dem ersten numerischen Wert
• Suchen Sie nach MAX- oder MIN-Zuordnungen
• Tipp: Passen Sie Ihre eigenen #N/A-Fehlermeldungen an

So verwenden Sie INDEX und MATCH in Excel

Bevor wir die INDEX- und MATCH-Funktionen verwenden, sollten wir uns zunächst vergewissern, wie uns INDEX und MATCH beim Nachschlagen von Werten helfen können.

So verwenden Sie die INDEX-Funktion in Excel

Das INDEX -Funktion in Excel gibt den Wert an einer bestimmten Position in einem bestimmten Bereich zurück. Die Syntax der INDEX-Funktion lautet wie folgt:

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

Zum Beispiel zu wissen die Partitur von Jeff, der 6Wenn Sie den Schüler auf der Liste haben, können Sie die INDEX-Funktion wie folgt verwenden:

=INDEX(C2:C11,6)

√ Hinweis: Die Reichweite C2: C11 Dort werden die Punkte aufgeführt, während die Zahl angegeben wird 6 findet das Prüfungsergebnis der 6Schülerin.

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

Jetzt sollten wir wissen, dass die INDEX-Funktion perfekt mit horizontalen oder vertikalen Bereichen arbeiten kann. Aber was ist, 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 es herauszufinden Jeffs Punktzahl Innerhalb des Tabellenbereichs statt in einer einzelnen Spalte können wir seine Punktzahl mit a lokalisieren Zeilennummer 6 und einem Spaltennummer 3 der Zellen über A2 bis C11 so was:

=INDEX(A2:C11,6,3)

Bei einer wirklich großen Datenbank mit mehreren Zeilen und Spalten ist es jedoch sicherlich nicht bequem, die Formel mit einer genauen Zeilen- und Spaltennummer anzuwenden. Und hier sollten wir die Verwendung der MATCH-Funktion kombinieren.

Toll! Verwenden Sie effiziente Tabs in Excel wie Chrome, Edge, Firefox und Safari!
Sparen Sie 50% Ihrer Zeit und reduzieren Sie jeden Tag Tausende von Mausklicks für Sie!

So verwenden Sie die MATCH-Funktion in Excel

Die MATCH-Funktion in Excel gibt einen numerischen Wert zurück, die Position eines bestimmten Elements im angegebenen Bereich. Die Syntax der MATCH-Funktion lautet wie folgt:

• Lookup-Wert (erforderlich) bezieht sich auf den Wert, der in der übereinstimmen soll lookup_array.
• lookup_array (erforderlich) bezieht sich auf den Zellbereich, in dem MATCH durchsucht werden soll.
• Übereinstimmungstyp (optional): 1, 0 or -1.
  • 1 (Standard), MATCH findet den größten Wert, der kleiner oder gleich dem ist Lookup-Wert. Die Werte im lookup_array müssen in aufsteigender Reihenfolge platziert werden.
  • 0, MATCH findet den ersten Wert, der genau dem entspricht Lookup-Wert. Die Werte im lookup_array kann in beliebiger Reihenfolge sein. (In den Fällen, in denen der Übereinstimmungstyp auf 0 festgelegt ist, können Sie Platzhalterzeichen verwenden.)
  • -1, MATCH findet den kleinsten Wert, der größer oder gleich dem Lookup-Wert. Die Werte im lookup_array muss in absteigender Reihenfolge platziert werden.

Zum Beispiel zu wissen die Position von Vera in der Namensliste, können Sie die MATCH-Formel wie folgt verwenden:

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

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

Da wir nun die grundlegende Verwendung der INDEX- und MATCH-Funktionen in Excel kennen, krempeln wir die Ärmel hoch und machen uns bereit, die beiden Funktionen zu kombinieren.

So kombinieren Sie INDEX und MATCH in Excel

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

Finden Evelyns Partitur, mit dem Wissen, dass die Prüfungsergebnisse vorliegen 3In der dritten Spalte können wir Verwenden Sie die MATCH-Funktion, um die Zeilenposition automatisch zu bestimmen ohne dass eine manuelle Zählung erforderlich ist. Anschließend können wir die INDEX-Funktion verwenden, um die abzurufen Wert am Schnittpunkt der identifizierten Zeile und der 3. Spalte:

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

Nun, da die Formel vielleicht etwas kompliziert aussieht, gehen wir jeden Teil davon durch.

Das INDEX Formel enthält drei Argumente:

• Zeilennummer: MATCH("Evelyn",A2:A11,0) liefert INDEX die Zeilenposition des Wertes „Evelyn" im Bereich A2: A11, Das ist 5.
• Spalte_num: 3 spezifiziert das 3XNUMX. Spalte für INDEX, um die Punktzahl innerhalb des Arrays zu lokalisieren.
• Array: A2: C11 weist INDEX an, den passenden Wert am Schnittpunkt der angegebenen Zeile und Spalte innerhalb des Bereichs von zurückzugeben A2 bis C11. Schließlich erhalten wir das Ergebnis 90.

In der obigen Formel haben wir einen fest codierten Wert verwendet: "Evelyn". In der Praxis sind hartcodierte Werte jedoch unpraktisch, da sie jedes Mal geändert werden müssten, wenn wir nach anderen Daten suchen möchten, beispielsweise nach der Punktzahl eines anderen Schülers. In solchen Szenarien können wir Zellbezüge nutzen, um dynamische Formeln zu erstellen. In diesem Fall werde ich es zum Beispiel tun Ändern Sie „Evelyn“ in F2:

=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, um die INDEX- und MATCH-Funktionen zu verwenden, um unterschiedliche Anforderungen zu erfüllen.

INDEX und MATCH, um eine bidirektionale Suche anzuwenden

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

In solchen Fällen können wir eine bidirektionale Suche, auch Matrixsuche genannt, durchführen, indem wir zwei MATCH-Funktionen verwenden: eine zum Ermitteln der Zeilennummer und die andere zum Ermitteln der Spaltennummer. Zum Beispiel wissen Evelyns Partitur, sollten wir die Formel verwenden:

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

INDEX und MATCH, um eine Linkssuche anzuwenden

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 Möglichkeiten einer anderen leistungsstarken Excel-Suchfunktion, SVERWEIS, übersteigt.

Tatsächlich ist die Möglichkeit, Suchvorgänge auf der linken Seite durchzuführen, einer der Aspekte, bei denen die Kombination von INDEX und MATCH VLOOKUP in den Schatten stellt.

Finden Evelyns Klasse, verwenden Sie die folgende Formel, um Suche nach Evelyn in B2:B11 und Rufen Sie den entsprechenden Wert aus A2:A11 ab.

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

INDEX und MATCH, um eine Groß-/Kleinschreibung anzuwenden

Bei den MATCH-Funktionen wird die Groß-/Kleinschreibung grundsätzlich nicht beachtet. Wenn Ihre Formel jedoch zwischen Groß- und Kleinbuchstaben unterscheiden soll, können Sie sie durch die Einbindung von verbessern GENAU Funktion. Durch die Kombination der MATCH-Funktion mit EXACT in einer INDEX-Formel können Sie dann effektiv eine Suche unter Berücksichtigung der Groß- und Kleinschreibung durchführen, wie unten gezeigt:

• Array bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
• Lookup-Wert bezieht sich auf den abzugleichenden Wert unter Berücksichtigung der Groß-/Kleinschreibung von Zeichen im lookup_array.
• lookup_array bezieht sich auf den Zellbereich, mit dem MATCH verglichen werden soll Lookup-Wert.

Zum Beispiel zu wissen JIMMYs Prüfungsergebnis, verwenden Sie die folgende Formel:

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

√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter, außer in Excel 365 und Excel 2021.

INDEX und MATCH, um eine möglichst große Übereinstimmung zu finden

In Excel kann es zu Situationen kommen, in denen Sie die nächste oder am nächsten kommende Übereinstimmung mit einem bestimmten Wert innerhalb eines Datensatzes finden müssen. In solchen Szenarien kann die Verwendung einer Kombination aus 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 Wertebereich, zu dem Sie die größte Übereinstimmung finden möchten Lookup-Wert.
• Lookup-Wert bezieht sich auf den Wert, um die nächstgelegene Übereinstimmung zu finden.

Zum Beispiel, um es herauszufinden dessen Punktzahl am nächsten bei 85 liegt, verwenden Sie die folgende Formel, um Suchen Sie in C85:C2 nach der Punktzahl, die 11 am nächsten kommt und Rufen Sie den entsprechenden Wert aus A2:A11 ab.

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

√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter, außer in Excel 365 und Excel 2021.

Toll! Verwenden Sie effiziente Tabs in Excel wie Chrome, Edge, Firefox und Safari!
Sparen Sie 50% Ihrer Zeit und reduzieren Sie jeden Tag Tausende von Mausklicks für Sie!

INDEX und MATCH, um eine Suche mit mehreren Kriterien anzuwenden

Um einen Wert zu finden, der mehrere Bedingungen erfüllt und eine Suche in zwei oder mehr Spalten erfordert, verwenden Sie die folgende Formel. Mit der Formel können Sie eine Suche nach mehreren Kriterien durchführen, indem Sie verschiedene Bedingungen in verschiedenen Spalten angeben und so den gewünschten Wert finden, der alle angegebenen Kriterien erfüllt.

√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter. Ein Paar geschweifte Klammern wird dann in der Bearbeitungsleiste 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 prüft, ob eine bestimmte Lookup-Wert stimmt mit den Werten im überein lookup_array.

Um zum Beispiel die zu finden Partitur von Coco der Klasse A, dessen Geburtsdatum der 7 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, um eine Suche über mehrere Spalten hinweg anzuwenden

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 ermitteln, müssen Sie eine Suche über die Datenspalten durchführen und diesen mit dem entsprechenden Schlüssel in der Referenzspalte verknüpfen.

Wie können wir beispielsweise in der folgenden Tabelle den Schüler Shawn mithilfe von INDEX und MATCH seiner entsprechenden Klasse zuordnen? Nun, Sie können es mit einer Formel erreichen, aber die Formel ist ziemlich umfangreich und es kann schwierig sein, sie zu verstehen, geschweige denn sie sich zu merken und 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)), "")

Das ist wo Kutools for Excel's Indizieren und Abgleichen mehrerer Spalten Funktion ist praktisch. Dies vereinfacht den Prozess und ermöglicht die schnelle und einfache Zuordnung bestimmter Einträge zu den entsprechenden Kategorien. Um dieses leistungsstarke Werkzeug freizuschalten und Shawn mühelos seiner Klasse zuzuordnen, ist es ganz einfach Laden Sie das Kutools for 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 passende Klasse anzeigen möchten.
2. Auf dem Kutoolen Tab, klicken Sie auf Formel-Helfer > Lookup & Referenz > Indizieren und Abgleichen mehrerer Spalten.
3. 
3. Gehen Sie im Popup-Dialogfeld wie folgt vor:
   1. Klicken Sie auf den 1 Schaltfläche neben Lookup_col , um die Spalte auszuwählen, die die Schlüsselinformationen enthält, die Sie zurückgeben möchten, also die Klassennamen. (Sie können hier nur eine einzelne Spalte auswählen.)
   2. Klicken Sie auf die 2 Schaltfläche neben Tabelle_rng um die Zellen auszuwählen, die den Werten im ausgewählten . entsprechen Lookup_col, also die Schülernamen.
   3. Klicken Sie auf die 3 Schaltfläche neben Lookup-Wert , um die Zelle auszuwählen, die den Namen des Schülers enthält, den Sie seiner Klasse zuordnen möchten, in diesem Fall Shawn.
   4. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK.
   5. 

Ergebnis

Kutools hat die Formel automatisch generiert und Shawns Klassenname wird sofort in der Zielzelle angezeigt.

INDEX und MATCH zur Suche nach dem ersten nicht leeren Wert

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

• Den ersten nicht leeren Wert in einer Spalte oder Zeile abrufen und dabei Fehler ignorieren:

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

• Erhalten Sie den ersten nicht leeren Wert in einer Spalte oder Zeile, einschließlich Fehlern:

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

  

INDEX und MATCH zur Suche nach dem ersten numerischen Wert

Um den ersten numerischen Wert aus einer Spalte oder 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 zum Suchen nach MAX- oder MIN-Zuordnungen

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

• INDEX und MATCH, um einen dem Maximalwert zugeordneten Wert abzurufen:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX und MATCH, um einen dem Mindestwert zugeordneten Wert abzurufen:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• In den obigen Formeln gibt es zwei Argumente:
• • Array bezieht sich auf den Bereich, aus dem Sie die zugehörigen Informationen zurückgeben möchten.
  • lookup_array stellt den Satz von Werten dar, die nach bestimmten Kriterien untersucht oder durchsucht werden sollen, z. B. Maximal- oder Minimalwerte.

Zum Beispiel, wenn Sie feststellen 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

Wenn Sie mit den INDEX- und MATCH-Funktionen von Excel arbeiten, kann der #N/A-Fehler auftreten, wenn kein passendes Ergebnis vorliegt. Wenn Sie beispielsweise in der folgenden Tabelle versuchen, die Punktzahl einer Schülerin namens Samantha zu ermitteln, wird der Fehler #N/A angezeigt, 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 einschließen:

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

Oben finden Sie alle relevanten Inhalte im Zusammenhang mit den INDEX- und MATCH-Funktionen in Excel. Ich hoffe, Sie finden das Tutorial hilfreich. Wenn Sie weitere Tipps und Tricks zu Excel erfahren möchten, klicken Sie bitte hier um auf unsere umfangreiche Sammlung von über Tausenden von Tutorials zuzugreifen.