XLOOKUP(): Excels vielseitigste Suchfunktion

Wenn du schon einmal VLOOKUP() verwendet hast und nicht weiterkamst, weil du nach links suchen musstest, löst XLOOKUP() dieses Problem. Es ist Excels neuere Suchfunktion. Mit einer einzigen Formel kann sie VLOOKUP(), HLOOKUP() und sogar die meisten INDEX()- und MATCH()-Kombinationen ersetzen.

XLOOKUP() kann in jede Richtung suchen, mehr als eine Spalte auf einmal zurückgeben und eine eigene Nachricht anzeigen, wenn ein Wert fehlt. 

In diesem Leitfaden schauen wir uns zuerst die Syntax an. Danach bauen wir Schritt für Schritt echte Formeln auf, und zwar durchgängig mit einem einfachen Datensatz. Außerdem behandeln wir exakte und ungefähre Treffer, Mehrspalten-Rückgaben, Fehlerbehandlung und wie sich XLOOKUP() mit VLOOKUP() sowie INDEX()/MATCH() vergleichen lässt. 

So verwendest du XLOOKUP() in Excel

Lass uns eine echte XLOOKUP()-Formel von Grund auf erstellen. 

Unser Datensatz enthält fünf Produkte mit ID, Name, Kategorie und Preis in den Spalten A bis D, die Überschriften stehen in Zeile 1.

Datensatz für XLOOKUP(). Bild: Autor.

Angenommen, wir wollen den Preis von „P104“ finden. So gehst du Schritt für Schritt vor.

Einen einzelnen Wert nachschlagen

Klicke auf eine leere Zelle, in der das Ergebnis erscheinen soll. Ich verwende F2. Tippe dann folgende Formel:

=XLOOKUP("P104", A2:A6, D2:D6)

In dieser Formel: 

• „P104“ ist der lookup_value, also der gesuchte Wert

• A2:A6 ist das lookup_array, der Bereich, in dem Excel sucht

• D2:D6 ist das return_array, der Bereich, aus dem Excel die Antwort holt

Excel durchsucht Spalte A („Product ID“) nach „P104“, findet es in Zeile 5 und gibt den entsprechenden Wert aus Spalte D zurück: 45 $.

Statt „P104“ direkt zu tippen, kannst du auch auf eine Zelle verweisen, damit du den Suchwert ändern kannst, ohne die Formel selbst zu bearbeiten. In unserem Fall enthält Zelle A5 „P104“, daher lautet die Formel:

=XLOOKUP(A5, A2:A6, D2:D6)

XLOOKUP() in Excel verwenden. Bild: Autor.

Jetzt kannst du in A5 jede beliebige Produkt-ID eingeben, und der Preis aktualisiert sich automatisch.

Einen Wert aus einer anderen Spalte zurückgeben

Hier zeigt sich der große Unterschied zwischen XLOOKUP() und VLOOKUP(). Du bist nicht darauf festgelegt, nur Werte rechts der Suchspalte zurückzugeben.

Angenommen, statt des Preises möchtest du die Kategorie zu „P102“. Gleicher Aufbau, zeige mit dem return_array einfach auf Spalte C („Category“):

=XLOOKUP(A3, A2:A6, C2:C6)

Hier liefert A3 „Home“. Änderst du auf A5, aktualisiert es sich auf „Electronics“. Genauso kannst du das return_array erneut ändern, um den Produktnamen, den Preis oder jede andere Spalte zu ziehen – immer mit demselben lookup_value und lookup_array.

XLOOKUP() gibt einen Wert aus einer anderen Spalte zurück. Bild: Autor.

Mit VLOOKUP() müsstest du Spalten rechts der Suchspalte zählen und den Spaltenindex bei jedem anderen Zielwert anpassen. Mit XLOOKUP() zeigst du einfach direkt auf die gewünschte Spalte. 

Die Formel über mehrere Zeilen kopieren

Bisher haben wir jeweils nur einen Wert nachgeschlagen. In der Praxis brauchst du jedoch oft viele Werte auf einmal, zum Beispiel eine Liste von Bestell-IDs, zu denen jeweils ein passender Preis gehört.

So richtest du das ein: Spalte A, beginnend bei A2, enthält bereits unsere Liste der Product IDs. Die passenden Preise sollen daneben in Spalte F erscheinen.

