Nachschlagen in 2D-Tabelle mit dem Index & MATCH Funktion
In diesem Artikel erfahren Sie, wie Sie mithilfe einer INDEX-MATCH-MATCH-Funktion in Excel nach Werten in einer 2D-Tabelle suchen.
Szenario:
Angenommen, Sie müssen mehrere Suchvorgänge in einer Tabelle mit Hunderten von Spalten durchführen. In solchen Fällen dauert die Verwendung unterschiedlicher Formeln für jede Suche zu lange. Wie wäre es mit einer dynamischen Suchformel, die Sie anhand des bereitgestellten Headers nachschlagen können? Ja, das können wir.
Diese Formel heißt INDEX MATCH MATCH-Formel oder eine 2D-Suchformel.
Wie löse ich das Problem?
Damit die Formel zuerst verstanden wird, müssen wir die folgenden Funktionen ein wenig überarbeiten. link: / lookup-formulas-excel-index-function [INDEX-Funktion]
-
link: / lookup-formulas-excel-match-function [MATCH-Funktion]
link: / lookup-formulas-excel-index-function [Die INDEX-Funktion] gibt den Wert an einem bestimmten Index in einem Array zurück.
link: / lookup-formulas-excel-match-function [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 Übereinstimmungsfunktion gibt den Index des Suchwerts1 im Zeilenkopffeld zurück. Eine weitere MATCH-Funktion gibt den Index des Suchwerts2 im Spaltenüberschriftenfeld zurück. Die Indexnummern werden nun in die INDEX-Funktion eingegeben, um die Werte unter dem Suchwert aus den 2D-Tabellendaten abzurufen.
Generische Formel:
= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )
Daten: Array von Werten in der Tabelle ohne Header lookup_value1: Wert, der im row_header gesucht werden soll.
row_headers: Zeilenindex-Array zum Nachschlagen.
lookup_value1: Wert, der im column_header gesucht werden soll.
column_headers: Spaltenindexarray zum Nachschlagen.
Beispiel:
Die obigen Aussagen können kompliziert zu verstehen sein. Lassen Sie uns dies anhand der Formel in einem Beispiel verstehen. Hier finden Sie eine Liste der Ergebnisse, die die Schüler mit ihrer Themenliste erzielt haben. Wir müssen die Punktzahl für einen bestimmten Schüler (Gary) und ein bestimmtes Fach (Sozialkunde) ermitteln, wie im folgenden Schnappschuss gezeigt.
Der Student-Wert1 muss mit dem Array Row_header übereinstimmen, und der Betreff-Wert2 muss mit dem Array Column_header übereinstimmen.
Verwenden Sie die Formel in der J6-Zelle:
= INDEX ( table , MATCH ( J5, row, 0 , MATCH ( J4, column, 0 ) ) )
Erläuterung:
Die MATCH-Funktion vergleicht den Student-Wert in der J4-Zelle mit dem Zeilenüberschriften-Array und gibt seine Position 3 * als Zahl zurück.
Die MATCH-Funktion vergleicht den Betreff-Wert in der J5-Zelle mit dem Spaltenüberschriften-Array und gibt seine Position 4 * als Zahl zurück.
-
Die INDEX-Funktion verwendet die Zeilen- und Spaltenindexnummer, sucht 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 Werte für die 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 Punktzahl von Student Gary in Subject Social Studies als 36 erhalten.
Es beweist, dass die Formel gut funktioniert, und für Zweifel siehe die folgenden Hinweise zum Verständnis.
Jetzt werden wir die ungefähre Übereinstimmung mit Zeilen- und Spaltenüberschriften als Zahlen verwenden. Bei der ungefähren Übereinstimmung werden nur die Zahlenwerte verwendet, da dies auf keinen Fall für Textwerte gilt. Hier haben wir einen Preis für Werte gemäß Höhe und Breite des Produkts.
Wir müssen den Preis für eine bestimmte Höhe (34) und Breite (21) ermitteln, wie im folgenden Schnappschuss gezeigt.
Der Height-Wert1 muss mit dem Row_header-Array und der Width-Wert2 mit dem Column_header-Array übereinstimmen.
Verwenden Sie die Formel in der K6-Zelle:
= INDEX (data , MATCH (K4, Height, 1 , MATCH ( K5, Width, 1 ) ) )
Erläuterung:
Die MATCH-Funktion gleicht den Höhenwert in der K4-Zelle mit dem Zeilenkopf-Array ab und gibt seine Position 3 * als Zahl zurück.
Die MATCH-Funktion stimmt den Width-Wert in der K5-Zelle mit dem Spaltenüberschriften-Array ab und gibt seine Position 2 * als Zahl zurück.
-
Die INDEX-Funktion verwendet die Zeilen- und Spaltenindexnummer, sucht 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 Werte für die 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, der durch Höhe (34) und Breite (21) erhalten wird, als 53,10. Es beweist, dass die Formel gut funktioniert, und im Zweifelsfall lesen Sie die Hinweise unten, um mehr Verständnis zu erhalten.
Anmerkungen:
-
Die Funktion gibt den Fehler #NA zurück, wenn das Lookup-Array-Argument für die MATCH-Funktion ein 2D-Array ist, das das Header-Feld der Daten ist.
-
Die Funktion stimmt mit dem genauen Wert überein, da das Match-Typ-Argument für die MATCH-Funktion 0 ist.
-
Die Suchwerte können als Zellreferenz oder direkt mit dem Anführungszeichen („) in der Formel als Argumente angegeben werden.
Ich hoffe, Sie haben verstanden, wie die Tabelle“ Suche in 2D „mithilfe der Funktion INDEX & MATCH in Excel verwendet wird. Weitere Informationen Artikel in Excel-Suchwert hier. Bitte geben Sie Ihre Fragen unten im Kommentarfeld an.
Wir werden Ihnen sicherlich helfen.
Verwandte Artikel
link: / lookup-formulas-use-index-and-match-to-lookup-value [Verwenden Sie INDEX und MATCH, um den Wert zu suchen]: INDEX & MATCH-Funktion, um den Wert nach Bedarf zu suchen.
` link: / summieren-summenbereich-mit-index-in-excel [SUM-bereich mit INDEX in Excel] `: Verwenden Sie die INDEX-Funktion, um die SUMME der erforderlichen Werte zu finden.
` link: / count- Excel-Summen-Funktion [Verwendung der SUM-Funktion in Excel] `: Ermitteln Sie die SUMME von Zahlen mithilfe der im Beispiel erläuterten SUMME-Funktion.
How um die INDEX-Funktion zu verwenden in Excel : Ermitteln Sie den INDEX des Arrays mithilfe der im Beispiel erläuterten INDEX-Funktion.
link: / lookup-formulas-excel-match-function [Verwendung der MATCH-Funktion in Excel]: Suchen Sie den MATCH im Array mithilfe des INDEX-Werts in der MATCH-Funktion, die anhand eines Beispiels erläutert wurde.
link: / lookup-formulas-excel-lookup-function [Verwendung der LOOKUP-Funktion in Excel]: Ermitteln Sie den Lookup-Wert im Array mithilfe der im Beispiel erläuterten LOOKUP-Funktion.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Ermitteln Sie den Suchwert im Array mithilfe der im Beispiel erläuterten VLOOKUP-Funktion.
link: / lookup-formulas-hlookup-function-in-excel [Verwendung der HLOOKUP-Funktion in Excel]: Ermitteln Sie den Lookup-Wert im Array mithilfe der im Beispiel erläuterten HLOOKUP-Funktion.
Beliebte Artikel
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfung zur Steigerung Ihrer Produktivität]
link: / excel-generals-wie-man-eine-dropdown-liste-in-microsoft-excel bearbeitet [Dropdown-Liste bearbeiten]
link: / excel-bereichsname-absolute-referenz-in-excel [Absolute referenz in Excel]
link: / tips-bedingte-formatierung-mit-if-Anweisung [Wenn mit bedingter Formatierung]
link: / logische Formeln-wenn-Funktion-mit-Platzhaltern [Wenn mit Platzhaltern]
link: / lookup-formulas-vlookup-by-date-in-excel [Vlookup-by-date]
link: / excel-text-editing-und-format-join-first-and-last-name-in-excel [Vor- und Nachname in Excel verbinden]