Normalerweise gibt die SVERWEIS-Funktion die erste gefundene Übereinstimmung zurück. Aber die Sache wird kompliziert, wenn es mehr als eine Übereinstimmung gibt und wir ein bestimmtes Vorkommen erhalten möchten, z. B. die zweite Übereinstimmung.
In diesem Artikel erfahren Sie, wie Sie mit SVERWEIS ein zweites, drittes oder N-tes Match erhalten. Um dies zu erreichen, müssen wir eine Hilfsspalte verwenden. Wenn Sie keine Hilfsspalte verwenden möchten, da dies nicht mit allen Daten möglich ist, können Sie den INDEX-MATCH-Ansatz verwenden. Wenn Sie jedoch eine Hilfsspalte verwenden können, finden Sie hier die generische Formel, um die N-te Übereinstimmung mit der SVERWEIS-Funktion zu finden.
Generische Formel
| =SVERWEIS(Suchwert&Vorkommen,Tabellen-Array,Spalte,0) |
Lookup-Wert: Es ist der Suchwert, nach dem Sie suchen möchten.
Auftreten:Das Vorkommen des Nachschlagewerts, den Sie abgleichen möchten.
Tabellenarray:Es ist das Tabellenarray, in dem Sie nach Daten suchen möchten. Stellen Sie sicher, dass die erste Spalte in dieser Tabelle die Hilfsspalte ist.
Spalte: Die Spaltennummer im Tabellen-Array aus dem Sie den Wert abrufen möchten.
0: Es ist für eine genaue Übereinstimmung. Wenn Sie keine genaue Übereinstimmung wünschen, lassen Sie sie weg oder verwenden Sie 1 oder WAHR.
Jetzt verwenden wir es in einem Beispiel:
Beispiel: Finden Sie den zweiten Verkauf eines Mitarbeiters in der Excel-Tabelle
Wir haben eine Tabelle, die die Verkäufe von Verkäufern in verschiedenen Monaten aufzeichnet. Die Namen der Verkäufer können sich im Protokoll wiederholen. Wir müssen das zweite Vorkommen von Micky abgleichen und dann die Verkäufe von Jan erhalten.
Beachten Sie, dass wir eine Hilfsspalte hinzugefügt haben. Diese Spalte schreibt den Namen des Verkäufers und verkettet dann das Vorkommen dieses Namens. Wir haben die laufende Zählung verwendet, um das Vorkommen zu erhalten.
Die Formel in der Hilfsspalte lautet:
| =B3&ZÄHLENWENN($B$3:B3,B3) |
Damit haben wir den Tisch fertig. Hier befindet sich der Nachschlagewert in Zelle P3, die Vorkommensnummer befindet sich in Q3, das Tabellenarray ist A3:N10 und die Spaltennummer ist 3.
Schreiben Sie nun diese Formel in Zelle P4:
| =SVERWEIS(P3&Q3,A3:N10,3,0) |
Drücken Sie die Eingabetaste und bam! Sie haben Ihren Nachschlagewert des benötigten Vorkommens.
Wie funktioniert es?
Die Funktionalität ist einfach. Anfänglich haben wir keine eindeutige ID, die als Suchwert verwendet werden kann. Also erstellen wir einen. Wir verwenden den Namen und die Formel für die laufende Zählung, um eine eindeutige ID zu erstellen. Wir stellen sicher, dass dies die erste Spalte in der Tabelle von links ist, da wir Werte aus den Spalten rechts davon abrufen werden.
Jetzt erstellt die Nachschlageformel die eindeutige ID mit dem Verkettungsoperator & (P3&Q3). Dies macht es zu einer einzigartigen ID
Als nächstes nimmt die SVERWEIS-Formel dies als Nachschlagewert und sucht nach seiner Position in der TabelleA3:N10. Hier, SVERWEIS findet den Wert in der 6. Zeile der Tabelle. Jetzt geht es in die 3. Spalte und gibt den Wert zurück.
Dies ist der einfachste Weg, um die N-te Übereinstimmung in Excel zu erhalten. Aber es ist nicht immer machbar. Es fügt dem Blatt zusätzliche Daten und Berechnungen hinzu, die die Excel-Datei schwer und langsam machen können.
Wenn Sie über eine kleine Datenmenge verfügen, ist dies großartig, aber bei großen Daten möchten Sie möglicherweise eine unabhängige Formel verwenden, die keine Hilfsspalte benötigt. In diesem Fall können Sie eine Matrixformel verwenden, die INDEX- und MATCH-Funktionen verwendet.
Also ja, Leute, so können Sie das N-te Match in Excel mit der SVERWEIS-Funktion abrufen. Ich hoffe, ich war erklärend genug und es war hilfreich. Wenn Sie Zweifel an diesem Artikel oder einem anderen Thema im Zusammenhang mit Excel/VBA haben, fragen Sie im Kommentarbereich unten nach.
In Verbindung stehende Artikel:
Suchen Sie die n-te Übereinstimmung mit der Funktion INDEX & MATCH | Um eine n-te Übereinstimmung zu erhalten, ohne eine Hilfsspalte zu verwenden, verwenden wir die INDEX- und MATCH-Funktion. Wir verwenden die boolesche Logik, um den Index aus der Tabelle zu erhalten.
So suchen Sie nach mehreren Werten | Um alle übereinstimmenden Werte aus einer Liste abzurufen, verwenden wir die Funktion INDEX MATCH. SVERWEIS kann nur dann mehrere Werte abrufen, wenn wir eine Hilfsspalte verwenden.
Lookup-Wert mit mehreren Kriterien | Wenn Sie mehr als eine Lookup-Tabelle suchen müssen, wie verwenden Sie SVERWEIS aus zwei oder mehr Lookup-Tabellen. Dieser Artikel löst dieses Problem ganz einfach
Nachschlagewert mit mehreren Kriterien | Wir können einfach die SVERWEIS-Funktion verwenden. Aber wenn Sie diese eindeutige Spalte in Ihren Daten nicht haben und in mehreren Spalten nach einem Wert suchen müssen, hilft SVERWEIS nicht weiter
So suchen Sie die Adresse in Excel |Es kann vorkommen, dass Sie die Adresse der Zelle abrufen möchten, aus der ein Wert abgerufen wird. Mit dieser Adresse können Sie mit der OFFSET-Funktion einfach benachbarte Werte abrufen
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| 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 Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.