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

2024 Autor: Abigail Brown | [email protected]. Zuletzt bearbeitet: 2023-12-17 06:43

Was man wissen sollte

Die INDEX-Funktion kann allein verwendet werden, aber wenn Sie die MATCH-Funktion darin verschachteln, wird eine erweiterte Suche erstellt.
Diese verschachtelte Funktion ist flexibler als SVERWEIS und kann schneller Ergebnisse liefern.

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

Was sind die INDEX- und MATCH-Funktionen?

INDEX und MATCH sind Excel-Suchfunktionen. 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. Beispielsweise könnte es verwendet werden, um den Wert in der zweiten Zeile eines Datensatzes oder in der fünften Zeile und dritten Sp alte zu finden.

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

INDEX und MATCH Syntax & 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 verwenden wird. Es kann sich um eine oder mehrere Zeilen und Sp alten 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. Dies ist erforderlich, es sei denn, column_num ist vorhanden.
column_num ist die Sp alte im Array, aus der ein Wert zurückgegeben werden soll, z. B. 1 oder 9. Dies ist optional.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value ist der Wert, den Sie in lookup_array abgleichen möchten. Es kann sich um eine Zahl, einen Text oder einen logischen Wert handeln, der manuell eingegeben oder über einen Zellbezug referenziert wird. Dies ist erforderlich.
lookup_array ist der Bereich der zu durchsuchenden Zellen. Es kann eine einzelne Zeile oder eine einzelne Sp alte sein, z. B. A2:D2 oder G1:G45. Dies ist erforderlich.
match_type kann -1, 0 oder 1 sein. Es gibt an, wie lookup_value mit Werten in lookup_array abgeglichen wird (siehe unten). 1 ist der Standardwert, wenn dieses Argument weggelassen wird.

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

Verwenden Sie 1 oder -1 für Zeiten, in denen Sie eine ungefähre Suche entlang einer Skala durchführen müssen, z. B. wenn Sie mit Zahlen arbeiten und wenn Annäherungen in Ordnung sind. Denken Sie jedoch daran, dass 1 der Standardwert ist, wenn Sie match_type nicht angeben, was die Ergebnisse verfälschen kann, wenn Sie wirklich eine exakte Übereinstimmung wünschen.

Beispiel für INDEX- und MATCH-Formeln

Bevor wir uns ansehen, wie INDEX und MATCH in einer Formel kombiniert werden, müssen wir verstehen, wie diese Funktionen für sich allein 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 erh alten:

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

MATCH Beispiele

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

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

=MATCH("Stacy", A2:D2, 0) sucht nach Stacy im Bereich A2:D2 und gibt 3 als Ergebnis zurück.
=MATCH(14, D1:D2) sucht nach 14 im Bereich D1:D2, aber da es nicht in der Tabelle gefunden wird, findet MATCH den nächstgrößeren Wert das ist kleiner oder gleich 14, was in diesem Fall 13 ist, was sich an Position 1 von lookup_array befindet.
=MATCH(14, D1:D2, -1) ist identisch mit der obigen Formel, aber da das Array nicht in absteigender Reihenfolge ist, wie es -1 erfordert, wir erh alten eine Fehlermeldung.
=MATCH(13, A1:D1, 0) sucht nach 13 in der ersten Zeile des Blattes, was 4 zurückgibt, da es das vierte Element in diesem Array ist.

INDEX-MATCH Beispiele

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

Zellbezug in Tabelle suchen

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

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

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

MATCH(F1, A2:A5) sucht den F1-Wert (8795) im Datensatz A2:A5. Wenn wir die Sp alte herunterzählen, können wir sehen, dass es 2 ist, also hat die MATCH-Funktion das gerade herausgefunden.
Das INDEX-Array ist B2:B5, da wir letztendlich nach dem Wert in dieser Sp alte suchen.
Die INDEX-Funktion könnte jetzt so umgeschrieben werden, da 2 das ist, was MATCH gefunden hat: INDEX(B2:B5, 2, [column_num]).
Da column_num optional ist, können wir das hier entfernen: INDEX(B2:B5, 2).
Also, das ist jetzt wie eine normale INDEX-Formel, bei der wir den Wert des zweiten Elements in B2:B5 finden, das rot ist.

Nach Zeilen- und Sp altenüberschriften suchen

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

In diesem Beispiel von MATCH und INDEX führen wir eine Zwei-Wege-Suche durch. Die Idee ist, zu sehen, wie viel Geld wir im Mai mit grünen Artikeln verdient haben. Dies ist dem obigen Beispiel wirklich ähnlich, aber eine zusätzliche MATCH-Formel ist in INDEX verschachtelt.

MATCH(G1, A2:A13, 0) ist das erste gelöste Item in dieser Formel. Es sucht nach G1 (das Wort "May") in A2:A13, um einen bestimmten Wert zu erh alten. Wir sehen es hier nicht, aber es ist 5.
MATCH(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 Sp altenüberschriften bei B1:E1. Dieser löst sich auf 3.
Wir können die INDEX-Formel jetzt folgendermaßen umschreiben, um zu visualisieren, was passiert: =INDEX(B2:E13, 5, 3). Dies sucht in der gesamten Tabelle B2:E13 nach der fünften Zeile und dritten Sp alte, die $180. zurückgibt.

MATCH- und INDEX-Regeln

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

MATCH unterscheidet nicht zwischen Groß- und Kleinschreibung, daher werden Groß- und Kleinbuchstaben gleich behandelt, wenn Textwerte abgeglichen werden.
MATCH gibt aus mehreren Gründen NV zurück: wenn match_type 0 ist und lookup_value nicht gefunden wird wenn match_type -1 ist und lookup_array nicht in absteigender Reihenfolge ist, wenn match_type 1 ist und lookup_array nicht in aufsteigender Reihenfolge ist Reihenfolge, und wenn lookup_array keine einzelne Zeile oder Sp alte ist.
Sie können ein Platzh alterzeichen im Lookup_value-Argument verwenden, wenn match_type 0 ist und lookup_value eine Textzeichenfolge ist. Ein Fragezeichen steht für ein beliebiges einzelnes Zeichen und ein Sternchen für eine beliebige Zeichenfolge (z. B. =MATCH("Jo", 1:1, 0)). Um MATCH zu verwenden, um ein tatsächliches Fragezeichen oder Sternchen zu finden, geben Sie zuerst ~ ein.
INDEX gibt REF zurück! if row_num und column_num zeigen nicht auf eine Zelle innerhalb des Arrays.