Den letzten passenden Wert suchen

Die meisten Suchvorgänge geben den ersten gefundenen Treffer zurück. Manchmal benötigen Sie jedoch den letzten: den aktuellsten Preis für ein Produkt, die neueste Statusaktualisierung oder den letzten Eintrag für einen Kunden.

Wenn eine Liste mit der Zeit wächst und derselbe Schlüssel mehrfach vorkommt, ist die unterste Zeile normalerweise die aktuellste. Ein standardmäßiger VLOOKUP oder MATCH mit exakter Übereinstimmung greift stattdessen hartnäckig auf die oberste Zeile zu.

In dieser Lektion lernen Sie mehrere zuverlässige Methoden kennen, um den letzten passenden Wert abzurufen.

MATCH(value, range, 0) durchsucht den Bereich von oben nach unten und stoppt beim allerersten exakten Treffer. Wenn "Apple" in den Zeilen 2, 5 und 9 vorkommt, gibt MATCH 2 zurück.

Das ist ideal, wenn die Schlüssel eindeutig sind, ignoriert jedoch neuere Zeilen. Um das letzte Vorkommen zu erreichen, benötigen wir eine Technik, die von unten nach oben sucht oder die Position des letzten Treffers zurückgibt.

Wenn Sie eine moderne Version von Excel oder Google Sheets verwenden, macht XLOOKUP dies ganz einfach. Mit seinem fünften und sechsten Argument steuern Sie den Übereinstimmungsmodus und die Suchrichtung.

Übergeben Sie -1 als Suchmodus-Argument, um vom letzten zum ersten Eintrag zu suchen. XLOOKUP gibt dann den Wert zurück, der zum untersten passenden Schlüssel gehört.

Hier wird das Produkt in G1 mit A2:A10 abgeglichen und der passende Preis aus B2:B10 zurückgegeben, wobei die Suche unten beginnt.

In älteren Tabellenkalkulationen wird häufig ein bekannter Trick mit LOOKUP, der Zahl 2 und einer geschickten Division durch eine Bedingung verwendet.

Der Ausdruck 1/(A2:A10=G1) erzeugt für passende Zeilen den Wert 1 und für nicht passende Zeilen einen Divisionsfehler. LOOKUP sucht nach 2, einem Wert, der größer als jeder vorhandene Wert ist, überspringt die Fehler und landet beim letzten gültigen Wert 1. Anschließend gibt es den passenden Wert aus B2:B10 zurück.

Gehen Sie 1/(A2:A10=G1) Schritt für Schritt durch:

• In Zeilen, deren Schlüssel übereinstimmt, ergibt 1/TRUE = 1.
• In Zeilen ohne Übereinstimmung ergibt 1/FALSE einen #DIV/0!-Fehler.

LOOKUP ignoriert Fehler und gibt, wenn es sein Ziel (2) nicht findet, das Ergebnis zurück, das am letzten fehlerfreien Eintrag ausgerichtet ist. Da alle Treffer den Wert 1 haben, gewinnt die letzte 1. Sie erhalten also den Wert aus der Zeile mit dem letzten Treffer.

Sie können auch bei der INDEX-MATCH-Familie bleiben. Die Idee besteht darin, zunächst die Position des letzten Treffers zu finden und sie anschließend an INDEX zu übergeben.

Verwenden Sie denselben Divisionstrick innerhalb von MATCH. Suchen Sie nach 2 in 1/(A2:A10=G1), um die Zeilenposition des letzten Treffers zu erhalten. Übergeben Sie diese Position an INDEX für die Rückgabespalte.

Wenn das dritte Argument von MATCH weggelassen wird, ist der Standardwert 1. Das bedeutet eine ungefähre Übereinstimmung bei aufsteigend sortierten Daten. MATCH sucht dann nach dem größten Wert, der kleiner oder gleich 2 ist.

Das Array 1/(A2:A10=G1) enthält nur Einsen und Fehler. Der größte Wert, der höchstens 2 ist, lautet 1, und MATCH gibt die Position der letzten solchen 1 zurück. Diese Position entspricht genau der Zeile mit dem letzten Treffer.

Angenommen, A2:A10 enthält die im Zeitverlauf erfassten Bestellstatus für "Order-7" und B2:B10 den jeweiligen Statustext. "Order-7" kommt in den Zeilen 3, 6 und 9 vor.

• Das Treffer-Array markiert die Zeilen 3, 6 und 9 mit einer 1 und alle übrigen mit Fehlern.
• MATCH(2, ...) gibt 9 als Position zurück, gezählt ab dem Anfang des Bereichs – den letzten Treffer.
• INDEX gibt den Status aus dieser letzten Zeile zurück, also den aktuellsten.

Welchen Ansatz sollten Sie verwenden?

• XLOOKUP mit -1: am übersichtlichsten und am leichtesten lesbar, wenn Ihre App dies unterstützt.
• LOOKUP(2, 1/...): funktioniert nahezu überall und erfordert keine spezielle Version.
• INDEX-MATCH(2, 1/...): praktisch, wenn Sie zusätzlich die Position benötigen oder einen Wert aus einer anderen Spalte zurückgeben möchten.

Alle drei Methoden liefern dasselbe Ergebnis. Wählen Sie je nach verfügbaren Werkzeugen und gewünschter Lesbarkeit der Formel.

Achten Sie auf folgende Punkte:

• Unterschiedliche Bereichsgrößen: Der Bedingungsbereich und der Rückgabebereich müssen dieselbe Höhe haben, sonst werden die Zeilen falsch zugeordnet.
• Verborgene Duplikate: Nachgestellte Leerzeichen können dazu führen, dass sich "Apple " von "Apple" unterscheidet. Bereinigen Sie den Text zuerst mit TRIM.
• Kein Treffer: Der Trick gibt einen Fehler zurück, wenn nichts übereinstimmt. Verwenden Sie IFERROR, um einen verständlichen Ersatzwert anzuzeigen.

Sie können den Trick für den letzten Treffer mit zwei Bedingungen kombinieren. Multiplizieren Sie die Bedingungsprüfungen innerhalb der Division, sodass nur Zeilen, die beide Schlüssel erfüllen, eine 1 erzeugen.

Ermitteln Sie beispielsweise den aktuellsten Preis, bei dem das Produkt G1 und die Region G2 entspricht. Der LOOKUP(2, ...)-Trick landet weiterhin in der letzten passenden Zeile.

Das ist besonders nützlich für Protokolle mit Zeitstempeln, in denen dasselbe Produkt in mehreren Regionen vorkommt.

Überprüfen Sie Ihr Verständnis von Suchvorgängen nach dem letzten Treffer.

So geben Sie statt des ersten den letzten passenden Wert zurück:

• Verwenden Sie XLOOKUP(..., -1), um, sofern verfügbar, von unten nach oben zu suchen.
• Verwenden Sie den klassischen LOOKUP(2, 1/(range=key), result)-Trick in jeder Version.
• Verwenden Sie INDEX(result, MATCH(2, 1/(range=key))), wenn Sie zusätzlich die Position benötigen.

Achten Sie darauf, dass die Bereiche dieselbe Größe haben, entfernen Sie überflüssige Leerzeichen, und schließen Sie die Formel zur Sicherheit in IFERROR ein.