Ganze Zeilen oder Spalten zurückgeben

Bisher hat XLOOKUP einen einzelnen Wert zurückgegeben. Es kann jedoch auch eine gesamte Zeile oder Spalte mit Daten auf einmal zurückgeben.

Wenn eine Formel mehrere Werte zurückgibt, laufen diese automatisch in die benachbarten Zellen über. So kann ein einziges XLOOKUP einen ganzen kleinen Datensatz ausfüllen.

Der Trick besteht darin, das Rückgabearray über mehrere Spalten zu erstrecken. Geben Sie statt B2:B20 einfach B2:E20 zurück.

XLOOKUP findet die passende Zeile und gibt anschließend jede Spalte dieser Zeile aus dem Rückgabearray zurück. Eine Formel, vier Ergebnisse.

Geben Sie die Formel in eine einzelne Zelle ein, zum Beispiel F2, und drücken Sie die Eingabetaste. Die Werte erscheinen in F2, G2, H2 und I2.

Ein schwacher blauer Rahmen kennzeichnet den Überlaufbereich. Sie bearbeiten nur die Zelle oben links. Die übrigen Zellen werden durch den Überlauf gefüllt und können nicht direkt geändert werden.

Eine Mitarbeitertabelle enthält die ID in Spalte A sowie Name, Abteilung, Rolle und Gehalt in den Spalten B bis E.

Geben Sie eine ID in D2 ein, und ein einziges XLOOKUP gibt den gesamten Datensatz zurück. Ändern Sie die ID, und die gesamte Zeile wird sofort aktualisiert – ein kleines Suchwerkzeug in einer einzigen Formel.

Dasselbe Prinzip funktioniert auch vertikal. Wenn Sie eine Kopfzeile durchsuchen, können Sie eine gesamte Ergebnisspalte zurückgeben.

Hier durchsucht XLOOKUP die Kopfzeile B1:E1 nach einer Bezeichnung aus D2 und lässt die vollständige Spalte B2:E50 unterhalb der passenden Überschrift überlaufen.

Sobald eine Formel überläuft, können Sie mit der Zelladresse gefolgt von einer Raute auf den gesamten Überlaufbereich verweisen, zum Beispiel mit F2#.

Das ist sehr praktisch: Eine SUM-Formel über einer übergelaufenen Zeile bleibt auch dann korrekt, wenn sich die Breite der Zeile ändert, denn F2# bedeutet immer „der gesamte Überlauf ab F2“.

Da das Ergebnis ein Array ist, können Sie es direkt an Funktionen übergeben, die Bereiche akzeptieren.

Sie können die Suche beispielsweise in SUM einschließen, um die Summe einer zurückgegebenen Zeile mit Monatswerten zu bilden – alles in einer einzigen Formel und ohne Hilfszellen.

Eine Formel mit Überlauf benötigt leere Zellen, die sie ausfüllen kann. Wenn bereits eine Zelle im Überlaufbereich Daten enthält, gibt XLOOKUP den Fehler #SPILL! zurück.

Die Lösung ist einfach: Löschen Sie die blockierenden Zellen oder verschieben Sie die Formel in einen freien Bereich. Der gesamte Überlaufbereich muss leer sein.

Für eine übersichtliche Suchkarte können Sie auch die Feldüberschriften überlaufen lassen.

Platzieren Sie ein XLOOKUP, das die Datenzeile zurückgibt, und verweisen Sie darüber auf den Überschriftenbereich. Wenn sich der Überlauf verbreitert oder verkleinert, bleiben Ihre Bezeichnungen weiterhin an den zurückgegebenen Spalten ausgerichtet.

Sie können sogar ein XLOOKUP in ein anderes verschachteln. Das innere XLOOKUP gibt eine ganze Spalte zurück, und das äußere wählt daraus eine einzelne Zelle aus.

So entsteht eine echte zweidimensionale Suche, bei der sowohl eine Zeile als auch eine Spalte abgeglichen werden – vollständig mit XLOOKUP. Das ist eine elegante Alternative zu INDEX-MATCH-MATCH.

Sie wissen jetzt, dass XLOOKUP mehr als einen Wert zurückgeben kann:

• Ein mehrspaltiges Rückgabearray lässt eine ganze Zeile überlaufen
• Ein mehrzeiliges Rückgabearray lässt eine ganze Spalte überlaufen
• Mit dem Suffix # können Sie auf den Überlauf verweisen
• Leeren Sie blockierende Zellen, um #SPILL! zu vermeiden

So wird aus einer einzigen Formel eine vollständige Datensatzanzeige.

Sie haben den XLOOKUP-Kurs abgeschlossen und gelernt, Ergebnisse überlaufen zu lassen:

• Erweitern Sie das Rückgabearray, damit eine vollständige Zeile oder Spalte überläuft
• Überläufe füllen leere benachbarte Zellen und werden durch einen blauen Rahmen gekennzeichnet
• Verweisen Sie mit dem Suffix # auf einen Überlauf, zum Beispiel mit F2#
• Halten Sie den Überlaufbereich frei, um #SPILL! zu vermeiden

Mit beherrschter Syntax, Fallbacks, Suchrichtung und Überläufen kann XLOOKUP fast jede herkömmliche Suchformel ersetzen, die Sie schreiben.