Verwendung der Excel XLOOKUP-Funktion -

Zusammenfassung

Die Excel XLOOKUP-Funktion ist ein moderner und flexibler Ersatz für ältere Funktionen wie VLOOKUP, HLOOKUP und LOOKUP. XLOOKUP unterstützt ungefähre und genaue Übereinstimmungen, Platzhalter (*?) Für Teilübereinstimmungen und Suchvorgänge in vertikalen oder horizontalen Bereichen.

Zweck

Suchwerte in Bereich oder Array

Rückgabewert

Übereinstimmende Werte aus dem Rückgabearray

Syntax

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumente

Lookup - Der Lookup-Wert.
lookup_array - Das zu suchende Array oder der zu durchsuchende Bereich.
return_array - Das zurückzugebende Array oder der zurückzugebende Bereich.
not_found - (optional) Wert, der zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wurde.
match_mode - (optional) 0 = genaue Übereinstimmung (Standard), -1 = genaue Übereinstimmung oder nächstkleinere Übereinstimmung, 1 = genaue Übereinstimmung oder nächstgrößere Übereinstimmung, 2 = Platzhalterübereinstimmung.
Suchmodus - (optional) 1 = Suche vom ersten (Standard), -1 = Suche vom letzten, 2 = binäre Suche aufsteigend, -2 = binäre Suche absteigend.

Ausführung

Excel 365

Verwendungshinweise

XLOOKUP ist ein moderner Ersatz für die VLOOKUP-Funktion. Es ist eine flexible und vielseitige Funktion, die in einer Vielzahl von Situationen eingesetzt werden kann.

XLOOKUP kann Werte in vertikalen oder horizontalen Bereichen finden, ungefähre und genaue Übereinstimmungen durchführen und Platzhalter (*?) Für Teilübereinstimmungen unterstützen. Darüber hinaus kann XLOOKUP Daten ab dem ersten oder dem letzten Wert suchen (siehe Details zu Übereinstimmungstyp und Suchmodus unten). Im Vergleich zu älteren Funktionen wie VLOOKUP, HLOOKUP und LOOKUP bietet XLOOKUP mehrere wichtige Vorteile.

Nachricht nicht gefunden

Wenn XLOOKUP keine Übereinstimmung finden kann, wird wie bei anderen Übereinstimmungsfunktionen in Excel der Fehler # N / A zurückgegeben. Im Gegensatz zu den anderen Übereinstimmungsfunktionen unterstützt XLOOKUP ein optionales Argument namens not_found, mit dem der Fehler # N / A überschrieben werden kann, wenn er sonst auftreten würde. Typische Werte für not_found sind möglicherweise "Nicht gefunden", "Keine Übereinstimmung", "Kein Ergebnis" usw. Wenn Sie einen Wert für not_found angeben, schließen Sie den Text in doppelte Anführungszeichen ("") ein.

Hinweis: Seien Sie vorsichtig, wenn Sie eine leere Zeichenfolge ("") für not_found angeben. Wenn keine Übereinstimmung gefunden wird, zeigt XLOOKUP nichts anstelle von # N / A an. Wenn Sie den Fehler # N / A sehen möchten, wenn keine Übereinstimmung gefunden wird, lassen Sie das Argument vollständig weg.

Übereinstimmungstyp

Standardmäßig führt XLOOKUP eine genaue Übereinstimmung durch. Das Übereinstimmungsverhalten wird durch ein optionales Argument namens match_type gesteuert, das die folgenden Optionen bietet:

Übereinstimmungstyp	Verhalten
0 (Standard)	Genaue Übereinstimmung. Gibt # N / A zurück, wenn keine Übereinstimmung vorliegt.
-1	Genaue Übereinstimmung oder nächster kleinerer Gegenstand.
1	Genaue Übereinstimmung oder nächster größerer Gegenstand.
2	Platzhalterübereinstimmung (*,?, ~)

Suchmodus

Standardmäßig beginnt XLOOKUP mit dem Abgleich ab dem ersten Datenwert. Das Suchverhalten wird durch ein optionales Argument namens search_mode gesteuert , das die folgenden Optionen bietet:

Suchmodus	Verhalten
1 (Standard)	Suche vom ersten Wert
-1	Suche vom letzten Wert (umgekehrt)
2	Binäre Suchwerte in aufsteigender Reihenfolge sortiert
-2	Binäre Suchwerte in absteigender Reihenfolge sortiert

Binäre Suchen sind sehr schnell, aber die Daten müssen nach Bedarf sortiert werden. Wenn die Daten nicht richtig sortiert sind, kann eine binäre Suche ungültige Ergebnisse zurückgeben, die völlig normal aussehen.

Beispiel 1 - grundlegende genaue Übereinstimmung

