XLOOKUP in Excel: So geben Sie den neuesten Wert zurück, ohne zu sortieren

Wenn Sie bereits von VLOOKUP auf XLOOKUP in Excel umgestiegen sind, haben Sie den ersten Schritt zu besseren Formeln gemacht. Aber die meisten Menschen verwenden es immer noch nur als direkten Ersatz - sie verpassen eine seiner leistungsstärksten Fähigkeiten.

Ein weniger bekannter, aber unglaublich nützlicher Trick ist das Umkehren der Suchrichtung. Mit einer kleinen Änderung kann XLOOKUP vom Ende Ihrer Daten starten und den neuesten passenden Eintrag zurückgeben, ohne dass sortiert oder Hilfsspalten verwendet werden müssen.

Warum VLOOKUP die neuesten Datensätze nicht findet

Der Kampf mit Excels Standard für die Top-down-Suche

Excel hat jahrzehntelang daran gearbeitet, uns das Denken von oben nach unten zu trainieren. Wenn Sie eine klassische Funktion wie SVERWEIS oder die INDEX/VERGLEICH-Kombination verwenden, um einen bestimmten Eintrag zu finden, durchsucht die Formel die erste Zeile Ihrer Daten und stoppt in dem Moment, in dem sie auf eine Übereinstimmung stößt. Wenn Sie 20 Einträge für „Bürobedarf“ über ein Jahr verteilt haben, liefern diese älteren Methoden konsequent den allerersten Eintrag — wie einen veralteten Preis vom letzten Januar.

Um dies zu umgehen, greifen viele auf Hilfsspalten zurück oder sortieren ihre Daten vor jeder Suche manuell in absteigender Reihenfolge. Dies kann eine destruktive Methode sein, um eine Arbeitsmappe zu verwalten: Das ständige Neusortieren Ihrer Quelldaten kann andere Abhängigkeiten zerstören, PivotTables durcheinanderbringen und das Risiko menschlicher Fehler erhöhen. Sie sollten das physische Layout Ihrer Tabelle nicht ändern müssen, nur um die Einschränkungen einer grundlegenden Suchformel zu erfüllen.

Die Suchrichtung mit XLOOKUP umkehren

Das Beherrschen des Arguments „search_mode“ für Bottom-up-Ergebnisse

XLOOKUP ist weitaus flexibler als seine Vorgänger, da es den Suchprozess in sechs verschiedene Argumente aufteilt. Die vollständige Syntax sieht folgendermaßen aus:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Die meiste Zeit benötigen Sie nur die ersten drei Argumente. Aber der Schlüssel, um den neuesten Eintrag zu finden, ist der search_mode. Standardmäßig verwendet Excel 1 (von oben nach unten suchen). Um dieses Verhalten umzukehren, verwenden Sie -1 (von unten nach oben suchen). Das weist Excel an, ganz unten in Ihren Daten zu beginnen und nach oben zu arbeiten. Dies funktioniert auch gut bei unordentlichen Protokollen. Wenn Ihre Einträge leicht durcheinander sind, ist die letzte übereinstimmende Zeile typischerweise die relevanteste.

Ein Beispiel aus der Praxis: Abrufen von Echtzeitpreisen aus einem Verkaufsprotokoll

Verwendung strukturierter Tabellenverweise für dynamische Aktualisierungen

Stellen Sie sich vor, Sie verfolgen Großhandelspreise für Obst. Die Preise schwanken täglich, und Sie protokollieren jedes neue Angebot am unteren Ende Ihrer Tabelle. In Zelle F2 möchten Sie den neuesten Preis für den Artikel nachschlagen, der in Zelle E2 eingetragen ist.

Bevor Sie die Formel schreiben, ist es hilfreich, Ihre Daten als Excel-Tabelle zu formatieren. Dadurch wird sichergestellt, dass Ihre XLOOKUP-Formel beim Hinzufügen neuer Zeilen am Ende die neuen Informationen automatisch „sieht“, ohne dass Sie Ihre Zellbereiche manuell aktualisieren müssen.

1. Klicken Sie irgendwo innerhalb Ihrer Daten.
2. Klicken Sie auf der Registerkarte Einfügen auf Tabelle (oder drücken Sie Strg+T).
3. Stellen Sie sicher, dass Meine Tabelle hat Überschriften aktiviert ist, und klicken Sie dann auf OK.
4. Mit der ausgewählten Tabelle öffnen Sie die Registerkarte Tabellenentwurf, geben einen Namen (wie T_Price_Log) in das Feld Tabellenname ein und drücken Enter.

Jetzt, da Ihre Daten organisiert sind, können Sie XLOOKUP in Zelle F2 verwenden, um den neuesten Preis abzurufen. Hier ist die Formel, die Sie benötigen:

=XLOOKUP($E$2, T_Price_Log[Item], T_Price_Log[Price], "Not found", 0, -1)

Die -1 am Ende sagt Excel, die Suche umzukehren und zwingt eine Suche von unten nach oben. Infolgedessen sagt Ihnen die Formel korrekt, dass der aktuelle Preis von Äpfeln 0,62 $beträgt.

Da Sie strukturierte Verweise verwendet haben, spiegelt die Formel automatisch den neuesten Eintrag wider.

Hätten Sie VLOOKUP oder INDEX/MATCH verwendet oder das Argument search_mode ganz weggelassen, würden Sie den ältesten Preis für Äpfel sehen (0,50 $, Zelle C2).

Modernisierung Ihres Workflows mit zusätzlichen XLOOKUP-Funktionen

Warum XLOOKUP der ultimative Ersatz für VLOOKUP ist

Die Beherrschung der Suchrichtung ist ein großer erster Schritt, aber XLOOKUP bietet mehrere andere Vorteile, die es den herkömmlichen Werkzeugen überlegen machen. Im Gegensatz zu VLOOKUP kann es Werte aus jeder Richtung zurückgeben (nicht nur aus den rechts befindlichen Spalten) und funktioniert sowohl vertikal als auch horizontal.

Über die Flexibilität hinaus ist XLOOKUP wesentlich robuster gegenüber Datenänderungen. Wenn Sie Spalten in Ihrer Quelltabelle einfügen oder löschen, bricht ein VLOOKUP häufig, da er auf eine statische Indexnummer angewiesen ist. XLOOKUP verwendet direkte Bereichsreferenzen oder strukturierte Tabellennamen, was bedeutet, dass Ihre Formeln intakt bleiben, selbst wenn Ihre Tabelle wächst und sich entwickelt.

Schließlich, während VLOOKUP oft in IFERROR eingebunden werden muss, um damit umzugehen, dass keine Übereinstimmungen gefunden werden, erlaubt das vierte Argument in XLOOKUP, einen "nicht gefunden"-Wert direkt in die Formel einzugeben. Das bedeutet, dass Sie sich nicht um Verschachtelungen kümmern müssen, und die Logik ist viel einfacher zu verfolgen.

Verbessern Sie Ihre XLOOKUP-Fähigkeiten

Sobald Sie diesen Richtungs-Trick gemeistert haben, gehen Sie einen Schritt weiter, indem Sie lernen, XLOOKUP mit mehreren Kriterien zu verwenden oder zweiseitige Suchen zu erstellen, um noch komplexere Datenbeziehungen zu verwalten. Ehe Sie sich versehen, werden Sie die Person sein, an die sich andere wenden, wenn ihre Tabellenkalkulationen nicht mehr richtig funktionieren.