Klicke dafür auf F2 und gib die Formel ein, wobei du für den Suchwert auf A2 verweist:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Klicke nun erneut auf F2 und ziehe den Ausfüllkästchen-Handler (das kleine Quadrat unten rechts an der Zelle) über die restlichen Zeilen nach unten.

Excel kopiert die Formel nach unten, und jede Zeile gibt jetzt den korrekten Preis für ihre eigene Product ID zurück. Die ID aus A2 erhält ihren Preis in F2, A3 in F3 und so weiter.

Die Formel in weitere Zellen kopieren. Bild: Autor.

Beachte die Dollarzeichen um A2:A6 und D2:D6. Das nennt sich absolute Referenz. Sie fixiert den Bereich, damit er sich beim Kopieren der Formel nach unten nicht verschiebt. 

Das ist hier wichtig, weil jede Zeile dieselbe Produktliste durchsuchen muss – nur jeweils mit einem anderen Suchwert.

Exakte und ungefähre Treffer in XLOOKUP() verwenden

Standardmäßig sucht XLOOKUP() nach einer exakten Übereinstimmung. Wird keine gefunden, liefert die Funktion einen Fehler – es sei denn, du hast das Argument if_not_found definiert, wie oben beschrieben. 

XLOOKUP() kann aber auch den nächstliegenden Treffer finden – über das Argument match_mode. 

Exakte Übereinstimmung (Standard)

Dafür musst du nichts zusätzlich angeben. Alle Formeln, die wir bisher gebaut haben, verwenden bereits die exakte Suche, weil dies das Standardverhalten von XLOOKUP() ist.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel sucht nach dem exakten Wert in A2 und gibt nur dann ein Ergebnis zurück, wenn es genau diesen Wert im Suchbereich findet.

Wenn du es ausdrücklich angeben willst, füge als viertes Argument eine Null ein:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() liefert eine exakte Übereinstimmung. Bild: Autor.

Siehst du das Leerzeichen vor der 0? Damit überspringst du if_not_found, da wir es hier leer lassen, und gehst direkt zu match_mode. Meist kannst du dieses Argument komplett weglassen und Excel bei der exakten Suche belassen.

Ungefähre Übereinstimmung

Angenommen, deine Daten haben nicht für jeden möglichen Suchwert einen Eintrag. Denk an eine Provisionsstaffel: Vertriebsmitarbeitende erhalten je nach Umsatzbereich unterschiedliche Sätze. Es gibt dann keine Zeile für jeden Dollarbetrag, sondern nur die Untergrenzen der Bereiche.

Hier hilft eine ungefähre Übereinstimmung. 

Setze match_mode auf -1, und XLOOKUP() findet den größten Wert, der kleiner oder gleich deinem Suchwert ist.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Stell dir vor, dein Suchbereich enthält Umsatzschwellen von 0 $, 5.000 $ und 10.000 $, jeweils mit einem eigenen Provisionssatz. 

Eine Person mit 7.500 $ Umsatz passt auf keine Zeile exakt. Mit match_mode = -1 findet XLOOKUP() 5.000 $, also die größte Schwelle, die 7.500 $ nicht übersteigt, und gibt den zugehörigen Satz zurück.

Setzt du match_mode stattdessen auf 1, kehrt XLOOKUP() die Logik um. Es findet den kleinsten Wert, der größer oder gleich deinem Suchwert ist. Das ist praktisch bei Versandgewichtsgrenzen, bei denen auf die nächste Stufe aufgerundet wird.

XLOOKUP() liefert eine ungefähre Übereinstimmung. Bild: Autor.

Hinweis: Die ungefähre Übereinstimmung setzt voraus, dass dein Suchbereich sortiert ist – aufsteigend für -1, absteigend für 1. Ist die Sortierung falsch, kann XLOOKUP() ohne Fehlermeldung ein falsches Ergebnis liefern, was noch problematischer ist. Prüfe daher die Sortierung, bevor du dich darauf verlässt.

Nach links suchen mit XLOOKUP()

VLOOKUP() durchsucht nur die linke Spalte eines Bereichs und gibt einen Wert aus einer Spalte rechts davon zurück – in die andere Richtung geht es nicht. Sein drittes Argument ist keine Bereichsangabe, sondern ein vom linken Rand gezählter Spaltenindex. 

Du kannst also nicht „eine Spalte nach links“ angeben. Wenn der benötigte Wert links der Suchspalte liegt, musst du die Daten umbauen oder eine Hilfsspalte ergänzen, nur damit die Suche funktioniert.

