Nachschlagen in 2D-Tabelle mit INDEX & MATCH-Funktion

In diesem Artikel erfahren Sie, wie Sie Werte in einer 2D-Tabelle mithilfe einer INDEX-MATCH-MATCH-Funktion in Excel nachschlagen.

Szenario:

Angenommen, Sie müssen mehrere Suchvorgänge in einer Tabelle mit Hunderten von Spalten durchführen. In solchen Fällen nimmt die Verwendung unterschiedlicher Formeln für jede Suche zu viel Zeit in Anspruch. Wie wäre es mit der Erstellung einer dynamischen Nachschlageformel, die Sie anhand der bereitgestellten Kopfzeile nachschlagen können. Ja, wir können dies tun. Diese Formel heißt INDEX MATCH MATCH-Formel oder sagen wir eine 2D-Lookup-Formel.

Wie löst man das Problem?

Damit die Formel zuerst verstanden wird, müssen wir die folgenden Funktionen ein wenig überarbeiten

1. INDEX-Funktion
2. MATCH-Funktion

Die INDEX-Funktion gibt den Wert an einem bestimmten Index in einem Array zurück.

Die MATCH-Funktion gibt den Index des ersten Auftretens des Werts in einem Array (eindimensionales Array) zurück.

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 Nachschlagewerts2 im Spaltenkopffeld zurück. Die Indexnummern werden nun in die INDEX-Funktion eingegeben, um die Werte unter dem Lookup-Wert aus den 2D-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 : Zeilenindex-Array zum Nachschlagen.

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.
Es beweist, dass die Formel gut funktioniert, und bei Zweifeln lesen Sie die folgenden Hinweise zum Verständnis.

Jetzt verwenden wir die ungefähre Übereinstimmung mit Zeilenüberschriften und Spaltenüberschriften als Zahlen. Bei der ungefähren Übereinstimmung werden nur die Zahlenwerte verwendet, da dies nicht auf Textwerte zutrifft

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.

Wie Sie im obigen Schnappschuss sehen können, haben wir den Preis nach Höhe erhalten (34) & Breite (21) als 53.10 . Es beweist, dass die Formel gut funktioniert, und bei Zweifeln lesen Sie die Hinweise unten für mehr Verständnis.
Anmerkungen:

1. 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…
2. Die Funktion stimmt mit dem genauen Wert überein, da das Übereinstimmungstypargument für die MATCH-Funktion 0 ist.
3. Die Nachschlagewerte können als Zellbezug oder direkt mit Anführungszeichen ( " ) in der Formel als Argumente angegeben werden.

Ich hoffe, Sie haben verstanden, wie Sie das Lookup in 2D-Tabelle mit der INDEX & MATCH-Funktion in Excel verwenden. Weitere Artikel zum Excel-Suchwert finden Sie hier. Stellen Sie Ihre Fragen gerne unten im Kommentarfeld. Wir werden Ihnen sicherlich helfen.

Verwenden Sie INDEX und MATCH, um den Wert zu suchen : INDEX & MATCH-Funktion zum Nachschlagen von Werten nach Bedarf.

Summenbereich mit INDEX in Excel : Verwenden Sie die INDEX-Funktion, um die SUMME der erforderlichen Werte zu ermitteln.

So verwenden Sie die SUM-Funktion in Excel : Ermitteln Sie die SUMME von Zahlen mit der SUM-Funktion, die mit einem Beispiel erklärt wird.

So verwenden Sie die INDEX-Funktion in Excel : Finden Sie den INDEX des Arrays mit der INDEX-Funktion, die mit einem Beispiel erklärt wird.

So verwenden Sie die MATCH-Funktion in Excel : Finden Sie die MATCH im Array mit dem INDEX-Wert in der MATCH-Funktion, die mit einem Beispiel erklärt wird.

So verwenden Sie die LOOKUP-Funktion in Excel : Finden Sie den Lookup-Wert im Array mithilfe der LOOKUP-Funktion, die mit einem Beispiel erklärt wird.

So verwenden Sie die SVERWEIS-Funktion in Excel : Suchen Sie den Lookup-Wert im Array mithilfe der SVERWEIS-Funktion, die mit einem Beispiel erklärt wird.

So verwenden Sie die HLOOKUP-Funktion in Excel : Suchen Sie den Lookup-Wert im Array mithilfe der HLOOKUP-Funktion, die mit einem Beispiel erklärt wird.

populäre Artikel

50 Excel-Shortcut zur Steigerung Ihrer Produktivität

Bearbeiten einer Dropdown-Liste

Absolute Referenz in Excel

Bei bedingter Formatierung

Wenn mit Wildcards

Vlookup nach Datum

Vor- und Nachname in Excel eintragen