Die XLOOKUP-Syntax

XLOOKUP ist die moderne Alternative zu VLOOKUP und HLOOKUP. Die Funktion findet einen Wert in einer Spalte (oder Zeile) und gibt den zugehörigen Wert aus einer anderen zurück.

Der größte Vorteil: Sie geben in zwei getrennten Bereichen an, wo gesucht werden soll und was zurückgegeben werden soll. Sie müssen keine Spalten zählen und sich keine Gedanken darüber machen, auf welcher Seite sich das Ergebnis befindet.

XLOOKUP ist in Excel 365, Excel 2021 und Google Sheets verfügbar.

Für den einfachsten XLOOKUP benötigen Sie nur drei Angaben:

• lookup_value – wonach Sie suchen
• lookup_array – der Bereich, in dem gesucht wird
• return_array – der Bereich, aus dem das Ergebnis abgerufen wird

Lesen Sie die Formel so: Finde diesen Wert in dieser Spalte und gib mir das zugehörige Element aus dieser Spalte.

Stellen Sie sich eine Produktliste vor. In Spalte A stehen die Produktnamen und in Spalte B die Preise. Sie möchten den Preis von "Keyboard" ermitteln.

Sie suchen den Namen in A2:A20 und geben den Preis aus B2:B20 zurück. XLOOKUP durchsucht den lookup_array, findet "Keyboard" und gibt anschließend den Wert zurück, der in derselben Zeile des return_array steht.

"Keyboard" direkt in die Formel einzutragen, ist unflexibel. Es ist wesentlich besser, den Suchwert auf eine Zelle zu verweisen.

Wenn Sie den Produktnamen in D2 eingeben, reagiert die Formel automatisch, sobald Sie ihn ändern. So funktionieren dynamische Suchwerkzeuge und Dashboards.

Der lookup_array und der return_array sollten dieselbe Höhe haben (also dieselbe Anzahl an Zeilen). XLOOKUP ordnet sie zeilenweise einander zu.

Wenn A2:A20 19 Zeilen umfasst, muss auch der return_array 19 Zeilen umfassen. Bei einer Abweichung entsteht ein Fehler #VALUE!, weil XLOOKUP die Zeilen nicht korrekt einander zuordnen kann.

Der return_array muss nicht neben dem lookup_array liegen. Angenommen, die Namen stehen in Spalte A, Sie möchten aber den Lagerbestand aus Spalte E zurückgeben.

Verweisen Sie einfach mit dem return_array auf E2:E20. XLOOKUP spielt es keine Rolle, wie groß der Abstand zwischen den Spalten ist – anders als VLOOKUP, das die Spalten nach rechts zählt.

Besonders benutzerfreundlich an XLOOKUP ist, dass standardmäßig nach einer exakten Übereinstimmung gesucht wird.

Bei VLOOKUP war standardmäßig eine ungefähre Übereinstimmung eingestellt. Wenn das abschließende FALSE vergessen wurde, wurden daher stillschweigend falsche Ergebnisse zurückgegeben. XLOOKUP macht es umgekehrt: Sie erhalten eine exakte Übereinstimmung, sofern Sie nicht bewusst etwas anderes anfordern.

XLOOKUP verarbeitet auch horizontale Daten. Wenn Monatsnamen in Zeile 1 und Umsätze in Zeile 2 stehen, können Sie die Zeile statt einer Spalte durchsuchen.

Die Struktur der Formel bleibt gleich: Suchwert, die Zeile mit den Monaten und die Zeile mit den Umsätzen. Eine Funktion ersetzt damit sowohl VLOOKUP als auch HLOOKUP.

Da XLOOKUP einen einzelnen Wert zurückgibt, können Sie die Funktion direkt in einer Berechnung verwenden.

Multiplizieren Sie beispielsweise einen ermittelten Stückpreis mit einer Menge in E2. Zuerst wird der Suchvorgang ausgeführt und der Preis zurückgegeben, anschließend verwendet die Multiplikation dieses Ergebnis. Dadurch bleibt Ihr Tabellenblatt übersichtlich und gut lesbar.

Sie können Bezüge auf ganze Spalten wie A:A und B:B verwenden, damit die Formel auch funktioniert, wenn neue Zeilen hinzukommen.

Das ist praktisch, kann große Tabellenblätter aber etwas verlangsamen. Bei übersichtlichen Daten ist es eine gute Gewohnheit, den genauen Bereich oder eine Excel-Tabelle zu verwenden.

Im Vergleich zu VLOOKUP ist XLOOKUP:

• Richtungsunabhängig – Rückgabespalten können links oder rechts vom Suchbereich liegen
• Sicherer – standardmäßig wird eine exakte Übereinstimmung verwendet
• Stabiler – das Einfügen von Spalten führt nicht zu Fehlern
• Einfacher – kein Zählen von Spaltenindizes

Sobald Sie die drei wichtigsten Argumente beherrschen, werden die meisten alltäglichen Suchvorgänge mühelos.

Testen Sie Ihr Verständnis der grundlegenden XLOOKUP-Syntax.

Sie haben die drei wichtigsten Argumente von XLOOKUP kennengelernt:

• lookup_value – wonach gesucht wird
• lookup_array – wo gesucht wird
• return_array – was zurückgegeben wird

Standardmäßig wird eine exakte Übereinstimmung verwendet, der Rückgabebereich kann sich an beliebiger Stelle befinden, und die Funktion funktioniert sowohl mit Spalten als auch mit Zeilen. Als Nächstes erfahren Sie, wie Sie fehlende Werte elegant behandeln.