Warum jeder Excel-Nutzer VLOOKUP noch heute durch XLOOKUP ersetzen sollte

Formeln in Tabellenkalkulationen wirkten früher instabil. Schon eine falsche Spaltennummer konnte dazu führen, dass ein ganzer Bericht fehlerhaft wurde. Doch als ich schließlich VLOOKUP durch XLOOKUP ersetzte, wurde Excel vorhersehbar, flexibel - und überraschenderweise äußerst robust gegen Fehler.

Die Syntax zerlegen - die schließlich dafür sorgte, dass die Excel-Logik klar wurde

Die Anatomie einer modernen Suchformel

Bevor ich erkläre, warum ich meinen alten Arbeitsablauf aufgegeben habe, ist es hilfreich zu verstehen, wie diese Funktionen mit Excel kommunizieren. VLOOKUP wurde zur bevorzugten Suchformel - schließlich werden Daten traditionell vertikal in Spalten und nicht horizontal in Zeilen angeordnet.

Es erfordert vier strenge Argumente. Sie müssen ihm einen Suchwert übergeben, ihn auf einen gesamten Datenblock verweisen lassen, angeben, auf welche Spaltennummer er zugreifen soll, und anschließend explizit „FALSE“ eingeben, damit er kein zufälliges, ähnliches Ergebnis zurückgibt:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

XLOOKUP ersetzt diese unflexible Vorgehensweise durch eine deutlich vielseitigere Lösung. Anstatt einen großen Tabellenbereich anzugeben und die Spalten zu zählen, gibt man einfach den zu suchenden Wert, die Spalte, in der gesucht werden soll, sowie die gewünschten Ergebnisdaten an.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

XLOOKUP wird noch leichter lesbar, wenn Sie Ihr Datenset in eine Excel-Tabelle umwandeln (indem Sie Strg+T drücken oder auf Einfügen > Tabelle klicken), da dadurch positionsbasierte Verweise in benannte Beziehungen umgewandelt werden. In allen nachfolgenden Beispielen verwende ich eine Tabelle namens StaffDirectory, die fünf Spalten enthält: ID, Name, Abteilung, Funktion und E-Mail.

VLOOKUP zwang mich, Spalten zu zählen - wie ein menschlicher Rechner.

Wenn die Spaltenreihenfolge alles durcheinanderbringt

Was mich bei VLOOKUP immer frustriert hat, war das manuelle Zählen der Spalten. Wenn man beispielsweise die E-Mail-Adresse von David Cho anhand seines Namens in Zelle A2 ermitteln will, liefert eine Tabellensuche nicht das gewünschte Ergebnis - schließlich kann VLOOKUP nur in der ersten Spalte des angegebenen Bereichs suchen. Da es sich bei dieser Spalte um „ID“ und nicht um „Name“ handelt, kann VLOOKUP den gesuchten Wert nicht finden; daher gibt die Formel einen Fehler zurück.

=VLOOKUP($A$2, StaffDirectory, 5, FALSE)

Um VLOOKUP zum Funktionieren zu bringen, muss man den Formelbereich so verändern, dass er bei der zweiten Spalte beginnt - dadurch wird die Formel jedoch unübersichtlich:

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

Beachten Sie, was dort passiert ist? Da ich das Tabellenarray so änden musste, dass es bei der Spalte „Name“ beginnt, verschob sich die Spaltenindex-Nummer von 5 auf 4. Wenn Sie jedoch vergessen, dass Sie nicht die gesamte Tabelle referenziert haben, und die Zahl 5 beibehalten, erhalten Sie einen #N/A-Fehler. Und wenn Sie falsch zählen und die Zahl 3 eingeben, gibt die Formel seinen Beruf an - nicht seine E-Mail-Adresse.

Schlimmer noch: Selbst wenn Ihre Formel jetzt korrekt ist - falls jemand irgendwo innerhalb dieses bestimmten Teilmengens eine neue Spalte einfügt, wird Ihre fest kodierte Indexnummer sofort ungültig, wodurch Ihre Berichte fehlerhaft werden.