XLOOKUP() hat diese Einschränkung nicht, da du die Such- und Rückgabebereiche getrennt angibst.

Nehmen wir unsere Produktliste: Product ID in Spalte A, Product Name in Spalte B. 

Angenommen, du hast einen Produktnamen und willst die ID finden – also das Gegenteil von dem, was VLOOKUP() kann.

Gib in C9 „Bluetooth Speaker“ ein (ein Name, der in der Liste vorkommt). In E2 eingeben:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() durchsucht Spalte B nach „Bluetooth Speaker“, findet den Eintrag in Zeile 5 und gibt „P104“ aus Spalte A zurück – obwohl Spalte A links von Spalte B liegt. 

XLOOKUP() kann nach links suchen – anders als VLOOKUP(). Bild: Autor.

Mehrere Spalten mit XLOOKUP() zurückgeben

XLOOKUP() kann mithilfe dynamischer Arrays mehrere Spalten in einer einzigen Formel zurückgeben. Zeige dafür mit return_array auf einen mehrspaltigen Bereich statt auf eine einzelne Spalte, und XLOOKUP() „verschüttet“ die gesamte passende Zeile über die benötigten Zellen. 

Probieren wir das mit unserer Produktliste. 

In C9 tippe ich eine Product ID, zum Beispiel „P104“. In E2 gebe ich ein:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Beachte: Das return_array ist hier B2:D6, also drei Spalten breit – Product Name, Category und Price. Nach Enter findet XLOOKUP() „P104“ in Spalte A und gibt „Bluetooth Speaker“, „Electronics“ und 45 $ auf einmal zurück – verschüttet in E9, F9 und G9.

XLOOKUP() gibt mehrere Spalten mit einer Formel zurück. Bild: Autor.

Das ist der sogenannte „Spillbereich“. E9 enthält die eigentliche Formel, F9 und G9 füllen sich automatisch – ohne eigene Formeln. 

Klicke auf F9, und in der Formelleiste siehst du die ursprüngliche Formel ausgegraut – nicht etwas, das direkt in F9 steht. Versuchst du in F9 zu tippen, während E9 verschüttet, zeigt Excel einen Spill-Fehler. 

Wie du das behebst, siehst du im Abschnitt „Häufige Fehler“.

Fehlende Werte in XLOOKUP() behandeln

Standardmäßig wirft XLOOKUP() den Fehler #N/A, wenn kein Treffer gefunden wird. Das Argument if_not_found kann das abfangen, indem es statt der Standardfehlermeldung einen gewünschten Text anzeigt.  

Angenommen, in C9 tippe ich eine Product ID, die in meinen Daten nicht existiert, etwa „P999“.

Eine eigene Nachricht zurückgeben

In G2 gebe ich ein:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Da „P999“ in Spalte A nicht existiert, überspringt XLOOKUP() den Fehler und gibt „Not Found“ zurück. Ändere den Text nach Bedarf, z. B. „Check Product ID“ oder „No Match“.

Eine eigene Nachricht statt des Standardfehlers anzeigen. Bild: Autor.

Eine leere Zelle zurückgeben

Manchmal möchtest du eine wirklich leere Zelle ohne Text. Verwende dazu zwei Anführungszeichen ohne Inhalt dazwischen, so:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Das ist nützlich in Berichten, in denen ein „Not Found“ neben echten Werten störend wirken würde.

Eine leere Zelle statt des Standardfehlers anzeigen. Bild: Autor.

Als echten Fehler stehen lassen

Wenn du if_not_found komplett weglässt, bleibt XLOOKUP() bei #N/A. Das ist nicht immer schlecht. Wenn du eine Formel für dich selbst baust und Tippfehler oder fehlende Einträge sofort sehen willst, ist #N/A hilfreicher als eine stille leere Zelle, die das Problem verdeckt.

Hinweis: if_not_found fängt nur fehlende Treffer ab. Wenn zum Beispiel lookup_array und return_array unterschiedlich groß sind, wirft XLOOKUP() weiterhin #VALUE!, und if_not_found verhindert das nicht. Diesen Fall behandeln wir weiter unten im Abschnitt „Häufige Fehler“.

Erweiterte XLOOKUP()-Funktionen

Schauen wir uns die fortgeschrittenen Funktionen von XLOOKUP() an.

Von unten nach oben suchen

