So verwenden Sie die INDEX- und MATCH-Funktion in Excel

December 02, 2021
ImMs Office Software & Apps

Was Sie wissen sollten

Die INDEX-Funktion kann alleine verwendet werden, aber das Verschachteln der MATCH-Funktion darin erzeugt eine erweiterte Suche.
Diese verschachtelte Funktion ist flexibler als SVERWEIS und kann schneller Ergebnisse liefern.

In diesem Artikel wird erläutert, wie Sie die Funktionen INDEX und MATCH zusammen in allen Versionen von Excel verwenden, einschließlich Excel 2019 und Microsoft 365.

Was sind die INDEX- und MATCH-Funktionen?

INDEX und MATCH sind Excel-Lookup Funktionen. Obwohl es sich um zwei völlig separate Funktionen handelt, die einzeln verwendet werden können, können sie auch kombiniert werden, um erweiterte Formeln zu erstellen.

Die INDEX-Funktion gibt einen Wert oder die Referenz auf einen Wert aus einer bestimmten Auswahl zurück. Es könnte beispielsweise verwendet werden, um den Wert in der zweiten Zeile eines Datensatzes oder in der fünften Zeile und dritten Spalte zu finden.

Während INDEX sehr gut alleine verwendet werden könnte, macht es das Verschachteln von MATCH in der Formel etwas nützlicher. Die MATCH-Funktion sucht in einem Zellbereich nach einem angegebenen Element und gibt dann die relative Position des Elements im Bereich zurück. Es könnte beispielsweise verwendet werden, um zu bestimmen, dass ein bestimmter Name das dritte Element in einer Namensliste ist.

INDEX- und MATCH-Syntax und Argumente

So müssen beide Funktionen geschrieben werden, damit Excel sie versteht:

=INDEX(Array, row_num, [column_num])

Array ist der Zellbereich, den die Formel verwendet. Es kann sich um eine oder mehrere Zeilen und Spalten handeln, z. B. A1:D5. Es ist erforderlich.
row_num ist die Zeile im Array, aus der ein Wert zurückgegeben werden soll, z. B. 2 oder 18. Es ist erforderlich, es sei denn column_num ist anwesend.
column_num ist die Spalte im Array, aus der ein Wert zurückgegeben werden soll, z. B. 1 oder 9. Es ist optional.

=SPIEL(Lookup-Wert, lookup_array, [match_type])

Lookup-Wert ist der Wert, den Sie abgleichen möchten lookup_array. Dies kann eine Zahl, ein Text oder ein logischer Wert sein, der manuell eingegeben oder über einen Zellbezug referenziert wird. Dies ist erforderlich.
lookup_array ist der Bereich der Zellen, durch die man schauen kann. Es kann sich um eine einzelne Zeile oder eine einzelne Spalte handeln, z. B. A2:D2 oder G1:G45. Dies ist erforderlich.
match_type kann sein -1, 0, oder 1. Es legt fest, wie Lookup-Wert wird mit Werten in. abgeglichen lookup_array (siehe unten). 1 ist der Standardwert, wenn dies Streit wurde weggelassen.