Doch wenn wir XLOOKUP verwenden, verschwinden rohe Zahlen, benutzerdefinierte Bereiche sowie das Zählen von Spalten. Die Formel liest sich wie ein gesprochener Satz:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Email])

Selbst wenn nun jemand neue Spalten hinzufügt oder die Anordnung ändert, bleibt die Formel stabil - weil sie auf benannte Spalten und nicht auf deren Positionen verweist.

Die native Fehlerbehandlung macht den Einsatz von IFERROR überflüssig.

Umgang mit fehlenden Daten ohne zusätzliche Schichten

Im Gegensatz zu VLOOKUP, das bei fehlendem Suchbegriff abbbricht und einen Fehlercode ausgibt, ermöglicht XLOOKUP die direkte Behandlung von Fehlern innerhalb der Formel.

Um diese unschönen #N/A-Fehler bei Verwendung von VLOOKUP zu verbergen, muss man die Formel in die Funktion IFERROR einbetten. Dadurch entsteht jedoch unnötiger „Lärm“ in den Zellen, und komplexe Tabellen werden schwieriger zu debuggen:

=IFERROR(VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE),"Employee not found")

XLOOKUP verzichtet ganz auf diese zusätzliche Verpackung, indem es sein viertes Argument nutzt, um eine lesbare Nachricht direkt in die Zelle einzufügen, falls ein Name nicht in der Liste steht:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found")

Ja, die Formeln könnten in der Länge ähnlich sein - aber nehmen Sie sich einen Moment Zeit, um zu erkennen, wie viel einfacher es ist, die XLOOKUP-Version zu erstellen und zu verstehen.

Exakte Übereinstimmung ist jetzt der Standard - anstatt eine versteckte Falle zu sein.

Weglassen des FALSE-Arguments bei VLOOKUP

Die Funktion VLOOKUP funktioniert mit einer gefährlichen Standardeinstellung: Wenn man das letzte Argument weglässt, geht das System davon aus, dass eine näherungsweise Übereinstimmung gewünscht ist. Dieses Verhalten führt zu zwei großen Problemen im alltäglichen Umgang mit Tabellenkalkulationen.

Erstens könnte es zu einem falsch-positiven Ergebnis kommen. Angenommen, Sie vergessen jenes abschließende Argument:

=VLOOKUP($A$2, StaffDirectory[[ID]:[Name]], 2)

Auch wenn „1065“ nicht auf der Liste steht, gibt VLOOKUP keinen Fehler aus. Stattdessen findet die Funktion den größten Wert, der kleiner oder gleich dem Suchwert ist - basierend auf der Sortierreihenfolge. In diesem Fall wird die ID „1064“ von Priya Patel ausgewählt und ihr Name zurückgegeben. Am Ende erhalten Sie falsche Daten in Ihrer Tabelle - ohne dass eine Warnung angezeigt wird, dass etwas schiefgelaufen ist.

Zweitens könnten Sie vergessen, Ihre Daten zu sortieren. Der Algorithmus für die näherungsweise Suche bei VLOOKUP verlangt zwingend, dass die Suchspalte in aufsteigender Reihenfolge sortiert ist (vom kleinsten zum größten Wert). Falls Ihre Tabelle jedoch absteigend sortiert ist - oder völlig ungeordnet - führt das Weglassen dieses Arguments dazu, dass der Algorithmus unberechenbare, chaotische Ergebnisse liefert. Sind die Daten nicht korrekt sortiert, kann VLOOKUP das falsche Ergebnis zurückgeben - oder manchmal sogar mit einem #N/A-Fehler scheitern.

XLOOKUP vermeidet jedoch beide dieser Fallstricke, indem es die exakte Übereinstimmung (das erste der beiden optionalen Verhalten, die ich zuvor erwähnt habe) zum Standardverhalten macht. Wenn Sie das Argument für das Abgleichsverfahren leer lassen, sucht die Funktion dennoch nach einer exakten Übereinstimmung - ungeachtet der Sortierung Ihrer Daten.

