Generische Formel
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Zusammenfassung
Bei großen Datenmengen kann die exakte Übereinstimmung von VLOOKUP schmerzhaft langsam sein, aber Sie können VLOOKUP schnell blitzen lassen, indem Sie zwei VLOOKUPS verwenden, wie unten erläutert.
Anmerkungen:
- Wenn Sie einen kleineren Datensatz haben, ist dieser Ansatz übertrieben. Verwenden Sie es nur mit großen Datenmengen, wenn die Geschwindigkeit wirklich zählt.
- Sie müssen die Daten nach dem Suchwert sortieren, damit dieser Trick funktioniert.
- In diesem Beispiel werden benannte Bereiche verwendet. Wenn Sie keine benannten Bereiche verwenden möchten, verwenden Sie stattdessen absolute Referenzen.
Genau passende VLOOKUP ist langsam
Wenn Sie VLOOKUP im "exakten Übereinstimmungsmodus" für einen großen Datensatz verwenden, kann dies die Berechnungszeit in einem Arbeitsblatt erheblich verlangsamen. Bei beispielsweise 50.000 Datensätzen oder 100.000 Datensätzen kann die Berechnung Minuten dauern.
Die genaue Übereinstimmung wird durch Angabe von FALSE oder Null als viertes Argument festgelegt:
=VLOOKUP(val,data,col,FALSE)
Der Grund, warum VLOOKUP in diesem Modus langsam ist, liegt darin, dass jeder einzelne Datensatz im Datensatz überprüft werden muss, bis eine Übereinstimmung gefunden wird. Dies wird manchmal als lineare Suche bezeichnet.
VLOOKUP mit ungefährer Übereinstimmung ist sehr schnell
Im Approximationsmodus ist VLOOKUP extrem schnell. Um VLOOKUP mit ungefährer Übereinstimmung zu verwenden, müssen Sie Ihre Daten nach der ersten Spalte (der Nachschlagespalte) sortieren und dann für das 4. Argument TRUE angeben:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP ist standardmäßig true, was eine beängstigende Standardeinstellung ist, aber das ist eine andere Geschichte).
Bei sehr großen Datenmengen kann der Wechsel zu VLOOKUP mit ungefährer Übereinstimmung eine dramatische Geschwindigkeitssteigerung bedeuten.
Also, ein Kinderspiel, richtig? Sortieren Sie einfach die Daten, verwenden Sie die ungefähre Übereinstimmung, und fertig.
Nicht so schnell (heh).
Das Problem mit VLOOKUP im Modus "ungefähre Übereinstimmung" ist folgendes: VLOOKUP zeigt keinen Fehler an, wenn der Suchwert nicht vorhanden ist. Schlimmer noch, das Ergebnis kann völlig normal aussehen, obwohl es völlig falsch ist (siehe Beispiele). Nicht etwas, das Sie Ihrem Chef erklären möchten.
Die Lösung besteht darin, VLOOKUP zweimal zu verwenden, beide Male im ungefähren Übereinstimmungsmodus:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Erläuterung
Die erste Instanz von VLOOKUP sucht einfach nach dem Suchwert (die ID in diesem Beispiel):
=IF(VLOOKUP(id,data,1,TRUE)=id
und gibt TRUE nur zurück, wenn der Suchwert gefunden wurde. In diesem Fall führt
die Formel VLOOKUP erneut im ungefähren Übereinstimmungsmodus aus, um einen Wert aus dieser Tabelle abzurufen:
VLOOKUP(id,data,col,TRUE)
Es besteht keine Gefahr eines fehlenden Suchwerts, da der erste Teil der Formel bereits überprüft wurde, um sicherzustellen, dass er vorhanden ist.
Wenn der Suchwert nicht gefunden wird, wird der Teil "Wert wenn FALSCH" der IF-Funktion ausgeführt, und Sie können einen beliebigen Wert zurückgeben. In diesem Beispiel verwenden wir NA (). Wir geben einen # N / A-Fehler zurück. Sie können jedoch auch eine Nachricht wie "Fehlend" oder "Nicht gefunden" zurückgeben.
Denken Sie daran: Sie müssen die Daten nach dem Suchwert sortieren, damit dieser Trick funktioniert.
