Wie verwende ich die vlookup match-kombination?
Die Kombination aus VLOOKUP und MATCH ist wie eine Supermacht. Wie wir alle wissen, ist SVERWEIS eine der beliebtesten Funktionen .
RECHTS?
Dies kann Ihnen helfen, schnell einen Wert in einer Spalte zu finden. Wenn Sie es jedoch häufiger verwenden, werden Sie einige Probleme erkennen. Und das größte Problem ist , dass es nicht dynamisch ist.
In VLOOKUP ist col_index_no ein statischer Wert, weshalb VLOOKUP nicht als dynamische Funktion funktioniert . Und hier müssen Sie VLOOKUP mit MATCH kombinieren.
Wenn Sie mit mehrspaltigen Daten arbeiten, ist es mühsam, deren Referenz zu ändern. Sie müssen dies manuell tun (die Spaltennummer ändern).
Der beste Weg, dieses Problem zu beheben, besteht darin, die MATCH-Funktion in VLOOKUP für col_index_number zu verwenden. Heute erzähle ich Ihnen in diesem Artikel alles, was Sie wissen müssen, um diese Kombinationsformel zu verwenden.
Probleme mit SVERWEIS
Ich habe die beiden Hauptgründe gefunden, eine Kombination dieser beiden Funktionen zu schaffen.
1. Statische Referenz
Schauen Sie sich einfach die Datentabelle unten an, in der Sie die 12-Monats-Umsätze für vier verschiedene Mitarbeiter sehen.
Angenommen, Sie möchten nach „Johns“ Angebot für den Monat Februar suchen. Die Formel sollte so aussehen.
=VLOOKUP("May",A1:E13,2,0)Und in dieser Formel haben Sie 2 als col_index_num erwähnt, weil Johns Verkauf in der zweiten Spalte steht. Aber was werden Sie tun, wenn Ihr Chef Ihnen sagt, Sie sollen den Verkaufswert für „Peter“ ermitteln?
Sie müssen den Wert in col_index_num ändern, da er nicht dynamisch ist.
2. Spalten hinzufügen oder entfernen
Denken Sie jetzt anders. Sie müssen direkt vor Johns Spalte eine neue Spalte für einen neuen Mitarbeiter hinzufügen.
Und hier ist Johns Spaltennummer 3 und Ihr Formelergebnis ist falsch.
Da col_index_num ein statischer Wert ist, müssen Sie ihn auch hier manuell von 2 auf 3 ändern und noch einmal, wenn Sie etwas anderes benötigen.
An diesem Punkt wissen Sie eindeutig, dass Sie col_index_num dynamisch machen müssen. Und dafür ersetzen Sie es am besten durch die MATCH-Funktion.
Warum Match-Funktion?
Bevor Sie VLOOKUP und MATCH kombinieren, müssen Sie die Match-Funktion und ihre Funktionsweise verstehen. Die grundlegende Verwendung von MATCH besteht darin, die Zellennummer des Suchwerts in einem Bereich zu finden.
Syntax: MATCH(lookup_value,lookup_array,[match_type])
Es verfügt hauptsächlich über drei Argumente: den Suchwert, einen Bereich zum Durchsuchen des Werts und den Übereinstimmungstyp, um eine genaue Übereinstimmung oder eine Fuzzy-Übereinstimmung anzugeben.
In den folgenden Daten suche ich beispielsweise mit der Match-Funktion aus einer Kopfzeile nach dem Namen „John“.
Und im Ergebnis wurde 2 zurückgegeben, da sich der Name in der zweiten Zelle der Zeile befindet.
VLOOKUP und MATCH zusammen
Jetzt ist es an der Zeit, VLOOKUP und MATCH zusammenzuführen. Fahren wir also mit unserem vorherigen Beispiel fort.
Lassen Sie uns zunächst eine Formel erstellen, die beide Funktionen verwendet. Anschließend werden wir verstehen, wie diese beiden zusammenarbeiten.
Schritte zum Erstellen dieser Kombinationsformel:
- Geben Sie zunächst in einer Zelle den Monatsnamen und in einer anderen Zelle den Namen des Mitarbeiters ein.
- Geben Sie anschließend die folgende Formel in die dritte Zelle ein.
=VLOOKUP(C15,A1:E13,MATCH(C16,A1:E1,0),0)
In der Formel oben haben Sie VLOOKUP verwendet, um den Monat MAI zu finden, und für das Argument col_index_num haben Sie die Match-Funktion anstelle eines statischen Werts verwendet.
Und in der Match-Funktion haben Sie „John“ (Name des Mitarbeiters) als Suchwert verwendet.
Hier hat die Match-Funktion die Zellennummer für „John“ in der Zeile darüber zurückgegeben. Danach verwendete VLOOKUP diese Zellennummer, um den Wert zurückzugeben.
Einfach ausgedrückt teilt die MATCH-Funktion VLOOKUP die Spaltennummer mit, um den Wert zu erhalten.
Probleme gelöst?
Oben haben Sie von zwei verschiedenen Problemen erfahren, die durch die statische col_index_num verursacht werden.
Und dafür haben Sie VLOOKUP und MATCH kombiniert. Jetzt müssen wir prüfen, ob diese Probleme behoben sind oder nicht.
1. Statische Referenz
Sie haben in der Match-Funktion auf den Namen des Mitarbeiters verwiesen, um die Spaltennummer für SVERWEIS zu erhalten.
Wenn Sie den Mitarbeiternamen in der Zelle ändern, ändert die Match-Funktion die Spaltennummer. Und wenn Sie den Wert eines anderen Mitarbeiters ermitteln möchten, müssen Sie den Namen des Mitarbeiters in der Zelle ändern.
Auf diese Weise haben Sie eine dynamische col_index_number.
Endlich müssen Sie die Formel nicht immer wieder ändern.
2. Spalten hinzufügen oder entfernen
Vor dem Hinzufügen einer neuen Spalte befanden sich Johns Daten in der 2. Spalte und die Match-Funktion gab 2 zurück. Und nach dem Einfügen einer neuen Spalte befanden sich Johns Daten in der 3. Spalte und die Match-Funktion gab 3 zurück.
Wenn Sie eine neue Spalte für einen neuen Mitarbeiter hinzufügen, wird der Formelwert nicht geändert, da die Match-Funktion seinen Wert aktualisiert.
So erhalten Sie auch beim Einfügen/Löschen einer Spalte immer die richtige Spaltennummer. Die MATCH-Funktion gibt die richtige Spaltennummer zurück.