Standardmäßig führt XLOOKUP eine genaue Übereinstimmung durch. Im folgenden Beispiel wird XLOOKUP verwendet, um Verkäufe basierend auf einer genauen Übereinstimmung mit Movie abzurufen. Die Formel in H5 lautet:

=XLOOKUP(H4,B5:B9,E5:E9)

Detailliertere Erklärung hier.

Beispiel 2 - grundlegende ungefähre Übereinstimmung

Geben Sie einen Wert für das Argument "match_mode" an, um eine ungefähre Übereinstimmung zu aktivieren. Im folgenden Beispiel wird XLOOKUP verwendet, um einen Rabatt basierend auf der Menge zu berechnen, für den eine ungefähre Übereinstimmung erforderlich ist. Die Formel in F5 liefert -1 für match_mode, um eine ungefähre Übereinstimmung mit dem Verhalten "exakte Übereinstimmung oder nächstkleinere" zu ermöglichen:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Detailliertere Erklärung hier.

Beispiel 3 - mehrere Werte

XLOOKUP kann für dieselbe Übereinstimmung mehr als einen Wert gleichzeitig zurückgeben. Das folgende Beispiel zeigt, wie XLOOKUP so konfiguriert werden kann, dass drei übereinstimmende Werte mit einer einzigen Formel zurückgegeben werden. Die Formel in C5 lautet:

=XLOOKUP(B5,B8:B15,C8:E15)

Beachten Sie, dass das Rückgabearray (C8: E15) drei Spalten enthält: First, Last, Department. Alle drei Werte werden zurückgegeben und liegen im Bereich C5: E5.

Beispiel 4 - Zwei-Wege-Suche

XLOOKUP kann verwendet werden, um eine bidirektionale Suche durchzuführen, indem ein XLOOKUP in einem anderen verschachtelt wird. Im folgenden Beispiel ruft das "innere" XLOOKUP eine ganze Zeile (alle Werte für Glass) ab, die als Rückgabearray an das "äußere" XLOOKUP übergeben wird. Das äußere XLOOKUP findet die entsprechende Gruppe (B) und gibt den entsprechenden Wert (17,25) als Endergebnis zurück.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Weitere Details hier.

Beispiel 5 - Nachricht nicht gefunden

Wenn XLOOKUP wie andere Suchfunktionen keinen Wert findet, wird der Fehler # N / A zurückgegeben. Geben Sie zum Anzeigen einer benutzerdefinierten Nachricht anstelle von # N / A einen Wert für das optionale Argument "nicht gefunden" an, das in doppelte Anführungszeichen ("") eingeschlossen ist. Verwenden Sie beispielsweise Folgendes, um "Nicht gefunden" anzuzeigen, wenn kein passender Film gefunden wird, basierend auf dem folgenden Arbeitsblatt.

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Sie können diese Nachricht nach Ihren Wünschen anpassen: "Keine Übereinstimmung", "Film nicht gefunden" usw.

Beispiel 6 - komplexe Kriterien

Mit der Fähigkeit, Arrays nativ zu behandeln, kann XLOOKUP mit komplexen Kriterien verwendet werden. Im folgenden Beispiel stimmt XLOOKUP mit dem ersten Datensatz überein, bei dem: Konto mit "x" beginnt und Region "Ost" ist und Monat nicht April:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Details: (1) einfaches Beispiel, (2) komplexeres Beispiel.

XLOOKUP Vorteile

XLOOKUP bietet einige wichtige Vorteile, insbesondere gegenüber VLOOKUP:

XLOOKUP kann Daten rechts oder links von Suchwerten suchen
XLOOKUP kann mehrere Ergebnisse zurückgeben (Beispiel 3 oben)
XLOOKUP entspricht standardmäßig einer exakten Übereinstimmung (VLOOKUP ist standardmäßig ungefähr)
XLOOKUP kann mit vertikalen und horizontalen Daten arbeiten
XLOOKUP kann eine umgekehrte Suche durchführen (letzte bis erste)
XLOOKUP kann ganze Zeilen oder Spalten zurückgeben, nicht nur einen Wert
XLOOKUP kann nativ mit Arrays arbeiten, um komplexe Kriterien anzuwenden

Anmerkungen

XLOOKUP kann sowohl mit vertikalen als auch mit horizontalen Arrays arbeiten.
XLOOKUP gibt # N / A zurück, wenn der Suchwert nicht gefunden wird.
Die Suchmatrix muss eine Dimension mit dem kompatiblen haben return_array Argument, sonst XLOOKUP kehrt #VALUE!
Wenn XLOOKUP zwischen Arbeitsmappen verwendet wird, müssen beide Arbeitsmappen geöffnet sein, andernfalls gibt XLOOKUP #REF! Zurück.
Wie die INDEX-Funktion gibt XLOOKUP als Ergebnis eine Referenz zurück.