INDEX MATCH ist eine clevere Möglichkeit, eine bidirektionale Suche in Excel durchzuführen und kann als Workaround für die Einschränkungen von VLOOKUP verwendet werden .
Beispielsweise funktioniert SVERWEIS nur, wenn sich der Nachschlagewert in der ersten Spalte des Nachschlage-Arrays befindet. Außerdem kann es keine in absteigender Reihenfolge sortierten Listen akzeptieren oder einen Wert zurückgeben, der sich links vom Nachschlagewert befindet.
Wenn Sie SVERWEIS regelmäßig verwenden, sind Sie möglicherweise schon auf diese Probleme gestoßen und haben sich wahrscheinlich einfach entschieden, damit zu leben. Wenn Sie lernen, wie Sie INDEX MATCH verwenden, können Sie das Problem hervorragend umgehen.
INDEX MATCH besteht aus zwei Funktionen. Die MATCH-Funktion wird verwendet, um herauszufinden, wo sich der Nachschlagewert in einem Zellbereich befindet. Die INDEX-Funktion gibt dann den Wert oder die Zellreferenz aus einer Tabelle oder einem Bereich zurück.
Die Funktionen MATCH und INDEX werden kombiniert, um die Aufgaben zu erledigen, die SVERWEIS nicht erledigen kann. Dazu gehört das Nachschlagen von Werten links vom Nachschlagewert, das Akzeptieren ungefährer Übereinstimmungen, wenn die Liste in absteigender Reihenfolge sortiert ist, und sogar das Zurückgeben von Werten, die mehreren Kriterien entsprechen . .
Lassen Sie uns jede dieser Funktionen Schritt für Schritt aufschlüsseln.
So verwenden Sie die MATCH-Funktion
Mit der Funktion MATCH wird die Positionsnummer eines bekannten Werts innerhalb eines Zellbereichs ermittelt. Die Funktion MATCH in Excel hat drei mögliche Argumente mit der folgenden Syntax:
- Lookup_value ist der bekannte Wert, den Sie für eine Suche verwenden. Dies kann ein Wert oder eine Zellreferenz sein.
- Lookup_array ist der Zellbereich, in dem der Nachschlagewert gefunden wird.
- Match_type ist eine Einstellung, die Excel mitteilt, ob Sie eine nahezu übereinstimmende Übereinstimmung akzeptieren, wenn der Lookup_Value nicht im Lookup-Array gefunden wird.
Wenn wir beispielsweise die Positionsnummer des Wortes „matt“ im Bereich B2 bis B9 unten wissen möchten.
Beachten Sie, dass im obigen Beispiel der Wert „matte“ direkt in die Formel zwischen doppelten Anführungszeichen eingegeben wurde. Wenn der Nachschlagewert ein Zellbezug ist, werden keine doppelten Anführungszeichen verwendet.
Beachten Sie auch, dass bei der MATCH-Funktion die Groß-/Kleinschreibung nicht beachtet wird.
So verwenden Sie die INDEX-Funktion
Die Excel-Funktion INDEX gibt einen Wert oder eine Zellreferenz aus einer Tabelle oder einem Bereich zurück. Die Funktion INDEX hat zwei Formate – das Array-Format und das Referenzformat. Das Array-Format wird verwendet, wenn wir den Wert zurückgeben möchten, der in der Ergebniszelle gefunden wurde. Das Referenzformat wird verwendet, wenn Excel die Zellreferenz der Ergebniszelle zurückgeben soll (z. B. D17). Wir konzentrieren uns auf das Array-Format, da dies das Format ist, das Sie für die Kombination INDEX/MATCH lernen müssen.
Die Syntax der Array-Funktion INDEX lautet
- Array bezieht sich auf den Bereich oder das Array, das die zu indizierenden Daten enthält. Dieses Argument ist erforderlich.
- Row_num ist die Zeilennummer, in der sich die Zelle mit dem Rückgabewert befindet. Row_num kann weggelassen werden, wenn das Array nur aus einer Zeile besteht. Wenn row_num weggelassen wird, ist column_num erforderlich.
- Column_num ist die Spaltennummer, in der sich die Zelle mit dem Rückgabewert befindet. Column_num ist optional, aber erforderlich, wenn row_num weggelassen wird.
In der folgenden Tabelle möchten wir mithilfe der INDEX-Funktion den Wert anzeigen, der sich in der dritten Zeile und zweiten Spalte des Arrays befindet.
Das dritte Element in Spalte 2 wird als Wert „Matt“ identifiziert und als Ausgabe in Zelle F2 zurückgegeben.
Kombinieren der Funktionen INDEX und MATCH
Wie bereits erwähnt, können die Formeln INDEX und MATCH, wenn sie verschachtelt sind, eine Suche durchführen, die das Gleiche leistet wie die Funktion SVERWEIS und noch mehr. Das Verschachteln einer Formel bedeutet, dass eine ganze Formel als Argument einer anderen Funktion verwendet wird.
Wir können das auf unsere einfache Abfrage anwenden, die Werte aus einem Datensatz extrahiert und dabei die Produktnummer als Eingabe oder Nachschlagewert verwendet.
Mit MATCH ermitteln wir, an welcher Stelle in der Liste die Produktnummer (Positionsnummer) steht, und mit INDEX geben wir die Werte aus den entsprechenden Spalten (Papiertyp, Preis oder Auf Lager) anhand ihrer Spaltennummern zurück.
Für sich allein betrachtet würde jede Funktion wie folgt lauten:
Dies führt eine Suche nach dem Wert in Zelle G2 im Bereich A2 bis A9 durch und erfordert eine genaue Übereinstimmung. Es gibt die Positionsnummer dieses Wertes zurück.
Dies gibt den Wert aus dem Array A2 bis D9 zurück, der die in der MATCH-Formel gefundene Zeilennummer und die Spaltennummer 2 (Papiertyp) schneidet. Die MATCH-Formel wird als row_num -Argument verwendet.
Die Formeln in G4 und G5 sind identisch, mit Ausnahme des letzten Arguments, bei dem es sich um 3 bzw. 4 handelt, um die jeweilige Spaltennummer darzustellen.
Ehrlich gesagt scheint dies ziemlich kompliziert, wenn man bedenkt, dass SVERWEIS dies bereits auf eine viel einfachere Weise und mit nur vier Argumenten erledigt. Wir haben jedoch zu Beginn darauf hingewiesen, dass SVERWEIS keinen Wert zurückgeben kann, der sich links vom Nachschlagewert befindet.
Wenn wir den bekannten Wert so ändern, dass der Papiertyp nun unser Suchwert ist , gibt VLOOKUP den Fehler #NA (Wert nicht verfügbar) zurück. Sehen wir uns an, wie INDEX MATCH damit umgeht.
Für INDEX MATCH sind Nachschlagevorgänge nach links oder rechts kein Problem, da das MATCH-Lookup-Array für sich allein steht und nicht mit dem INDEX-Array verbunden ist.
Auf diese Weise kann INDEX MATCH auch als Ersatz für HLOOKUP verwendet werden , die horizontale Version der VLOOKUP-Funktion.
Sehen wir uns eine fortgeschrittenere Anwendung der INDEX MATCH-Lösung an.
INDEX MATCH – Erweitertes Beispiel
Die Prüfungsteilnehmer haben ihre Studenten-IDs auf ihren Prüfungsunterlagen eingetragen. Diese werden dann ausgewertet und die Noten in Spalte D eingetragen. Wir möchten die Studenten-ID im Array A2 bis C7 nachschlagen, aber SVERWEIS ist dazu nicht in der Lage. Die Studentennamen werden links neben den ID-Nummern angezeigt, da die Liste alphabetisch geordnet ist. Das ist unser erstes Problem.
Wir möchten außerdem jedem Schüler eine Note basierend auf seinem Testergebnis zuweisen. Das Testergebnisdiagramm (F2 bis G6) ist in absteigender Reihenfolge sortiert, was ebenfalls mit SVERWEIS nicht möglich ist.
Die MATCH-Funktion wird verwendet, um die Zeilennummer der INDEX-Funktion zu bestimmen.
(Der Bereich C2 bis C7 wird kopiert, daher können wir $ verwenden, um die Referenzen zu fixieren. Erfahren Sie mehr über absolute und gemischte Referenzen .)
Wir haben den Übereinstimmungstyp 0 verwendet, um sicherzustellen, dass nur eine exakte Übereinstimmung zurückgegeben wird.
Die MATCH-Funktion hat die Studenten-ID 114125 an Position 5 des Nachschlage-Arrays gefunden.
Nun müssen wir das nur noch mit dem entsprechenden Studierendennamen verknüpfen.
(Der Bereich A2 bis C7 wird kopiert, daher können wir $ verwenden, um die Referenzen zu fixieren. Erfahren Sie mehr über absolute und gemischte Referenzen .)
Die MATCH-Formel wurde in die INDEX-Funktion eingefügt bzw. darin verschachtelt, um herauszufinden, welche Zeile in jedem Fall zurückgegeben werden soll. Die erste Spalte des Arrays enthält den Wert, den wir zurückgeben möchten, daher ist das letzte Argument der INDEX-Funktion ( column_num ) 1.
Diese Formel kann nun in die restlichen Zeilen der Spalte C kopiert werden.
Die Bestimmung der Note für jeden Schüler erfolgt nach einem ähnlichen Muster, mit einer Ausnahme. Da die Bewertungstabelle nur fünf Werte enthält, stimmen die Testergebnisse der Schüler möglicherweise nicht genau überein. Daher muss der Übereinstimmungstyp in der MATCH-Formel angepasst werden, um eine ungefähre Übereinstimmung zu akzeptieren.
Mehr lesen: Wo man Outlander Staffel 7 im Jahr 2024 sehen kann: Eine schottische Saga streamen
Unsere MATCH- und INDEX-Formeln würden lauten
- VERGLEICH(D11,$F$2:$F$6,-1)
- INDEX($F$2:$G$6,verschachtelte MATCH-Formel,2)
Bei Verschachtelung würde die vollständige Formel wie folgt lauten:
Obwohl SVERWEIS entwickelt wurde, um die allgemeine Aufgabe des Suchens von Elementen in einer Tabelle zu vereinfachen, können wir INDEX MATCH für komplexere Aufgaben nicht verwerfen.
Hier ist etwas, das Sie vielleicht wissen möchten: Excel hat XLOOKUP als Fix für die oben genannten Beschwerden über die Einschränkungen von VLOOKUP eingeführt. Der Nachteil ist, dass es nur in Excel 2021, Excel für das Web oder mit einem Abonnement für Microsoft 365 verfügbar ist . Wenn Sie also eine ältere Version haben, sollten Sie INDEX MATCH unbedingt verstehen und beherrschen.
Meistern Sie Excel noch heute
Erlernen Sie mehr grundlegende Excel-Kenntnisse und steigern Sie Ihre Produktivität mit dem GoSkills -Kurs „Microsoft Excel – Grundkenntnisse und Fortgeschrittene“ .