Setze search_mode auf -1, und XLOOKUP() durchsucht den Bereich von unten nach oben statt von oben nach unten. Nutze das bei doppelten Werten, wenn du den jüngsten Eintrag willst – nicht den ersten.

Angenommen, mein Datensatz enthält drei identische Customer IDs. In Zelle E2 gebe ich die Standardformel ein, die von oben nach unten sucht: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Das wird vom Beginn der Tabelle berechnet und zeigt das Ergebnis des ersten Treffers. 

Will ich stattdessen von unten suchen, verwende ich diese Formel: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Hier weist -1 Excel an, die Suche am unteren Ende zu starten. Zurückgegeben wird 340 – der erste Treffer von unten.  

XLOOKUP() durchsucht Daten von unten nach oben. Bild: Autor.

Binärer Suchmodus

Setze search_mode auf 2, und XLOOKUP() wechselt von der zeilenweisen Prüfung zu einer binären Suche. 

So funktioniert sie: 

• Sie schaut zuerst auf das mittlere Element des Bereichs. 

• Gibt es keinen Treffer, verwirft sie die Hälfte, in der der Wert nicht liegen kann, und prüft die Mitte des verbleibenden Teils. 

• So geht es weiter, jeweils halbierend, bis ein Treffer gefunden ist. 

• Mit search_mode = -2 läuft das genauso, startet aber vom letzten statt vom ersten Eintrag.

Gib in E2 ein:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Diese Formel liefert stattdessen 200, also eine völlig andere Zeile.

Der binäre Suchmodus liefert das falsche Ergebnis. Bild: Autor.

Denn die binäre Suche funktioniert nur, wenn dein lookup_array sortiert ist. Unsere Spalte mit Customer IDs ist nicht sortiert – sie lautet C100, C101, C100, C102, C100 – daher verwirft die binäre Suche die falsche Hälfte und liefert einen falschen Treffer. 

Schauen wir, was passiert, wenn die Daten sortiert sind. 

Sortiere dasselbe Protokoll aufsteigend nach Customer ID (alle drei C100 am Anfang) und führe dieselbe Formel erneut aus. 

Der binäre Suchmodus funktioniert mit sortierten Daten. Bild: Autor.

Diesmal liefert E2 den Wert 120 – genau wie die normale Suche. Das ist der Unterschied: Die binäre Suche überspringt nur Daten, die sie sicher ausschließen kann – und das gilt nur, wenn die Daten vorher sortiert sind. 

Verschachtelte XLOOKUP()-Formeln

Verschachtle eine XLOOKUP() in eine andere, wenn das Ergebnis der ersten Suche als Bereich für eine zweite Suche dienen soll, statt eines festen Rückgabebereichs.

Angenommen, du willst einen Preis finden, aber aus welcher Spalte er kommt, hängt von einer separaten Suche ab – etwa der Preisklasse eines Kunden.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

In dieser Formel ermittelt die innere XLOOKUP() anhand von D9 zunächst, aus welcher Spalte gelesen werden soll. Die äußere XLOOKUP() nutzt dieses Ergebnis dann als return_array. 

Verschachteltes XLOOKUP(). Bild: Autor.

XLOOKUP() vs. VLOOKUP()

Wir haben im Detail gesehen, wie XLOOKUP() funktioniert. Hier ist der Vergleich mit VLOOKUP().

Zwei Dinge sind besonders wichtig: 

• VLOOKUP() verwendet standardmäßig die ungefähre Suche. Wenn du das vierte Argument vergisst, erscheint kein Fehler – es kommt einfach stillschweigend ein falscher Wert zurück.

• Wenn du in einem VLOOKUP()-Sheet eine Spalte einfügst, ziehen Formeln stillschweigend den falschen Wert. XLOOKUP() vermeidet das, weil es direkt mit Bereichen statt mit Spaltenzählung arbeitet.

Das macht VLOOKUP() nicht überflüssig. Für einfache, statische Sheets funktioniert es weiterhin gut. Für neue Arbeitsmappen ist XLOOKUP() jedoch die sicherere Standardwahl.

XLOOKUP() vs. INDEX() und MATCH()

Bevor es XLOOKUP() gab, waren INDEX() und MATCH() zusammen die Standardlösung, um die Grenzen von VLOOKUP() zu umgehen. Dabei werden zwei Funktionen statt einer verwendet. 

• MATCH() findet die Position deines Suchwerts. 

