Wenn Sie Excel-Tipps schon länger gelesen haben, haben Sie immer jemanden gefunden, der über die Verwendung der Funktionen Excel INDEX () & MATCH () anstelle von Excel VLOOKUP spricht. Für mich selbst war es immer zu schwierig, ZWEI neue Funktionen gleichzeitig zu beherrschen. Aber es ist ein cooler Trick. Geben Sie mir fünf Minuten und ich werde versuchen, es in einfachem Englisch zu erklären.
Die 30 Sekunden Überprüfung von VLOOKUP
Angenommen, Sie haben eine Tabelle mit Mitarbeiterdatensätzen. Die erste Spalte ist eine Mitarbeiternummer, und die verbleibenden Spalten enthalten verschiedene Daten zum Mitarbeiter. Jedes Mal, wenn Sie eine Mitarbeiternummer im Arbeitsblatt haben, können Sie mit VLOOKUP ein bestimmtes Datum über den Mitarbeiter zurückgeben. Die Syntax lautet VLOOKUP (Wert, Datenbereich, Spaltennummer, FALSE). Zu Excel heißt es: "Gehen Sie zum Datenbereich. Suchen Sie eine Zeile mit (Wert) in der ersten Spalte des Datenbereichs. Geben Sie den (Spalten-Nr.) Wert aus dieser Zeile zurück. Sobald Sie den Dreh raus haben, es ist sehr einfach und mächtig.
Das Problem
Eines Tages haben Sie eine Situation, in der Sie den Namen des Mitarbeiters haben, aber die Mitarbeiternummer benötigen. In der folgenden Abbildung haben Sie einen Namen in A10 und müssen die Mitarbeiternummer in B10 finden.
Wenn sich das Schlüsselfeld rechts neben den Daten befindet, die Sie abrufen möchten, funktioniert VLOOKUP nicht. Wenn nur der VLOOKUP -1 als Spaltennummer akzeptieren würde, gäbe es kein Problem. Aber das tut es nicht. Eine gängige Lösung besteht darin, vorübergehend eine neue Spalte A einzufügen, die Namensspalte in die neue Spalte A zu kopieren, mit VLOOKUP zu füllen, spezielle Werte einzufügen und dann die temporäre Spalte A zu löschen. Excel-Profis können diesen Schritt wahrscheinlich im Ruhezustand ausführen.
Ich werde vorschlagen, dass Sie sich der Herausforderung stellen und versuchen, diese Einzelschrittmethode anzuwenden. Ja, Sie müssen die Formel für ein paar Wochen an Ihre Wand heften, aber das haben Sie auch schon vor langer Zeit mit VLOOKUP gemacht, oder?
Ich denke, der Grund, warum dies so schwierig ist, ist, dass Sie zwei Funktionen verwenden, die Sie wahrscheinlich noch nie zuvor verwendet haben. Lassen Sie es mich in zwei Teile zerlegen.
Erstens gibt es die INDEX () -Funktion. Dies ist eine schrecklich benannte Funktion. Wenn jemand "Index" sagt, beschwört es in meinem Kopf nichts herauf, was dem ähnelt, was diese Funktion tut. Index erfordert drei Argumente.
=INDEX(data range, row number, column number)
In Englisch wechselt Excel zum Datenbereich und gibt den Wert im Schnittpunkt der (Zeilennummer) -ten Zeile und der (Spaltennummer) -ten Spalte zurück. Hey, denk darüber nach - das ist ziemlich einfach, oder? =INDEX($A$2:$C$6,4,2)gibt Ihnen den Wert in B5.
Wenn Sie INDEX () auf unser Problem anwenden, können Sie sich vorstellen, dass Sie Folgendes verwenden würden, um die Mitarbeiternummer aus dem Bereich zurückzugeben : =INDEX($A$2:$A$6,?,1). Eigentlich scheint dieses Stück so trivial, dass es nutzlos erscheint. Wenn Sie jedoch das Fragezeichen durch eine MATCH () - Funktion ersetzen, haben Sie die Lösung.
Hier ist die Syntax:
=MATCH(Value, Single-column data range, FALSE)
Es sagt Excel: "Durchsuchen Sie den Datenbereich und teilen Sie mir die relative Zeilennummer mit, für die Sie eine Übereinstimmung finden (Daten). Um herauszufinden, in welcher Zeile sich der Mitarbeiter in A10 befindet, würden Sie verwenden =MATCH(A10,$B$2:$B$6,FALSE). Ja, dies ist komplexer als Index , aber es sollte genau in der Gasse der VLOOKUP-Profis sein. Wenn A10 "Miller, Bob" enthält, gibt dieser MATCH zurück, dass er sich in der 3. Reihe des Bereichs B2: B6 befindet.
Da ist es - die MATCH () - Funktion teilt der Indexfunktion mit, in welche Zeile sie schauen soll - Sie sind fertig. Nehmen Sie die Indexfunktion, ersetzen Sie unser Fragezeichen durch die MATCH-Funktion, und Sie können jetzt das Äquivalent von VLOOKUPs ausführen, wenn sich das Schlüsselfeld nicht in der linken Spalte befindet. Hier ist die zu verwendende Funktion:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Die Haftnotiz an meiner Wand zeigt sie tatsächlich als zwei Zeilen. Zuerst habe ich die Erklärung für MATCH () geschrieben. Darunter habe ich die Erklärung für INDEX () geschrieben. Ich habe dann eine Trichterform zwischen den beiden gezeichnet, um anzuzeigen, dass die MATCH () - Funktion in das 2. Argument der INDEX () - Funktion übergeht.
Die ersten Male, als ich eines davon machen musste, war ich versucht, nur eine neue temporäre Spalte A hineinzuschlagen, aber ich hatte den Schmerz, es stattdessen so zu machen. Es ist schneller und erfordert weniger Manipulation. Wenn Sie also das nächste Mal wünschen, Sie könnten eine negative Zahl in die VLOOKUP-Funktion einfügen, versuchen Sie diese seltsame Kombination aus INDEX und MATCH, um Ihre Probleme zu lösen.