Welcher Übereinstimmungstyp soll verwendet werden
Übereinstimmungstyp	Was es macht	Regel	Beispiel
1	Findet den größten Wert, der kleiner oder gleich ist Lookup-Wert.	Die lookup_array Werte müssen in aufsteigender Reihenfolge platziert werden (z. B. -2, -1, 0, 1, 2; oder A-Z; oder FALSCH, WAHR.	Lookup-Wert ist 25, aber es fehlt in lookup_array, also wird stattdessen die Position der nächstkleineren Zahl, z. B. 22, zurückgegeben.
0	Findet den ersten Wert, der genau gleich ist Lookup-Wert.	Die lookup_array Werte können in beliebiger Reihenfolge sein.	Lookup-Wert 25 ist, gibt also die Position 25 zurück.
-1	Findet den kleinsten Wert, der größer oder gleich ist Lookup-Wert.	Die lookup_array Werte müssen in absteigender Reihenfolge platziert werden (z. B. 2, 1, 0, -1, -2).	Lookup-Wert ist 25, aber es fehlt in lookup_array, daher wird stattdessen die Position der nächstgrößeren Zahl, z. B. 34, zurückgegeben.

Verwenden 1 oder -1 für Zeiten, in denen Sie eine Näherungssuche entlang einer Skala durchführen müssen, z. B. beim Umgang mit Zahlen und wenn Näherungen in Ordnung sind. Aber denken Sie daran, wenn Sie nichts angeben match_type, 1 ist die Standardeinstellung, die die Ergebnisse verzerren kann, wenn Sie wirklich eine genaue Übereinstimmung wünschen.

Beispiel für INDEX- und MATCH-Formeln

Bevor wir uns ansehen, wie man INDEX und MATCH zu einer Formel kombiniert, müssen wir verstehen, wie diese Funktionen alleine funktionieren.

INDEX-Beispiele

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

In diesem ersten Beispiel gibt es vier INDEX-Formeln, die wir verwenden können, um verschiedene Werte zu erhalten:

=INDEX(A1:B2,2,2) durchsucht A1:B2, um den Wert in der zweiten Spalte und zweiten Zeile zu finden, der ist Stacy.
=INDEX(A1:B1,1) durchsucht A1:B1, um den Wert in der ersten Spalte zu finden, der ist Jon.
=INDEX(2:2,1) durchsucht alles in der zweiten Zeile, um den Wert in der ersten Spalte zu finden Tim.
=INDEX(B1:B2,1) durchsucht B1:B2, um den Wert in der ersten Zeile zu finden, der Amy.

MATCH-Beispiele

=VERGLEICH("Stacy",A2:D2,0)
=VERGLEICH(14,D1:D2)
=VERGLEICH(14,D1:D2,-1)
=VERGLEICH(13,A1:D1,0)

Hier sind vier einfache Beispiele für die MATCH-Funktion:

=VERGLEICH("Stacy",A2:D2,0) sucht Stacy im Sortiment A2: D2 und kehrt zurück 3 als Ergebnis.
=VERGLEICH(14,D1:D2) sucht 14 im Sortiment D1: D2, aber da er nicht in der Tabelle gefunden wird, findet MATCH den nächstgrößeren Wert, der kleiner oder gleich ist 14, das ist in diesem Fall 13, die in Position ist 1 von lookup_array.
=VERGLEICH(14,D1:D2,-1) ist identisch mit der Formel darüber, aber da das Array nicht in absteigender Reihenfolge wie -1 erfordert, erhalten wir einen Fehler.
=VERGLEICH(13,A1:D1,0) schaut nach 13 in der ersten Zeile des Blattes, das zurückkehrt 4 da es das vierte Element in diesem Array ist.

INDEX-MATCH-Beispiele

Hier sind zwei Beispiele, bei denen wir INDEX und MATCH in einer Formel kombinieren können:

Zellreferenz in Tabelle suchen

=INDEX(B2:B5,VERGLEICH(F1,A2:A5))

MATCH- und INDEX-Excel-Funktionen in einer Formel verschachtelt

In diesem Beispiel wird die MATCH-Formel innerhalb der INDEX-Formel verschachtelt. Ziel ist es, die Artikelfarbe anhand der Artikelnummer zu identifizieren.

Wenn Sie sich das Bild ansehen, können Sie in den Zeilen "Getrennt" sehen, wie die Formeln für sich allein geschrieben würden, aber da wir sie verschachteln, passiert Folgendes:

SPIEL(F1,A2:A5) sucht den F1 Wert (8795) im Datensatz A2: A5. Wenn wir die Spalte herunterzählen, können wir sehen, dass es ist 2, das ist es, was die MATCH-Funktion gerade herausgefunden hat.
Das INDEX-Array ist B2: B5 da wir letztendlich nach dem Wert in dieser Spalte suchen.
Die INDEX-Funktion könnte nun so umgeschrieben werden, da 2 hat MATCH gefunden: INDEX(B2:B5, 2, [column_num]).
Schon seit column_num optional ist, können wir dies entfernen, um Folgendes zu belassen: INDEX(B2:B5,2).
Dies ist also wie eine normale INDEX-Formel, bei der wir den Wert des zweiten Elements in finden B2: B5, welches ist rot.

Nach Zeilen- und Spaltenüberschriften suchen

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))

INDEX- und MATCH-Verschachtelungsbeispiel in Excel

In diesem Beispiel von MATCH und INDEX führen wir eine bidirektionale Suche durch. Die Idee ist, zu sehen, wie viel Geld wir verdient haben Grün Artikel in Kann. Dies ist dem obigen Beispiel sehr ähnlich, aber eine zusätzliche MATCH-Formel ist in INDEX verschachtelt.

ÜBEREINSTIMMUNG(G1,A2:A13,0) ist das erste gelöste Element dieser Formel. Es sucht G1 (das Wort "Mai") in A2: A13 einen bestimmten Wert zu erhalten. Wir sehen es hier nicht, aber es ist 5.
ÜBEREINSTIMMUNG(G2,B1:E1,0) ist die zweite MATCH-Formel, und sie ist der ersten sehr ähnlich, sucht aber stattdessen nach G2 (das Wort "Grün") in den Spaltenüberschriften bei B1: E1. Dieser beschließt zu 3.
Wir können jetzt die INDEX-Formel wie folgt umschreiben, um zu visualisieren, was passiert: =INDEX(B2:E13,5,3). Das sieht in der ganzen Tabelle aus, B2: E13, für die fünfte Zeile und dritte Spalte, was zurückgibt $180.

MATCH- und INDEX-Regeln

Beim Schreiben von Formeln mit diesen Funktionen sind einige Dinge zu beachten:

MATCH ist nicht Groß-/Kleinschreibung beachten, sodass Groß- und Kleinbuchstaben beim Abgleichen von Textwerten gleich behandelt werden.
MATCH kehrt zurück #N / A aus mehreren Gründen: wenn match_type ist 0 und Lookup-Wert wird nicht gefunden, wenn match_type ist -1 und lookup_array ist nicht in absteigender Reihenfolge, wenn match_type ist 1 und lookup_array ist nicht in aufsteigender Reihenfolge, und wenn lookup_array ist keine einzelne Zeile oder Spalte.
Sie können ein Platzhalterzeichen im verwenden Lookup-Wert argumentieren, wenn match_type ist 0 und Lookup-Wert ist eine Textzeichenfolge. Ein Fragezeichen entspricht einem einzelnen Zeichen und ein Sternchen entspricht einer beliebigen Zeichenfolge (z. B. =VERGLEICH("Jo*",1:1,0)). Um mit MATCH nach einem tatsächlichen Fragezeichen oder Sternchen zu suchen, geben Sie zuerst ~ ein.
INDEX-Rendite #REF! wenn row_num und column_num Zeigen Sie nicht auf eine Zelle innerhalb des Arrays.