XLOOKUP in Excel: Syntax, Beispiele und warum es VLOOKUP schlägt
Von Mirko Peters · · Aktualisiert
XLOOKUP in Excel erklärt: die Syntax, jedes Argument, Beispiele für exakte, Platzhalter- und letzte Treffer, mehrere Spalten und der Unterschied zu VLOOKUP.
Automatisch mit KI aus dem Originalbeitrag übersetzt. Englisches Original lesen
XLOOKUP in Excel sucht einen Wert in einem Bereich und gibt das passende Element aus einem anderen Bereich zurück: =XLOOKUP(lookup_value, lookup_array, return_array). Geben Sie eine Mitarbeiter-ID ein, zeigen Sie auf die ID-Spalte und danach auf die Namensspalte, und Sie erhalten den Namen. Dieser Artikel richtet sich an Excel-Anwender, die aus Gewohnheit noch VLOOKUP schreiben oder gerade XLOOKUP in der Datei eines Kollegen gesehen haben. Sie erhalten die Syntax, die sechs Argumente, Beispiele für typische Aufgaben im Arbeitsalltag, den Vergleich mit VLOOKUP und eine Liste der Fehler, auf die Sie wahrscheinlich stoßen.
Das Wichtigste in Kürze
- XLOOKUP benötigt einen Suchwert, einen Suchbereich und einen Rückgabebereich: =XLOOKUP(lookup_value, lookup_array, return_array). Die Funktion findet den ersten Treffer und gibt das Element aus dem Rückgabebereich zurück.
- Sie kann links von der Suchspalte suchen und arbeitet standardmäßig mit einer exakten Übereinstimmung. Beides kann VLOOKUP nicht bzw. tut es standardmäßig nicht.
- Das vierte Argument, if_not_found, ersetzt den Fehler #NV durch einen Text Ihrer Wahl. Ohne dieses Argument gibt eine erfolglose Suche #NV zurück.
- match_mode 2 aktiviert Platzhalter, -1 und 1 finden das nächstkleinere bzw. nächstgrößere Element, und search_mode -1 durchsucht vom letzten bis zum ersten Element.
- Die Microsoft-Seite nennt XLOOKUP für Microsoft 365, 2021 und 2024. Die Hinweise zu Excel 2016 und 2019 widersprechen sich jedoch. Testen Sie daher geteilte Dateien, bevor Sie sich darauf verlassen.
Die Syntax und die Optionen unten folgen der Microsoft-Seite zur XLOOKUP-Funktion. Wo ein Muster nicht auf dieser Seite steht, weist der Text darauf hin. Es handelt sich um eine Funktion für Excel, und sie ist diejenige, die Microsoft selbst als die bessere Wahl vorschlägt, wenn Sie die Hilfeseite zu VLOOKUP öffnen.
Die Syntax und die sechs Argumente
Die vollständige Formel sieht so aus:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Die ersten drei Argumente sind erforderlich. Die letzten drei sind optional.
lookup_valueist der Wert, nach dem Sie suchen, zum Beispiel eine ID in Zelle D2. Lassen Sie ihn weg, gibt XLOOKUP die leeren Zellen zurück, die es im Suchbereich findet.lookup_arrayist der Bereich oder das Array, das durchsucht wird, zum Beispiel A2:A100.return_arrayist der Bereich oder das Array, aus dem zurückgegeben wird, zum Beispiel B2:B100.if_not_foundist der Text, der angezeigt wird, wenn kein gültiger Treffer vorliegt. Lassen Sie ihn weg, erhalten Sie #NV.match_modelegt fest, wie die Übereinstimmung erfolgt.0steht für eine exakte Übereinstimmung und ist der Standard.-1bedeutet eine exakte Übereinstimmung oder, falls keine vorhanden ist, das nächstkleinere Element.1bedeutet eine exakte Übereinstimmung oder das nächstgrößere Element.2ist eine Platzhalterübereinstimmung, bei der*,?und~eine besondere Bedeutung haben.search_modelegt die Suchrichtung fest.1durchsucht vom ersten bis zum letzten Element und ist der Standard.-1sucht vom letzten zum ersten.2und-2sind binäre Suchen auf Daten, die aufsteigend bzw. absteigend sortiert sind. Microsoft warnt, dass unsortierte Daten bei den binären Modi ungültige Ergebnisse liefern.
Halten Sie das Sucharray und das Rückgabearray gleich groß. Wenn das Sucharray die Zeilen 2 bis 100 umfasst, sollte auch das Rückgabearray die Zeilen 2 bis 100 umfassen. Die Seite von Microsoft formuliert das nicht ausdrücklich als Regel, aber nicht zueinander passende Bereiche sind der häufigste Grund dafür, dass eine Suche die falsche Zeile oder einen Fehler zurückgibt.
Einfache Suchen und das Argument für nicht gefundene Werte
Angenommen, die Mitarbeiter-IDs stehen in A2:A100 und die Namen in B2:B100. Sie geben die gesuchte ID in D2 ein.
- Klicken Sie auf die Zelle, in der das Ergebnis erscheinen soll, zum Beispiel E2.
- Geben Sie
=XLOOKUP(D2,A2:A100,B2:B100)ein. - Drücken Sie Eingabe.
Sie erhalten den Namen, der zur ID gehört. XLOOKUP gibt den Eintrag für die erste gefundene Übereinstimmung zurück. Ist die ID nicht vorhanden, erscheint #NV.
Um eine freundlichere Anzeige zu erhalten, ergänzen Sie das vierte Argument:
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
Jetzt zeigt eine nicht aufgelistete ID „No record found“ an. Verwenden Sie das in Tabellen, die andere lesen. Ein Hinweis dazu: Eine solche Meldung kann ein echtes Problem verdecken. Wenn in jeder Zeile „No record found“ steht, liegt das eher an den Bereichen als an den Daten. Testen Sie daher zuerst eine bekannte ID.
Nach links suchen
VLOOKUP kann keinen Wert aus einer Spalte links von der Suchspalte zurückgeben. XLOOKUP kann das, weil Such- und Rückgabebereich getrennt sind. Wenn der Name in Spalte C steht und die gewünschte ID in Spalte A:
=XLOOKUP(F2,C2:C100,A2:A100)
Die Seite von Microsoft sagt es direkt: Die Suchspalte kann auf beiden Seiten der Rückgabespalte liegen.
Übereinstimmungsmodi: Platzhalter und nächstgelegene Werte
Suche mit Platzhaltern
Setzen Sie match_mode auf 2, um Platzhalter zu verwenden. Das Sternchen steht für beliebig viele Zeichen, das Fragezeichen für genau ein Zeichen. Mit einer Tilde suchen Sie nach einem wörtlichen Sternchen oder Fragezeichen.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
Diese Formel gibt den ersten Eintrag zurück, der mit „Jan“ beginnt. Lassen Sie die 2 weg, sucht Excel nach dem wörtlichen Text „Jan*“ und gibt Ihre Meldung für nicht gefundene Werte zurück. Das ist der häufigste Fehler bei Platzhaltern. Formulieren Sie Platzhalter möglichst genau, denn XLOOKUP liefert nur die erste Übereinstimmung, und ein zu weit gefasstes Muster kann etwas treffen, das Sie nicht beabsichtigt haben.
Nächstkleinerer oder nächstgrößerer Eintrag
Die Modi -1 und 1 eignen sich für Skalen wie Steuersätze, Provisionsstufen oder Preisstaffeln. Legen Sie die Schwellenwerte in A2:A6 und die Sätze in B2:B6 ab und suchen Sie in E2 nach einem Einkommenswert:
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
Mit -1 gibt Excel die exakte Übereinstimmung zurück, andernfalls den nächstkleineren Schwellenwert. Die leere Stelle vor der -1 überspringt das Argument für nicht gefundene Werte. Das Beispiel von Microsoft für die Näherungssuche verwendet 1, um einen Steuersatz für den nächstgrößeren Einkommenswert zurückzugeben. Wählen Sie also die Richtung, die zu Ihrer Tabelle passt. Anders als bei der alten Näherungssuche mit VLOOKUP müssen Sie die Schwellenwerte für diese Modi nicht sortieren. Binäre Suchmodi benötigen dagegen sortierte Daten.
Von unten suchen: die letzte Übereinstimmung
Kommt ein Wert mehrmals vor, gibt XLOOKUP die erste gefundene Zeile zurück. Um die letzte zu erhalten, suchen Sie vom Ende her, indem Sie search_mode auf -1 setzen:
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
Sind Bestellungen in chronologischer Reihenfolge aufgelistet, liefert diese Formel den neuesten Eintrag für einen Kunden. Die 0 sorgt für eine exakte Übereinstimmung. Dieses Muster eignet sich für Fragen wie „neuester Preis“, „letzter Kontakt“ und ähnliche, die mit VLOOKUP umständlich waren.
Mehrere Spalten gleichzeitig zurückgeben
Die Seite von Microsoft weist darauf hin, dass XLOOKUP im Gegensatz zu VLOOKUP mehrere Spalten als ein Array zurückgeben kann. Machen Sie dazu den Rückgabebereich breiter:
=XLOOKUP(D2,A2:A100,B2:D100)
Die Formel schreibt den Treffer aus Spalte B in die Zelle und verteilt die Werte aus den Spalten C und D in die Zellen rechts davon. Lassen Sie dafür Platz. Sind die Zellen rechts bereits gefüllt, zeigt Excel einen Überlauffehler an, statt sie zu überschreiben. Das Beispiel von Microsoft liefert mit einer einzigen Formel einen Mitarbeiternamen und eine Abteilung zurück.
Zweidimensionale Suchen
Microsoft zeigt, wie sich zwei XLOOKUP-Funktionen verschachteln lassen, um einen Wert am Schnittpunkt von Zeile und Spalte zu finden. Das Muster lautet laut der Seite:
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
Die innere Funktion wählt die Spalte aus, die der Überschrift in C3 entspricht. Die äußere Funktion sucht dann D2 in den Zeilenbeschriftungen und gibt den Wert aus dieser Spalte zurück. Microsoft vergleicht dies mit einer Kombination aus INDEX und VERGLEICH. Wenn Sie einen Bericht mit Produkten in den Zeilen und Monaten in den Spalten haben, ersetzt diese Formel eine deutlich kompliziertere Konstruktion.
Mehrere Kriterien
XLOOKUP nimmt genau einen Suchwert entgegen, und die Seite von Microsoft enthält kein Beispiel für mehrere Kriterien. Ein gängiges Muster lässt Excel zwei Bedingungen gleichzeitig prüfen. Angenommen, die Kategorie steht in Spalte A, der Status in Spalte B und der Preis in Spalte C. Um den Preis für die Kategorie in F2 und den Status in G2 zu finden:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Nicht gefunden")
Jeder Vergleich liefert WAHR oder FALSCH. Die Multiplikation ergibt nur dort 1, wo beide Bedingungen zutreffen, und XLOOKUP sucht nach der ersten 1. Das Muster funktioniert in aktuellen Versionen von Microsoft 365, steht aber nicht auf der Funktionsseite von Microsoft. Testen Sie es deshalb an einer kleinen Stichprobe. Eine Hilfsspalte, die beide Felder verbindet, ist die ältere Alternative. Fügen Sie zwischen den Werten ein Trennzeichen ein, zum Beispiel =A2&"|"&B2, und suchen Sie dann nach demselben verbundenen Text.
XLOOKUP und VLOOKUP im Vergleich
Die VLOOKUP-Seite von Microsoft nennt die Syntax als VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) und besagt, dass der Suchwert in der ersten Spalte des Bereichs stehen muss. Lassen Sie range_lookup weg, führt VLOOKUP eine Näherungssuche durch, die voraussetzt, dass die erste Spalte sortiert ist. Setzen Sie den Wert auf FALSCH für eine genaue Übereinstimmung. Dieselbe Seite empfiehlt, XLOOKUP zu verwenden. Laut Seite funktioniert XLOOKUP in jede Richtung und liefert standardmäßig genaue Übereinstimmungen.
Dieselbe Aufgabe mit beiden Funktionen, mit IDs in Spalte A und Namen in Spalte B:
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
Die Unterschiede, die im täglichen Einsatz zählen:
- VLOOKUP verweist auf das Ergebnis über die Spaltennummer. Wenn Sie eine Spalte in die Tabelle einfügen, ändert sich also die Bedeutung dieser Nummer. XLOOKUP verweist dagegen direkt auf den Ergebnisbereich.
- VLOOKUP kann nicht nach links suchen. XLOOKUP kann das.
- VLOOKUP arbeitet standardmäßig mit Näherungssuche. XLOOKUP arbeitet standardmäßig mit genauer Suche.
- XLOOKUP verfügt über ein Argument für den Fall, dass nichts gefunden wird, und eine integrierte Suchrichtung.
Sie müssen alte Arbeitsmappen nicht umschreiben. Wenn eine VLOOKUP-Formel funktioniert, lassen Sie sie unverändert. Verwenden Sie XLOOKUP für neue Formeln sowie für Suchen, die nach links suchen, den letzten Treffer ermitteln oder mehrere Rückgabespalten benötigen.
Welche Excel-Versionen XLOOKUP unterstützen
Hier ist Vorsicht geboten. Microsoft führt XLOOKUP für Excel für Microsoft 365 unter Windows und Mac, Excel 2024, Excel 2021 sowie die Apps für iPad, iPhone und Android auf. Ein Hinweis auf der Seite besagt, dass die Funktion in Excel 2016 oder 2019 nicht verfügbar ist, aber in Arbeitsmappen auftauchen kann, die in neueren Versionen erstellt wurden. Gleichzeitig nennt die Seite in der Zeile „Gilt für“ auch 2016 und 2019. Die Angaben widersprechen sich, daher sollten Sie Excel 2016 und 2019 nicht als sicher betrachten. Excel für das Web ist auf dieser Seite ebenfalls nicht aufgeführt.
Wenn Sie eine Arbeitsmappe an jemanden mit einer älteren Version senden, zeigt die Formel unter Umständen einen Fehler statt eines Werts an. Fragen Sie nach, welche Version Ihre Empfänger verwenden. Bei Dateien, die an viele Personen gehen, testen Sie auf der ältesten eingesetzten Version. Wenn Sie nicht testen können, verwenden Sie für diese Datei eine klassische Suchfunktion.
Häufige Fehler und Fehlermeldungen
- Bei Platzhaltern vergessen Sie
match_mode2 anzugeben. Die Suche sucht dann nach den wörtlichen Zeichen. - Ein binäres
search_mode(2 oder -2) bei unsortierten Daten verwenden. Laut Microsoft sind die Ergebnisse dann ungültig. - Suchbereich und Rückgabebereich haben unterschiedliche Größen. Machen Sie beide gleich lang.
- Versteckte Leerzeichen und als Text gespeicherte Zahlen. „100“ und 100 sind verschieden, daher schlägt die Suche fehl und Sie sehen #NV. Bereinigen Sie die Daten mit GLÄTTEN oder wandeln Sie die Zahlen um.
- Ein freundlicher Hinweis bei nicht gefundenen Werten kann Probleme verdecken. Testen Sie einen bekannten Wert, bevor Sie sich darauf verlassen.
- Der Rückgabebereich zeigt auf Zellen, in die das Ergebnis überlaufen müsste, und diese Zellen sind belegt.
- Vollständige Spaltenbezüge in sehr großen Tabellen. Verwenden Sie einen begrenzten Bereich oder eine Excel-Tabelle, damit Excel weniger Zellen durchsucht.
Verwandte Themen zum Vertiefen
- XLOOKUP im Vergleich zu INDEX und VERGLEICH. Beide Varianten suchen nach links, und die Kombination aus INDEX und VERGLEICH funktioniert auch in älteren Excel-Versionen. Lesen Sie dazu nach, wenn Sie Dateien mit Personen teilen, die Excel 2016 oder 2019 verwenden.
- Mehrere Treffer. XLOOKUP gibt nur den ersten Treffer zurück. Um alle Treffer zu erhalten, sehen Sie sich stattdessen die Funktion FILTER an.
- XLOOKUP mit Excel-Tabellen. Strukturierte Verweise passen Bereiche automatisch an wachsende Daten an und sind in Formeln gut lesbar.
- Daten aus Power Query. Nachdem Sie Daten in eine Tabelle geladen haben, kann XLOOKUP darin wie in jeder anderen Tabelle suchen. Aktualisieren Sie zuerst die Abfrage und prüfen Sie anschließend die Ergebnisse.
- Excel für das Web. Die XLOOKUP-Seite von Microsoft führt es nicht auf. Prüfen Sie in Ihrer eigenen Umgebung, ob die Funktion für Ihre Kolleginnen und Kollegen funktioniert, bevor Sie eine Datei freigeben.
Nächster Schritt
Nehmen Sie einen SVERWEIS aus Ihrer aktuellen Arbeitsmappe und schreiben Sie ihn als XLOOKUP neu. Ergänzen Sie eine Meldung für nicht gefundene Werte und vergleichen Sie die beiden Ergebnisse anhand einiger Ihnen bekannter IDs. Stimmen beide überein, haben Sie ein Muster, das Sie für jedes neue XLOOKUP in Excel wiederverwenden können.