Index Match: Die bessere Alternative zu Vlookup

SVERWEIS ist eine dieser Funktionen, die Excel großartig machen. Obwohl viele es zunächst nicht intuitiv finden, wird es bald zur zweiten Natur und unverzichtbar. Einige finden sogar, dass es lebensverändernd ist. SVERWEIS hat jedoch seine Probleme, zum einen liest es nur von links nach rechts. Wenn Sie links neben einer Referenzspalte nach einem Wert suchen müssen, müssen Sie Ihr Arbeitsblatt neu anordnen, um die Funktion verwenden zu können.

SVERWEIS durchsucht auch viele Spalten, wenn Sie in Wirklichkeit nur zwei davon verwenden. die Standardsyntax verweist nur auf relative Spaltenwerte, wenn Sie die Tabelle ändern, haben Sie erneut Pech und müssen ein wenig umorganisieren und Funktionen neu schreiben, was in großen Arbeitsmappen sehr mühsam sein kann. Dies führt auch zu Problemen beim Kopieren der Funktion in andere Spalten.

Es gibt jedoch eine Alternative, INDEX + MATCH. Es repliziert nicht nur die Funktionalität, sondern hat auch seine eigenen Vorteile und Boni. Es ist etwas, das Sie vielleicht noch nie gehört haben (vor allem, wenn Sie nicht zum Spaß über Excel lesen), aber am Ende dieses Artikels werden Sie in der Lage sein, es in die Praxis umzusetzen und die Früchte zu ernten.

INDEX + MATCH aufschlüsseln

Was viele Benutzer übersehen, ist, dass Excel-Funktionen miteinander verkettet und kombiniert werden können, um etwas noch leistungsfähiger zu machen als die Bestandteile. Jede Funktion benötigt ein Argument, wenn Sie etwas mehr Leistung oder erweiterte Funktionalität benötigen, können diese Argumente aus anderen Funktionen bestehen.

INDEX

=INDEX(Array, Zeilennummer)

Die INDEX-Funktion nimmt ein Benutzerarray, einen Satz von Werten, wie z. B. eine Spalte, und gibt den Wert der Zelle an dieser bestimmten Position zurück. Zum Beispiel habe ich eine Datenspalte, die die Positionen eines Rennens aufzeichnet, das zu einem bestimmten Zeitpunkt in der Vergangenheit ausgetragen wurde. Ich möchte wissen, welcher Fahrer den dritten Platz belegt hat, um diesen zu finden, kann ich einfach eingeben,

=INDEX(B2:B6, 3)

und das wird Rusty Shackleford zurückgeben.

Einfach aber nützlich. Wir haben jetzt die Möglichkeit, Werte innerhalb eines Arrays zurückzugeben.

SPIEL

=MATCH(Lookup-Wert, Lookup-Array, Übereinstimmungstyp)

MATCH() ist eine weitere einfache Funktion, die sich auf die gerade behandelte INDEX-Funktion bezieht. Anstatt jedoch einen Wert zurückzugeben, wird stattdessen eine Zahl zurückgegeben, die die relative Position eines Werts innerhalb eines Arrays angibt. MATCH erfordert die folgenden Argumente; der Wert, das zu durchsuchende Array und der Übereinstimmungstyp. Der Übereinstimmungstyp wird normalerweise auf 0 gesetzt, hängt jedoch von Ihrer Verwendung ab. 0 bedeutet, dass Sie nur eine genaue Übereinstimmung wünschen, 1 oder -1 bedeutet, dass Sie den nächstgelegenen Wert wünschen, wenn es keine genaue Übereinstimmung gibt.

In unserem obigen Rennbeispiel kenne ich den Fahrer, den ich finden muss, aber ich kenne seine Position in der Endwertung nicht. Da die Position relativ ist, muss ich sicherstellen, dass ich die gesamten Ergebnisse ab der ersten Position abdecke.

=MATCH(“Kilgore Forelle”, B2:B6, 0)

Dies wird einen Wert von 4 zurückgeben. Da wir von der Spitze gestartet sind, wissen wir, dass Trout in diesem speziellen Rennen den vierten Platz belegte.

INDEX-MATCH

