Nach links und von unten suchen

Eine der größten Einschränkungen von VLOOKUP war, dass nur Werte rechts von der Suchspalte zurückgegeben werden konnten. Für XLOOKUP gilt diese Einschränkung nicht.

Da Sie das Sucharray und das Rückgabearray getrennt angeben, kann sich das Ergebnis überall befinden – links, rechts, oberhalb oder unterhalb der durchsuchten Spalte.

Angenommen, die IDs stehen in Spalte B und die Namen in Spalte A, also links davon. Mit VLOOKUP wäre das nur durch eine Umordnung der Spalten möglich.

Mit XLOOKUP durchsuchen Sie einfach Spalte B und geben Spalte A zurück. Die Funktion gibt problemlos einen Wert zurück, der links von der Suchspalte steht.

XLOOKUP verfügt über ein optionales fünftes Argument, search_mode, das die Suchrichtung festlegt.

• 1 – von Anfang bis Ende suchen (Standard)
• -1 – von Ende bis Anfang suchen
• 2 – binäre Suche in aufsteigend sortierten Daten
• -2 – binäre Suche in absteigend sortierten Daten

Standardmäßig durchsucht XLOOKUP die Daten von oben nach unten und gibt die erste gefundene Übereinstimmung zurück.

Wenn ein Wert mehrmals in Ihrer Liste vorkommt, erhalten Sie mit dieser Standardeinstellung den frühesten Eintrag. Das ist meistens ausreichend, aber manchmal benötigen Sie stattdessen den neuesten Eintrag.

Setzen Sie search_mode auf -1, um von unten nach oben zu suchen. XLOOKUP gibt dann den letzten passenden Wert zurück.

Da if_not_found und match_mode vor search_mode stehen, müssen Sie die vorherigen Argumente als Platzhalter angeben, auch wenn sie leer sind.

Stellen Sie sich ein Protokoll vor, in dem jede Zeile eine Statusaktualisierung für eine Bestellung festhält – der älteste Eintrag steht oben.

Eine Suche von oben nach unten liefert den ersten Status der Bestellung. Eine Suche von unten nach oben mit -1 liefert den neuesten Status. Dieselben Daten können je nach Suchrichtung also zwei sehr unterschiedliche Ergebnisse liefern.

Argumente werden anhand ihrer Position zugeordnet. Sie können daher nicht direkt zum sechsten Argument springen, ohne die davorliegenden Argumente anzugeben.

In =XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1) ist "Not found" das Argument if_not_found und 0 das Argument match_mode (exakte Übereinstimmung). Sie füllen diese Positionen aus, damit -1 an der Stelle von search_mode steht.

Das benachbarte Argument des fünften Arguments, match_mode, legt fest, wie Übereinstimmungen ermittelt werden:

• 0 – exakte Übereinstimmung (Standard)
• -1 – exakte Übereinstimmung oder nächstkleinerer Wert
• 1 – exakte Übereinstimmung oder nächstgrößerer Wert
• 2 – Platzhalterübereinstimmung mit * und ?

Für Suchen nach links und von unten belassen Sie diesen Wert normalerweise bei 0.

Ein Tabellenblatt zu Preisänderungen listet jedes Produkt bei jeder Preisaktualisierung auf. Um den aktuellen Preis zu erhalten, benötigen Sie den letzten Eintrag für das jeweilige Produkt.

Eine Suche von unten mit -1 gibt den neuesten Preis zurück, ohne dass Sie sortieren oder zusätzliche Hilfsspalten anlegen müssen.

Die Suchmodi 2 und -2 verwenden eine schnellere binäre Suche. Dafür muss das Sucharray sortiert sein – für 2 aufsteigend und für -2 absteigend.

Bei sehr großen sortierten Listen ist diese Suche deutlich schneller. Wenn die Daten nicht wirklich sortiert sind, kann die binäre Suche falsche Ergebnisse liefern. Verwenden Sie sie daher mit Bedacht.

Sie können jetzt sowohl festlegen, wo als auch in welche Richtung XLOOKUP sucht:

• Geben Sie Werte problemlos aus Bereichen links von der Suchspalte zurück
• 1 für die erste Übereinstimmung (Standard)
• -1 für die letzte Übereinstimmung
• 2 oder -2 für eine schnelle binäre Suche in sortierten Daten

Denken Sie daran, die Positionen von if_not_found und match_mode vor search_mode auszufüllen.

Testen Sie Ihr Verständnis des Arguments search_mode.

Sie haben die Möglichkeiten von XLOOKUP in Bezug auf die Suchrichtung kennengelernt:

• Rückgabearrays können sich links vom Sucharray befinden
• search_mode (das 6. Argument) legt die Suchrichtung fest
• -1 findet die letzte Übereinstimmung und eignet sich ideal für Suchen nach dem neuesten Datensatz
• 2 und -2 ermöglichen eine schnelle binäre Suche in sortierten Daten

Als Nächstes geben Sie mit einem einzigen XLOOKUP ganze Zeilen oder Spalten zurück.