Mit VLOOKUP bekommen wir immer das erste Match. Das gleiche passiert mit der INDEX MATCH-Funktion. Wie können wir das zweite Match oder das 3. oder n. VLOOKUP?

In diesem Artikel erfahren Sie, wie Sie das N-te Auftreten eines Wertes im Bereich ermitteln.

0055

Generische Formel

{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}

Hinweis: Dies ist ein _ _array Formel . Sie müssen es mit STRG + UMSCHALT + EINGABETASTE eingeben . Bereich: Der Bereich, in dem Sie die n-te Wertposition * suchen möchten.

Wert: Der Wert, von dem Sie die n-te Position in diesem Bereich suchen. First_cell_in_range: Die erste Zelle in diesem Bereich. Wenn der Bereich A2: A10 ist, ist die erste Zelle im Bereich A2. *

n: die vorkommende Anzahl von Werten.

Sehen wir uns ein Beispiel an, um die Dinge klarer zu machen.

===

Beispiel: Finden Sie die zweite Übereinstimmung in Excel

Also hier habe ich diese Liste von Namen im Excel-Bereich A2: A10. Ich habe diesen Bereich als Namen benannt. Jetzt möchte ich die Position des zweiten Vorkommens von „Rony“ in Namen ermitteln.

0056

Im Bild oben sehen wir, dass es sich im Bereich A2: A10 (Namen) auf der 7. Position befindet. Jetzt müssen wir seine Position mit einer Excel-Formel ermitteln.

Wenden Sie die obige generische Formel in C2 an, um das zweite Vorkommen von Rony in der Liste nachzuschlagen.

{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}

Geben Sie es mit STRG + UMSCHALT + EINGABETASTE ein.

0057

Und wir haben eine Antwort. Es zeigt 7, was richtig ist. Wenn Sie den Wert von n in 3 ändern, erhalten Sie 8. Wenn Sie den Wert von n ändern, der größer als das Auftreten des Werts im Bereich ist, wird der Fehler #NUM zurückgegeben.

===

Wie funktioniert es?

Nun, es ist ganz einfach. Sehen wir uns jeden Teil einzeln an.

link: / tips-if-condition-in-excel [IF] (names = „Rony“, link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / Lookup-and-Reference-Excel-Zeilenfunktion [ROW] `(A2) + 1 *)

:

In IF gibt names = „Rony“ ein Array von TRUE und FALSE zurück. TRUE, wenn eine Zelle in Bereichsnamen (A2: A10) mit „Rony“ übereinstimmt. \ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.

Nächster ROW (Namen) –ROW (A2) +1: ROW (Namen *):

Hier gibt die ROW-Funktion die Zeilennummer jeder Zelle in Namen zurück.

\ {2; 3; 4; 5; 6; 7; 8; 9; 10}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) * Dann subtrahieren wir die Zeile Anzahl von A2 von jedem Wert im gegebenen Array. Dies gibt uns eine Reihe von Seriennummern ab 0.

\ {0; 1; 2; 3; 4; 5; 6; 7; 8}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: To get Seriennummern ab 1 addieren wir 1 zu jedem Wert in diesem Array. Dies gibt uns die Seriennummer ab 1.

\ {1; 2; 3; 4; 5; 6; 7; 8; 9}.

Jetzt haben wir IF (\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, \ {1; 2; 3; 4; 5; 6; 7; 8; 9}).

Dies löst \ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE} auf.

Jetzt haben wir die Formel gelöst, um zu verknüpfen: / statistische Formeln-Excel-kleine-Funktion [SMALL] (\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}, 2).

Jetzt gibt SMALL den zweitkleinsten Wert im Bereich zurück, nämlich 7.

Wie benutzen wir es?

Es stellt sich die Frage: Was bringt es, einen Rohindex für das n-te Match zu erhalten? Es wäre nützlicher, wenn Sie verwandte Informationen vom n-ten Wert abrufen könnten. Nun, das kann man auch machen. Wenn wir den Wert aus dem Wert der n-ten Übereinstimmung der benachbarten Zelle in Bereichsnamen (A2: A10) erhalten möchten.

{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}

Also ja Leute, so kann man das n-te Match in einem Bereich bekommen. Ich hoffe ich war erklärend genug. Wenn Sie Zweifel an diesem Artikel oder einem anderen Thema im Zusammenhang mit Excel / VBA haben, schreiben Sie in den Kommentaren unten.

Verwandte Artikel:

link: / Informationsformeln-wie-man-sequentielle-Zeilennummer-in-Excel erhält [wie man sequentielle Zeilennummer in Excel erhält]

link: / lookup-formeln-vlookup-top-5-werte-mit-doppelten-werten-mit-index-match-in-excel [Vlookup-Top-5-Werte mit doppelten Werten mit INDEX-MATCH in Excel]

link: / lookup-formulas-vlookup-multiple-values ​​[VLOOKUP Multiple Values]

link: / lookup-formulas-use-index-and-match-to-lookup-value [Verwenden Sie INDEX und MATCH, um den Wert zu suchen]

link: / Lookup-Formeln-Lookup-Wert-mit-mehreren-Kriterien [Lookup-Wert mit mehreren Kriterien]

Beliebte Artikel:

link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel]

link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016]

link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]