Vielleicht sehen Sie, wo wir jetzt unterwegs sind, oder vielleicht haben Sie vorgesprungen, um zu den guten Dingen zu gelangen. Mit INDEX + MATCH können wir diese beiden einfachen Funktionen kombinieren, um etwas Ähnliches, aber flexibleres als unser bewährtes SVERWEIS zu erstellen. Machen Sie sich keine Sorgen, wenn Sie dies ein paar Mal durchlesen müssen, es kann anfangs etwas schwierig sein. Wenn Sie erst einmal verstanden haben, was vor sich geht, ist es wirklich einfach, es aus den Komponenten wieder aufzubauen.

INDEX gibt einen bestimmten Wert zurück. Dies ist die primäre Funktion von SVERWEIS, wenn eine Reihe von Parametern gegeben ist; Sie können schnell einen benötigten Wert finden. Jetzt fehlt uns nur noch eine Möglichkeit, die Zeilenposition zu finden. Wenn Sie diese manuell finden müssen, verlieren Sie den Nutzen einer Funktion. Durch die Verwendung von MATCH im zweiten Argument können wir seine Ausgabe, eine relative Position, nehmen und diese in die Indexfunktion schieben, um dieses Problem zu lösen. Jetzt haben wir etwas, das genau das Gleiche wie SVERWEIS macht, nur mit etwas mehr Tipparbeit.

Die Funktion in die Praxis umsetzen

Anhand der folgenden Tabelle möchten wir herausfinden, welcher Produkttyp mit einer bestimmten Produkt-ID verknüpft ist.

Um INDEX MATCH zu verwenden, beginnen wir mit INDEX, da wir letztendlich eher einen bestimmten Wert als eine Position zurückgeben möchten. Innerhalb des INDEX schließen wir MATCH ein, um eine Position für unsere INDEX-Funktion zurückzugeben.

=INDEX(A2:A6,VERGLEICH(8316,B2:B6,0))

Was hier passiert, ist, dass ich Spalte A nach dem Wert der Zeile in Spalte B durchsuche, der von der Match-Funktion bereitgestellt wird.

Innerhalb der Match-Funktion gebe ich an, dass ich die Produkt-ID-Codes in B2:B6 durchsuchen und die Position des Werts 8316 zurücksenden muss. In diesem Fall befindet sie sich in Position 4, was dem Schreiben entspricht

=INDEX(A2:A6, 4)

Excel führt die Funktion aus und findet die Position im Array der angegebenen ID-Nummer. Es nimmt diese Position ein und sucht nach derselben Zeile in unserer A-Spalte und gibt den LKW-Typ für die spezifische ID zurück. Am Ende erhalten wir also die gleichen Ergebnisse mit etwas mehr Flexibilität bei den Eingaben.

Die Vorteile der Funktion

Die Stärke von INDEX + MATCH kommt von der kleineren Datenauswahl. Wenn Sie 15 Spalten haben, aber nur die Zahlen 1 und 15 verwenden, warum ziehen Sie dann alles andere ein? Dies kann dazu beitragen, die Laufzeit für große Datensätze zu beschleunigen.

Die zweite erweiterte Funktionalität ist, dass Index links oder rechts lesen kann, egal wo Sie die Spalten platzieren, INDEX ist im Gegensatz zu SVERWEIS gleichgültig, ob er sich links oder rechts von Ihrem ursprünglichen Array befindet. Wenn Sie sich das obige Beispiel noch einmal ansehen, haben wir genau das getan, von rechts nach links gearbeitet. Die Funktion kann auch als Qualitätssicherungsprüfung dienen, da Sie die beiden benötigten Spalten manuell angeben müssen, anstatt irgendwo innerhalb Ihrer Auswahl eine relative Spaltennummer einzugeben.

Brauchen Sie mehr Bildmaterial? Sehen Sie sich eine Bildschirmaufnahme eines Index-Match-Beispiels an, bei dem wir endgültige URLs in eine Anzeigentextvorlage einfügen.

Abschluss

Machen Sie weiter und spielen Sie mit INDEX + MATCH herum. Es ist eine sehr leistungsstarke Funktion, die Ihre Suchfähigkeiten verbessern kann. Sobald es klickt, bin ich sicher, dass Sie verkauft werden und nie mehr zurückschauen. Es gibt immer eine gewisse Lernkurve, wenn Sie etwas Neues ausprobieren, verpflichten Sie sich, Index Match für eine Weile zu verwenden. Es wird anfangs klobig sein, aber Sie werden die Vorteile in Kürze sehen.

———-
Beitrag aktualisiert von Jenna Kelly (vorheriger Beitrag: 03.02.14)