=XLOOKUP($A$2, StaffDirectory[ID], StaffDirectory[Name], "ID not found")

Da eine exakte Übereinstimmung der Standardfall ist, liefert die Suche nach „1065“ hier zuverlässig Ihre eigene „ID nicht gefunden“-Nachricht - wodurch Ihre Tabelle sowohl vor stillschweigenden Fehler-Ergebnissen als auch vor Sortierproblemen geschützt wird.

Das Ändern der Suchrichtung sollte keine komplexen Umwege erfordern.

Scannen von unten nach oben ohne Sortieren der Daten

Je größer die Datensätze werden, desto häufiger muss man in einem fortlaufenden Log die neuesten Informationen statt der ältesten finden. Dies ist das zweite der beiden nützlichen, optionalen Verhaltensweisen, von denen ich Ihnen zuvor erzählt habe: die umgekehrte Suchrichtung.

Schauen Sie sich unser Beispieldatenset an: Der Name „Marcus Vance“ erscheint zweimal, weil er von „Marketing“ (Datensatzzeile 1) in die „Vertriebsabteilung“ (Datensatzzeile 5) gewechselt ist. Mit anderen Worten: Die Datensatzzeile 5 enthält seine aktuellsten Informationen.

Allerdings gibt eine standardmäßige VLOOKUP-Funktion - sofern man die Daten nicht sortiert oder keine aufwendigen Array-Berechnungen durchführt - den ersten gefundenen Treffer zurück (in diesem Fall die erste Dat zeile), wobei der neuere Eintrag übersprungen wird.

=VLOOKUP($A$2, StaffDirectory[[Name]:[Department]], 2, FALSE)

Bei XLOOKUP bedeutet die Änderung der Suchrichtung lediglich, dass man -1 als sechstes Argument eingibt:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found", 0, -1)

Dies führt eine Bottom-Up-Scans durch, sodass die aktuelle Abteilung zurückgegeben wird.

Das Zurückgeben eines gesamten Arrays schützt Formeln davor, später zu zerbrechen.

Mehrere Zeilen und Spalten mit einer einzigen Formel auswählen

Wenn Sie mit VLOOKUP die Abteilung, die Rolle und die E-Mail-Adresse eines Mitarbeiters in eine Zusammenfassungszeile übernehmen möchten, könnten Sie drei separate Suchformeln schreiben und die Indexnummern in den drei Zellen manuell ändern:

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE)

und

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 3, FALSE)

und

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

Dynamische Arrays vereinfachen dies erheblich durch das sogenannte „Spilling“. Wenn man einen Block benachbarter Spalten für das Ergebnisarray auswählt, füllt eine einzige XLOOKUP-Formel automatisch mehrere Zellen aus:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[[Department]:[Email]])

Damit haben Sie deutlich weniger Formeln zu verwalten, was die Handhabung großer Tabellen erleichtert.

Eine smartere Art, in Excel zu suchen

Der Einsatz von XLOOKUP anstelle von VLOOKUP hat meine Vorgehensweise beim Erstellen von Tabellenkalkulationen komplett verändert. Statt mir Sorgen über fehlerhafte Spaltenindizes, unbeabsichtigte Näherungswerte oder starre Tabellenstrukturen machen zu müssen, kann ich nun weniger Zeit mit der Korrektur von Formeln verbringen und mehr Zeit damit, mit den Daten zu arbeiten. Natürlich ist diese Funktion nicht die einzige moderne Variante eines bewährten Werkzeugs. Falls Sie bisher die Kombination aus INDEX und MATCH für komplexe Tabellen verwendet haben, ermöglicht Ihnen der Wechsel zu XMATCH anstelle der herkömmlichen MATCH-Funktion, genau dieselben zuverlässigen und exakten Abgleichsregeln auch bei Zeilen- und Spaltenabfragen zu nutzen.