Kurs
XLOOKUP() vs. VLOOKUP(): Ein Vergleich für Excel-Benutzer
Seit Jahrzehnten ist VLOOKUP()
die beliebteste Funktion in Excel, wenn es um die Suche nach Daten geht. Es ist ein zuverlässiges Werkzeug, hat aber seine Grenzen. Deshalb wurde 2019 mit XLOOKUP()
eine neue Funktion mit erweiterten Möglichkeiten eingeführt. In diesem Artikel vergleichen wir VLOOKUP()
und XLOOKUP()
, um ihre Eigenschaften und unterschiedlichen Einsatzmöglichkeiten besser zu verstehen.
Bedenke, dass die Umstellung von VLOOKUP()
auf XLOOKUP()
nur eine von vielen Excel-Aktualisierungen ist. Um die Produktivität zu maximieren, ist es wichtig, mit den sich weiterentwickelnden Funktionen von Excel auf dem Laufenden zu bleiben. Schau dir also den Lernpfad zu den Excel-Grundlagen an, um sicherzustellen, dass du die neuesten Funktionen nutzt.
Bringe deine Karriere mit Excel voran
Erwerbe die Fähigkeiten, um Excel optimal zu nutzen - keine Erfahrung erforderlich.
XLOOKUP() und VLOOKUP() Syntax
Nachdem du nun ein grundlegendes Verständnis für beide Funktionen hast, wollen wir ihre Syntax verstehen und wie du sie in realen Anwendungen einsetzen kannst.
Syntax von VLOOKUP()
Werfen wir einen Blick auf die VLOOKUP()
Syntax.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In der obigen Formel:
-
lookup_value
ist der Wert, nach dem du suchen willst. Dies kann eine Zahl, ein Text oder ein Verweis auf eine Zelle sein, die den Suchwert enthält. -
table_array
ist der Bereich der Zellen, der die Daten enthält. Die erste Spalte in diesem Bereich sollte diesearch_key
enthalten. -
col_index_num
ist die Spaltennummer in dem Bereich, aus dem du den Wert abrufen möchtest. -
range_lookup
ist die Stelle, an der du einen logischen Wert eingibst (TRUE
oderFALSE
).
Syntax von XLOOKUP()
Werfen wir nun einen Blick auf die xlookup()
Syntax. Wie wir sehen können, hat XLOOKUP()
mehr Argumente als VLOOKUP()
.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
In der obigen Formel:
-
lookup_value
ist der Wert, nach dem du suchen willst. -
lookup_array
ist der Bereich der Zellen, in dem der Nachschlagewert gesucht wird. -
return_array
ist der Bereich von Zellen, der den Wert enthält, den du zurückgeben möchtest. -
if_not_found
(optional) gibt einen Wert zurück, wenn keine Übereinstimmung gefunden wird. Wenn der Wert nicht angegeben wird, wird#N/A
zurückgegeben. -
match_mode
(optional) legt die Art der Übereinstimmung fest.0
steht für die exakte Übereinstimmung (Standard).-1
steht für die exakte Übereinstimmung oder das nächstkleinere Element.1
steht für die exakte Übereinstimmung oder das nächstgrößere Element.2
steht für die Übereinstimmung mit Platzhalterzeichen. -
search_mode
(optional) legt den zu verwendenden Suchmodus fest.1
wird verwendet, um vom Ersten zum Letzten zu suchen (Standard).-1
wird verwendet, um vom Letzten zum Ersten zu suchen.2
wird verwendet, um die binäre Suche in aufsteigender Reihenfolge durchzuführen.-2
wird verwendet, um die binäre Suche in absteigender Reihenfolge durchzuführen.
Hauptunterschiede: XLOOKUP() vs. VLOOKUP()
Sehen wir uns nun einige wichtige Unterschiede zwischen den Funktionen XLOOKUP()
und VLOOKUP()
an.
Exakter Übereinstimmungsmodus
XLOOKUP()
ist standardmäßig auf exakte Übereinstimmung eingestellt, aber VLOOKUP()
erfordert die Angabe von FALSE
für eine exakte Übereinstimmung.
Genaue Übereinstimmung zwischen XLOOKUP() und VLOOKUP(). Quelle: Bild vom Autor.
In VLOOKUP()
habe ich den Wert range_lookup
(4. Argument) nicht angegeben, so dass er die nächstliegende Übereinstimmung liefert, was nicht das gewünschte Ergebnis ist. XLOOKUP()
Die Suche mit der Suchfunktion von hingegen liefert standardmäßig die exakte Übereinstimmung.
Nachschlagrichtung
VLOOKUP()
ist darauf beschränkt, nur rechts von der ersten Spalte der ausgewählten Tabelle zu suchen. Aber XLOOKUP()
kann in jeder Richtung nach Werten suchen.
Unterschied in der Suchrichtung zwischen VLOOKUP() und XLOOKUP(). Quelle: Bild vom Autor.
Hier hat VLOOKUP()
keine Noten gefunden, weil es nur nach Daten rechts von lookup_value
(Schülernamen) suchen kann. Im Vergleich dazu findet XLOOKUP()
die Noten für Robin, weil es in beide Richtungen (links oder rechts) suchen kann.
Array-Referenzen
In VLOOKUP()
musst du den gesamten Datenbereich (Tabellen-Array) definieren und die Spaltennummer (Spaltenindex), die die gewünschte Ausgabe (Rückgabespalte) enthält, in einer einzigen Formel angeben. XLOOKUP()
ist flexibler als das. Damit kannst du separate Arrays für den Nachschlagewert und die Daten, die du zurückgeben willst, definieren.
Array-Referenzen zwischen VLOOKUP() und XLOOKUP(). Quelle: Bild vom Autor.
Horizontales Nachschlagen
VLOOKUP()
erlaubt es dir nicht, horizontale Suchen durchzuführen. Wenn du eine horizontale Suche möchtest, musst du stattdessen HLOOKUP()
verwenden. XLOOKUP()
fasst diese beiden Funktionen zusammen, weil es sowohl vertikale als auch horizontale Suchvorgänge durchführt, sodass du keine separaten Funktionen für verschiedene Suchrichtungen verwenden musst.
Horizontaler Lookup-Unterschied zwischen VLOOKUP() und XLOOKUP(). Quelle: Bild vom Autor.
Handhabung von Spalteneinfügungen/-löschungen
Die Änderungen in der Spalte betreffen VLOOKUP()
aufgrund der fest codierten column_index_num
. XLOOKUP()
ist jedoch nicht von den Änderungen in den Spalten betroffen. Es funktioniert weiterhin, ohne dass die Formel angepasst wird.
XLOOKUP() bleibt davon unberührt, während VLOOKUP() fehlschlägt. Quelle: Bild vom Autor.
Sortieren und Suchen
Du kannst VLOOKUP()
in aufsteigender Reihenfolge sortieren, aber das würde zu Schwierigkeiten beim Umgang mit unsortierten Daten führen. XLOOKUP()
kann jedoch das Argument search_mode
verwenden, um sowohl in aufsteigender als auch in absteigender Reihenfolge zu suchen.
Behandlung von Einfügen/Löschen in XLOOKUP() und VLOOKUP(). Quelle: Bild vom Autor.
Benutzerdefinierte Fehlermeldungen
VLOOKUP()
zeigt ein #N/A
Fehlerzeichen an, wenn keine Übereinstimmung gefunden wird. XLOOKUP()
hat einen optionalen if_not_found
Parameter, mit dem du den Ausgabetext anpassen kannst, wenn ein Wert nicht gefunden wird.
Anpassen von Fehlermeldungen in XLOOKUP() und VLOOKUP(). Quelle: Bild vom Autor.
Du kannst sehen, dass VLOOKUP()
den Schüler nicht in seiner Liste finden konnte und einen #N/A
Fehler anzeigt. Auch die Funktion XLOOKUP()
konnte den Namen des Schülers nicht ausfindig machen. Im Gegensatz zu VLOOKUP()
gibt XLOOKUP()
jedoch eine bestimmte Meldung zurück, wenn es keine Übereinstimmung gibt.
Mehrere Werte zurückgeben
VLOOKUP()
kann immer nur einen Wert zurückgeben. Aber XLOOKUP()
kann Werte aus mehreren Spalten gleichzeitig abrufen. Deshalb kannst du eine XLOOKUP()
Formel anstelle von mehreren VLOOKUP()
Formeln verwenden.
Rückgabe mehrerer Werte mit XLOOKUP() und VLOOKUP(). Quelle: Bild vom Autor.
Suchmodus
VLOOKUP()
durchsucht eine Liste von Anfang an und gibt nur den ersten Wert zurück, der mit dem Gewünschten übereinstimmt. Aber XLOOKUP()
kann eine Liste in beide Richtungen (von oben nach unten oder von unten nach oben) durchsuchen und Dinge in umfangreichen Listen schnell finden.
Unterschied zwischen VLOOKUP() und XLOOKUP(). Quelle: Bild vom Autor.
Hier ruft VLOOKUP()
das erste Vorkommen ab, das aus dem ersten Semester stammt. Mit XLOOKUP()
verwende ich jedoch den Suchmoduscode -1
, um von unten nach oben zu suchen.
Praktische Beispiele und Anwendungsfälle
Werfen wir nun einen Blick auf einige praktische Beispiele und Anwendungsfälle.
Nachschlagen, ohne eine Tabelle neu anzuordnen
Ich habe eine Liste mit den Namen und Noten der Schüler und möchte die Noten der einzelnen Schüler anhand ihres Namens finden. Zu diesem Zweck werde ich die Funktionen XLOOKUP()
und VLOOKUP()
verwenden, um dir die Unterschiede zu zeigen.
Wenn ich XLOOKUP()
verwende, gebe ich die folgende Formel ein:
=XLOOKUP(D8,B2:B5,A2:A5)
In dieser Formel:
-
D8
enthält den Nachschlagewert, der Charlie ist. -
B2:B5
Bereich enthält die Namen der Schüler. Die FunktionXLOOKUP()
sucht nach dem Wert in D8 innerhalb dieses Bereichs. -
A2:A5
ist der Bereich, der die Punkte enthält, die den Schülernamen entsprechen.
Wenn ich VLOOKUP()
verwende, gebe ich die folgende Formel ein:
=VLOOKUP(D4,A1:B5,1,0)
In dieser Formel:
-
D4
enthält den Nachschlagewert, der Charlie ist. -
A1:B5
Bereich sucht in allen Spalten(Noten, Schülername). -
1
gibt den Wert aus der 1. Spalte des Bereichs zurück (Werte). -
0
findet die genaue Übereinstimmung.
VLOOKUP() gibt einen #N/A-Fehler zurück, aber XLOOKUP() behandelt ihn. Quelle: Bild vom Autor.
Wie du sehen kannst, zeigt VLOOKUP()
einen #N/A
Fehler an, weil es nicht nach links suchen kann.
Erstellen einer benutzerdefinierten Ausgabe, wenn ein Wert nicht gefunden wird
Ich habe eine Liste der Schüler mit ihren IDs, Namen und Noten. Bei der Suche habe ich versehentlich eine Schüler-ID eingegeben, die nicht auf der Liste stand. In einem solchen Fall soll eine angepasste Meldung erscheinen, wenn die Schüler-ID nicht gefunden wird. Ich werde die Funktionen XLOOKUP()
und VLOOKUP()
verwenden, um dir die Unterschiede zu zeigen.
Wenn ich XLOOKUP()
verwende, gebe ich die folgende Formel ein:
(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found"))
In dieser Formel:
-
E9
enthält den Wert, nach dem wir suchen wollen. In meinem Fall ist es 14256. -
A2:A5
ist der Zellbereich, der den Nachschlagewert enthält (Schüler-ID). -
B2:B5
sucht nach dem Ergebnis der ID 14526. -
Student not Found
wird angezeigt, wenn die Punktzahl nicht in der Liste enthalten ist.
Wenn ich VLOOKUP()
verwende, gebe ich die folgende Formel ein:
(=VLOOKUP(E6, A2:C5,2,0))
In dieser Formel:
-
E6
bezieht sich auf die Schüler-ID, nach der ich suche. -
A2:C5
bezieht sich auf den Bereich, der alle Daten in der Tabelle enthält. -
2
gibt die Nummer der Spalte an, aus der die Daten geholt werden sollen. -
0
findet die genaue Übereinstimmung.
XLOOKUP() gibt eine Meldung zurück, aber VLOOKUP() nicht. Quelle: Bild vom Autor.
Wie du siehst, existiert 14256 nicht im Bereich A2:A5
, also liefert XLOOKUP()
die benutzerdefinierte Nachricht Student not found
. Und VLOOKUP()
hingegen wirft einen #N/A
Fehler aus.
Suche von unten nach oben
Ich möchte das Gehalt von Sarah aus dem letzten Jahr herausfinden. Zu diesem Zweck werde ich die Funktionen XLOOKUP()
und VLOOKUP()
verwenden, um dir die Unterschiede zu zeigen.
Wenn ich XLOOKUP()
verwende, gebe ich die folgende Formel ein:
=XLOOKUP(F8,B2:B10,C2:C10,,,-1)
In dieser Formel:
-
F8
enthält den Nachschlagewert Sarah. -
B2:B10
schaut in den Bereich und sucht nach Sarah. -
C2:C10
ruft die Daten ab. -
-1
sucht von unten in der Spalte.
Wenn ich VLOOKUP()
verwende, gebe ich die folgende Formel ein:
=VLOOKUP(F4, B2:C10,2,0)
In dieser Formel:
-
F4
enthält den Nachschlagewert Sarah. -
B2:C10
sucht nach dem Nachschlagewert innerhalb dieses Bereichs. -
2
gibt die Spaltennummer an Gehalt an, aus der die Daten geholt werden sollen. -
0
findet die genaue Übereinstimmung.
Unterschied zwischen VLOOKUP() und XLOOKUP() im Suchmodus. Quelle: Bild vom Autor.
Du kannst sehen, dass XLOOKUP()
das gewünschte Gehalt für das gewünschte Jahr findet, während VLOOKUP()
nur den ersten übereinstimmenden Wert anzeigt. Ich konnte das tun, weil XLOOKUP()
es mir ermöglicht, von unten nach oben zu suchen.
Leistung und Kompatibilität
Vergleichen wir nun die Leistung und Kompatibilität der beiden Funktionen.
Leistung in großen Datensätzen
VLOOKUP()
kann bei großen Datensätzen langsamer sein, vor allem wenn die Suchspalte nicht sortiert ist und das Argument für die Bereichssuche auf FALSE
für eine exakte Übereinstimmung gesetzt ist. Das liegt daran, dass VLOOKUP()
den Datensatz sequentiell durchsucht, bis es eine Übereinstimmung findet, was bei großen Tabellen zeitaufwändig sein kann. XLOOKUP()
bietet eine bessere Leistung bei großen Datensätzen. Sie kann exakte Treffer effizienter verarbeiten und horizontal und vertikal ohne Sortierung suchen.
Kompatibilität mit verschiedenen Versionen von Excel
Nachdem du die Vorteile von XLOOKUP()
gesehen hast, denkst du bestimmt darüber nach, VLOOKUP()
aufzugeben. Aber ich möchte klarstellen, dass XLOOKUP()
zwar eine viel schnellere und hervorragende Funktion ist, aber manchmal nicht verfügbar ist. Das liegt daran, dass XLOOKUP()
eine neuere Funktion ist, die nur in Excel 2021 und Microsoft 365 (ab 2019) funktioniert. Wenn du mit Personen zusammenarbeitest, die ältere Versionen von Excel verwenden, musst du VLOOKUP()
und andere Funktionen anstelle von XLOOKUP()
verwenden.
Zusammenfassende Tabelle
Erstellen wir eine zusammenfassende Tabelle, damit du sie leicht finden kannst.
Feature | VLOOKUP() | XLOOKUP() |
---|---|---|
Standard Spielmodus | Erfordert die Angabe von FALSE für eine genaue Übereinstimmung. |
Standardmäßig ist die exakte Übereinstimmung eingestellt. |
Nachschlagen Richtung | Sucht nur auf der rechten Seite der Nachschlagezeile. | Du kannst in jede Richtung suchen (links, rechts, oben, unten). |
Array-Referenzen | Erfordert die Definition des gesamten Datenbereichs und des Spaltenindex in einer einzigen Formel. | Erlaubt separate Arrays für den Nachschlagewert und die Rückgabewerte. |
Horizontal Lookup | Unterstützt keine horizontalen Lookups (HLOOKUP() erforderlich ist). |
Unterstützt sowohl vertikale als auch horizontale Lookups. |
Umgang mit Spaltenänderungen | Beeinträchtigt durch Einfügen/Löschen von Spalten aufgrund von fest kodierten Spaltenindizes. | Unbeeinflusst von den Änderungen in den Säulen, funktioniert weiterhin ohne Anpassungen. |
Sortieren und Suchen | Begrenzt auf die Sortierung in aufsteigender Reihenfolge. | Du kannst sowohl in aufsteigender als auch in absteigender Reihenfolge suchen, indem du die search_mode Argument. |
Benutzerdefinierte Fehlermeldungen | Zeigt #N/A Fehler, wenn keine Übereinstimmung gefunden wird. |
Ermöglicht die Anpassung der Ausgabemeldung, wenn keine Übereinstimmung gefunden wird. |
Mehrere Werte zurückgeben | Kann nur einen Wert auf einmal zurückgeben. | Kann Werte aus mehreren Spalten gleichzeitig abrufen. |
Suchmodus | Sucht von oben nach unten und gibt den ersten Treffer zurück. | Kann in beide Richtungen suchen (von oben nach unten oder von unten nach oben), um schnellere Ergebnisse in umfangreichen Listen zu erzielen. |
Leistung in großen Datensätzen | Kann langsamer sein, besonders bei unsortierten Daten oder wenn range_lookup ist FALSE . |
Bessere Leistung, effizienter Umgang mit großen Datensätzen und Suchen ohne Sortierung. |
Kompatibilität | Funktioniert mit allen Versionen von Excel. | Nur in Excel 2021 und Microsoft 365 (ab 2019) verfügbar. |
Schlussgedanken | Für ältere Excel-Versionen oder einfachere Nachschlageaufgaben immer noch nützlich. | Leistungsstärker und flexibler, besser geeignet für komplexe oder umfangreiche Abfragen. |
Schlussgedanken
XLOOKUP()
übertrifft VLOOKUP()
, vor allem bei großen Datensätzen oder komplexen Nachschlageanforderungen. Seine Flexibilität, einschließlich der bidirektionalen Suche, der Abfrage mehrerer Ergebnisse und der Anpassungsfähigkeit an Datenänderungen, macht es zu einem hilfreichen Werkzeug. Dennoch ist VLOOKUP()
nach wie vor eine gute Wahl, vor allem für Benutzer älterer Excel-Versionen oder für diejenigen, die eine einfachere Methode für grundlegende Suchvorgänge bevorzugen. Die beste Wahl hängt letztlich von deinen spezifischen Bedürfnissen und deiner Excel-Version ab.
Wenn du neu in Excel bist oder dir ein solides Fundament aufbauen möchtest, sind unser Kurs Einführung in Excel und der Lernpfad Excel-Grundlagen ein guter Ausgangspunkt, um die Grundlagen zu beherrschen.
Excel-Grundlagen lernen
Ich bin ein Inhaltsstratege, der es liebt, komplexe Themen zu vereinfachen. Ich habe Unternehmen wie Splunk, Hackernoon und Tiiny Host geholfen, ansprechende und informative Inhalte für ihr Publikum zu erstellen.
Häufig gestellte Fragen
Ist XLOOKUP() besser als VLOOKUP()?
Ja, XLOOKUP()
ist besser, weil es in jeder Spalte einer Tabelle suchen kann, mehrere Ergebnisse zurückgibt, Fehler besser behandelt und sowohl vertikale als auch horizontale Suchvorgänge durchführt.
Was sind die Einschränkungen von VLOOKUP() im Vergleich zu XLOOKUP()?
VLOOKUP()
ist auf die Suche in der ersten Spalte eines bestimmten Bereichs beschränkt. Und sie kann nur ein Ergebnis pro Abfrage liefern.
Kann XLOOKUP() Array-Operationen wie SUMIFS() oder COUNTIFS() verarbeiten?
Ja, XLOOKUP()
kann mit Array-Funktionen wie SUMIFS()
und COUNTIFS()
verwendet werden, um komplexere Berechnungen und Datenanalysen durchzuführen.
Lerne Excel mit DataCamp
Kurs
Power Pivot in Excel
Kurs