• INDEX() verwendet diese Position, um den Zielwert von irgendwoher zu holen – links oder rechts, egal.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Zum Vergleich die XLOOKUP()-Variante, die wir hier durchgängig nutzen:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() funktioniert nur in Microsoft 365 und Excel 2021 und neuer, wie oben erwähnt. Wenn deine Arbeitsmappe in Excel 2019 oder 2016 geöffnet werden muss oder du mit Personen auf älteren Versionen teilst, laufen INDEX() und MATCH() weiterhin überall.

Es gibt noch einen selteneren Sonderfall. 

INDEX() und MATCH() trennen „Position finden“ und „Wert zurückgeben“. Das hilft bei manchen komplexen Verschachtelungen. Das ist jedoch selten relevant.

Abgesehen von diesen beiden Fällen ist XLOOKUP() die bessere Wahl für neue Arbeitsmappen. 

Häufige XLOOKUP()-Fehler und wie du sie behebst

Hier ist eine Kurzreferenz der wahrscheinlichsten Probleme – und wie du jedes davon löst.

#N/A-Fehler

Das ist die Standardreaktion von XLOOKUP(), wenn kein Treffer gefunden wird. Mir ist das oben passiert, als lookup_value und lookup_array nicht zusammenpassten und XLOOKUP() in Spalte A nichts finden konnte.

So behebst du das: Prüfe, ob dein lookup_value tatsächlich im lookup_array existiert – gleich geschrieben und gleich formatiert. Wenn fehlende Treffer normal sind, nutze if_not_found, um #N/A durch etwas Nützlicheres zu ersetzen, etwa „Not Found“ oder eine leere Zelle, wie oben gezeigt. 

Falscher Suchbereich

Das passiert, wenn lookup_array und return_array nicht sauber ausgerichtet sind: falsche Zeilen, falsche Spalten oder unterschiedlich große Bereiche. 

Je nach Excel- oder Sheets-Version erscheint das als #VALUE!-Fehler oder manchmal als #N/A mit einer separaten Erklärung des eigentlichen Problems. Lass dich von #N/A nicht täuschen – es kann mehr dahinterstecken. 

So behebst du das: Achte darauf, dass lookup_array und return_array dieselbe Anzahl Zeilen bzw. Spalten abdecken. Wenn lookup_array A2:A5 ist, muss return_array ebenfalls die Zeilen 2 bis 5 umfassen – nicht etwa 4 bis 5 oder einen anderen Versatz.

Datentyp-Konflikte

VLOOKUP() kann scheitern, wenn statt Zahlen Text erwartet wird oder umgekehrt. XLOOKUP() ist davon ähnlich betroffen. Wenn dein lookup_value eine Zahl ist, dein lookup_array dieselben Werte aber als Text speichert (oder umgekehrt), findet XLOOKUP() keinen Treffer – auch wenn die Werte auf dem Bildschirm identisch aussehen.

So behebst du das: Prüfe, ob lookup_value und lookup_array denselben Datentyp verwenden. Als Text gespeicherte Zahlen sind oft linksbündig statt rechtsbündig. Um Text in Zahl zu wandeln, umschließe deinen lookup_value mit VALUE(), oder nutze TEXT() für den umgekehrten Weg – je nachdem, wo der Konflikt liegt.

Spill-Fehler

Das passiert, wenn dein return_array mehr als eine Spalte umfasst, die benötigten Zellen für den Spill aber nicht leer sind. Excel braucht freien Platz neben der Formelfeld, um die Ergebnisse zu verschütten. Steht dort schon etwas, scheitert die gesamte Formel.

So behebst du das: Räume die Zellen frei, in die die Ergebnisse sollen, und gib dann die Formel ein. Wenn du dort Inhalte brauchst, verschiebe deine XLOOKUP()-Formel an einen Ort mit genügend freiem Platz.

Fazit 

Du weißt jetzt, wie du XLOOKUP() für Einzelwerte, mehrere Spalten, fehlende Daten und sogar verschachtelte Suchen über zwei Bereiche einsetzt. Das deckt eine Menge ab.

Verwende XLOOKUP() künftig als Standard in neuen Arbeitsmappen. Es ist flexibler als VLOOKUP(), leichter zu lesen als INDEX/MATCH() und erspart dir oft den IFERROR()-Mantel.

Wenn du tiefer einsteigen willst, lohnt es sich trotzdem, INDEX() und MATCH() separat zu beherrschen.