In diesem Artikel erfahren Sie, wie Sie die INDEX- und MATCH-Funktion zum Nachschlagen von Werten in Excel verwenden.
INDEX & MATCH-Funktion zum Ersetzen der vlookup-Funktion
Normalerweise verwenden wir die SVERWEIS-Funktion, um einen Wert zu finden, der den Zeilenindex und die Spaltennummer kennt. Für die SVERWEIS-Funktion benötigen wir die Spaltennummer und das Zeilenabgleichskriterium und das Wichtigste, um herauszufinden, dass sich der Wert rechts vom Abgleichswert befindet. Aber die Daten unterstützen das nicht immer. Oft müssen wir nur Werte nachschlagen, die den Zeilen- oder Spaltenindex nicht kennen, sondern nur die Werte haben, die übereinstimmen. Zum Beispiel: das Gehalt eines Mitarbeiters mit einer Mitarbeiter-ID ermitteln. In diesem suchen wir nach Mitarbeiter-ID für den Zeilenindex und Gehalt für den Spaltenindex. Lassen Sie uns lernen, wie wir diese Suche mit INDEX & MATCH durchführen können.
Wie löst man das Problem?
Damit die Formel zuerst verstanden wird, müssen wir die folgenden Funktionen ein wenig überarbeiten
- INDEX-Funktion
- MATCH-Funktion
Jetzt erstellen wir eine Formel mit den obigen Funktionen. Die Match-Funktion gibt den Index des Nachschlagewerts1 im Zeilenkopffeld zurück. Und eine andere MATCH-Funktion gibt den Index des Nachschlagewerts 2 aus dem Spaltenkopffeld zurück. Die Indexnummern werden nun in die INDEX-Funktion eingegeben, um die Werte unter dem Nachschlagewert aus den Tabellendaten zu erhalten.
Generische Formel:
| = INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) |
Daten : Array von Werten innerhalb der Tabelle ohne Überschriften
lookup_value1 : Wert, der im row_header gesucht werden soll.
row_headers : Zeilenindexarray, das gesucht werden soll.
lookup_value1 : Wert, der in der Spaltenüberschrift gesucht werden soll.
Spaltenüberschriften : Spalte Indexarray, das gesucht werden soll.
Beispiel:
Die obigen Aussagen können kompliziert zu verstehen sein. Lassen Sie uns dies anhand der Formel in einem Beispiel verstehen
Hier haben wir eine Liste der Punktzahlen, die die Schüler mit ihrer Fächerliste erreicht haben. Wir müssen die Punktzahl für einen bestimmten Schüler (Gary) und ein bestimmtes Fach (Sozialkunde) finden, wie im folgenden Schnappschuss gezeigt.
Der Student value1 muss mit dem Row_header-Array übereinstimmen und der Subject-Wert2 muss mit dem Column_header-Array übereinstimmen.
Verwenden Sie die Formel in der Zelle J6:
| = INDEX ( Tabelle , MATCH ( J5, Zeile, 0 , MATCH ( J4, Spalte, 0 ) ) ) |
Erläuterung:
- Die MATCH-Funktion gleicht den Student-Wert in der Zelle J4 mit dem Zeilenkopfarray ab und gibt seine Position zurück 3 als Zahl.
- Die MATCH-Funktion gleicht den Betreff-Wert in der Zelle J5 mit dem Spaltenüberschriften-Array ab und gibt seine Position zurück 4 als Zahl.
- Die INDEX-Funktion verwendet die Zeilen- und Spaltenindexnummer, schlägt in den Tabellendaten nach und gibt den übereinstimmenden Wert zurück.
- Das Argument vom Typ MATCH ist auf 0 festgelegt. Da die Formel die genaue Übereinstimmung extrahiert.
Hier werden die Werte der Formel als Zellreferenzen und row_header, table und column_header als benannte Bereiche angegeben.
Wie Sie im obigen Schnappschuss sehen können, haben wir die vom Schüler erhaltene Punktzahl erhalten Gary im Betreff Sozialwissenschaften als 36. Und es beweist, dass die Formel gut funktioniert. Wenn Sie Zweifel haben, lesen Sie die folgenden Hinweise zum Verständnis.
Jetzt verwenden wir die ungefähre Übereinstimmung mit Zeilenüberschriften und Spaltenüberschriften als Zahlen. Die ungefähre Übereinstimmung nimmt nur die Zahlenwerte an, da sie auf keine Textwerte angewendet werden kann
Hier haben wir einen Preis von Werten gemäß der Höhe und Breite des Produkts. Wir müssen den Preis für eine bestimmte Höhe (34) und Breite (21) finden, wie im folgenden Schnappschuss gezeigt.
Der Height-Wert1 muss mit dem Row_header-Array übereinstimmen und der Width-Wert2 muss mit dem Column_header-Array übereinstimmen.
Verwenden Sie die Formel in der Zelle K6:
| = INDEX ( Daten , MATCH ( K4, Höhe, 1 , MATCH ( K5, Breite, 1 ) ) ) |
Erläuterung:
- Die MATCH-Funktion gleicht den Height-Wert in der Zelle K4 mit dem Zeilenkopf-Array ab und gibt seine Position zurück 3 als Zahl.
- Die MATCH-Funktion gleicht den Breitenwert in der K5-Zelle mit dem Spaltenkopfarray ab und gibt seine Position zurück 2 als Zahl.
- Die INDEX-Funktion verwendet die Zeilen- und Spaltenindexnummer, schlägt in den Tabellendaten nach und gibt den übereinstimmenden Wert zurück.
- Das Argument vom Typ MATCH ist auf 1 festgelegt. Da die Formel die ungefähre Übereinstimmung extrahiert.
Hier werden die Werte der Formel als Zellreferenzen und row_header, data und column_header als benannte Bereiche angegeben, wie im obigen Snapshot erwähnt.
Sie können auch genaue Übereinstimmungen mit der INDEX- und MATCH-Funktion in Excel suchen. Erfahren Sie mehr darüber, wie Sie die Groß-/Kleinschreibung mit der Funktion INDEX & MATCH in Excel durchführen. Sie können auch mit den Platzhaltern in Excel nach den Teilübereinstimmungen suchen.
Wie Sie im obigen Schnappschuss sehen können, haben wir den Preis nach Höhe erhalten (34) & Breite (21) als 53.10 . Und es beweist, dass die Formel gut funktioniert. Wenn Sie Zweifel haben, lesen Sie die folgenden Hinweise für mehr Verständnis.
Anmerkungen:
- Die Funktion gibt den #NA-Fehler zurück, wenn das Lookup-Array-Argument für die MATCH-Funktion ein 2 D-Array ist, das das Header-Feld der Daten ist…
- Die Funktion stimmt mit dem genauen Wert überein, da das Übereinstimmungstypargument für die MATCH-Funktion 0 ist.
- Die Nachschlagewerte können als Zellbezug oder direkt mit Anführungszeichen ( " ) in der Formel als Argumente angegeben werden.
Ich hoffe, dieser Artikel zur Verwendung der INDEX- und MATCH-Funktion zum Nachschlagen von Werten in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
Verwenden Sie INDEX und MATCH, um den Wert zu suchen : Die INDEX-MATCH-Formel wird verwendet, um dynamisch und präzise einen Wert in einer gegebenen Tabelle zu suchen. Dies ist eine Alternative zur SVERWEIS-Funktion und überwindet die Unzulänglichkeiten der SVERWEIS-Funktion.
Verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen : Um aus mehreren Tabellen zu suchen, können wir einen IFERROR-Ansatz verwenden. Um aus mehreren Tabellen nachzuschlagen, wird der Fehler als Wechsel zur nächsten Tabelle verwendet. Eine andere Methode kann ein If-Ansatz sein.
So führen Sie die Groß-/Kleinschreibung in Excel durch : Die SVERWEIS-Funktion von Excel unterscheidet nicht zwischen Groß- und Kleinschreibung und gibt den ersten übereinstimmenden Wert aus der Liste zurück. INDEX-MATCH ist keine Ausnahme, kann jedoch geändert werden, um die Groß-/Kleinschreibung zu berücksichtigen. Mal sehen wie…
Nachschlagen von häufig erscheinendem Text mit Kriterien in Excel : Die Suche erscheint am häufigsten im Text in einem Bereich, den wir mit der Funktion INDEX-MATCH mit MODE verwenden. Hier ist die Methode.
Populäre Artikel :
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.