In diesem Artikel erfahren Sie, wie Sie mit der INDEX & MATCH-Funktion die n-te Übereinstimmung in der Tabelle suchen.
Szenario:
Zum Beispiel müssen wir die numerisch n-te Übereinstimmung und die entsprechenden Ergebnisse aus der Tabelle finden. Wir brauchen eine allgemeine Formel, die beim Finden der n-ten Übereinstimmung hilft, um den erforderlichen Wert zu erhalten.
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 der niedrigsten Übereinstimmung aus dem Bereich zurück. Die INDEX-Funktion nimmt den Zeilenindex als Argument und gibt die entsprechenden erforderlichen Ergebnisse zurück. Diese Funktion findet die
Generische Formel:
= INDEX ( data , MATCH ( SMALL (range, n), range, match_type ) , col_num )
Daten : Array von Werten in der Tabelle ohne Überschriften
Bereich : lookup_array für die niedrigste Übereinstimmung
n : Zahl, n-te Übereinstimmung
match_type : 1 (genau oder nächstkleiner) oder 0 (genaue Übereinstimmung) oder -1 (genau oder nächstgrößer)
spalte_num : Spaltennummer, erforderlicher Wert zum Abrufen aus der Tabellenspalte.
Beispiel:
Die obigen Aussagen können verwirrend sein. Lassen Sie uns dies anhand der Formel in einem Beispiel verstehen
Hier haben wir eine Tabelle mit Details zum Weltkontinent. Wir müssen den Ländercode aus der angegebenen Tabelle aus dem Nachschlagewert als Ländername finden.
Benannter Bereich, der für das Array Tabelle (E5: I10) und Bereich (I5: I10) verwendet wird.
Hier haben wir stattdessen den benannten Bereich für das Zellreferenzarray verwendet, da er leicht zu verstehen ist. Wir müssen nach allen Details suchen, bei denen der Preiswert das Minimum oder das niedrigste im Bereich ist.
Verwenden Sie die obige Formel, um das erste Detail zu erhalten, das groß ist
Verwenden Sie die Formel in der Zelle G6:
= INDEX ( tabelle ,MATCH ( KLEIN (Reichweite, 1) ,Reichweite , 0 ) , 4 )
Benannte Bereiche
Tisch (E5:I10)
Reichweite (I5:I10)
Erläuterung:
- Die SMALL-Funktion findet die erste niedrigste Übereinstimmung im Bereich und gibt den Wert an die MATCH-Funktion zurück.
- Die MATCH-Funktion stimmt mit der genauen Mindestwertübereinstimmung im Preisbereich überein und gibt ihren Zeilenindex an die INDEX-Funktion zurück.
- Die INDEX-Funktion findet den Capital-Wert mit dem ROW-Index und der 4. Spalte in der Tabelle mit dem Namen range.
Die Formel gibt alle Ergebnisse für den ersten niedrigsten Wert zurück. Kopieren Sie nun die Formel, um die zweitkleinste oder drittniedrigste zu erhalten, müssen Sie nur das n-Argument unter der Funktion SMALL ändern, um die unterschiedlichen Ergebnisse zu erhalten.
Ändern Sie nun einfach den n-Wert und erhalten Sie die Ergebnisse wie unten gezeigt.
Wie Sie aus dem obigen Snapshot sehen können, haben wir alle Details erhalten, die dem n-ten Wert in der Tabelle entsprechen. Extrahieren Sie Details aus der Tabelle mit der oben angegebenen Formel.
Beispiel:
Sie können auch die LARGE-Funktion verwenden, um die n-te Übereinstimmung von der obersten oder die n-höchste Übereinstimmung aus dem Bereich zu erhalten und die entsprechenden Ergebnisse zurückzugeben.
Generische Formel:
= INDEX ( data , MATCH ( LARGE (range, n), range, match_type ) , col_num )
Daten : Array von Werten in der Tabelle ohne Überschriften
Bereich : lookup_array für die höchste Übereinstimmung
n : Zahl, n-te Übereinstimmung
match_type : 1 (genau oder nächstkleiner) oder 0 (genaue Übereinstimmung) oder -1 (genau oder nächstgrößer)
spalte_num : Spaltennummer, erforderlicher Wert zum Abrufen aus der Tabellenspalte.
Beispiel:
Die obigen Aussagen können verwirrend sein. Lassen Sie uns dies anhand der Formel in einem Beispiel verstehen
Hier haben wir eine Tabelle mit Details zum Weltkontinent. Wir müssen den Ländercode aus der angegebenen Tabelle aus dem Nachschlagewert als Ländername finden.
Benannter Bereich, der für das Array Tabelle (E5: I10) und Bereich (I5: I10) verwendet wird.
Hier haben wir stattdessen den benannten Bereich für das Zellreferenzarray verwendet, da er leicht zu verstehen ist. Wir müssen nach allen Details suchen, bei denen der Preiswert im Bereich am höchsten oder höchsten ist.
Verwenden Sie die obige Formel, um das erste Detail zu erhalten, das groß ist
Verwenden Sie die Formel in der Zelle G6:
= INDEX ( Tabelle ,MATCH ( GROSS (Reichweite, 1) ,Reichweite, 0 ) , 4 )
Benannte Bereiche
Tisch (E5:I10)
Reichweite (I5:I10)
Erläuterung:
- Die LARGE-Funktion findet die erste höchste Übereinstimmung im Bereich und gibt den Wert an die MATCH-Funktion zurück.
- Die MATCH-Funktion stimmt mit der genauen n-ten Übereinstimmung des Übereinstimmungswerts im Preisbereich überein und gibt ihren Zeilenindex an die INDEX-Funktion zurück.
- Die INDEX-Funktion findet den Capital-Wert mit dem ROW-Index und der 4. Spalte in der Tabelle mit dem Namen range.
Die Formel gibt alle Ergebnisse für den ersten niedrigsten Wert zurück. Kopieren Sie nun die Formel, um die zweitkleinste oder drittniedrigste zu erhalten, müssen Sie nur das n-Argument unter der Funktion SMALL ändern, um die unterschiedlichen Ergebnisse zu erhalten.
Ändern Sie nun einfach den n-Wert und erhalten Sie die Ergebnisse wie unten gezeigt.
Wie Sie aus dem obigen Snapshot sehen können, haben wir alle Details erhalten, die dem n-ten Wert in der Tabelle entsprechen. Extrahieren Sie Details aus der Tabelle mit der oben angegebenen Formel.
Hier sind einige Beobachtungen bei der Verwendung der obigen Formel.
Anmerkungen:
- Der Wert von n darf nicht kleiner als 1 oder größer als die Bereichslänge sein
- Die Funktion gibt den #NA-Fehler zurück, wenn das Lookup-Array-Argument für die MATCH-Funktion nicht die gleiche Länge wie das Tabellen-Array hat.
- Die Formel gibt einen Fehler zurück, wenn lookup_value nicht mit dem Wert in der Tabelle lookup_array übereinstimmt.
- 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, Sie haben verstanden, wie Sie mit der INDEX & MATCH-Funktion die n-te Übereinstimmung in der Tabelle suchen. Weitere Artikel zum Excel-Suchwert finden Sie hier. Stellen Sie Ihre Fragen gerne unten im Kommentarfeld. Wir werden Ihnen sicherlich helfen.
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 : 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 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.
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