Excel

Excel XLOOKUP-Funktion

Excel Xlookup Function

Excel XLOOKUP-FunktionZusammenfassung

Die Excel-XVERWEIS-Funktion ist ein moderner und flexibler Ersatz für ältere Funktionen wie SVERWEIS, HVERWEIS und VERWEIS. XLOOKUP unterstützt ungefähre und genaue Übereinstimmungen, Platzhalter (* ?) für Teilübereinstimmungen und Nachschlagen in vertikalen oder horizontalen Bereichen.



Zweck Werte im Bereich oder Array suchen Rückgabewert Passende(r) Wert(e) aus Rückgabearray Syntax =XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) Argumente
  • Schau hoch - Der Nachschlagewert.
  • lookup_array - Das zu durchsuchende Array oder der Bereich.
  • return_array - Das Array oder der Bereich, der zurückgegeben werden soll.
  • nicht gefunden - [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, 1 = genaue Übereinstimmung oder nächstgrößere, 2 = Wildcard-Ü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 Nutzungshinweise

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

XLOOKUP kann Werte in vertikalen oder horizontalen Bereichen finden, kann ungefähre und genaue Übereinstimmungen durchführen und unterstützt Platzhalter (* ?) für Teilübereinstimmungen. 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 SVERWEIS , HVERWEIS , und SCHAU HOCH , XLOOKUP-Angebote mehrere entscheidende Vorteile .





Nachricht nicht gefunden

Wenn XLOOKUP keine Übereinstimmung finden kann, wird der Fehler #N/A zurückgegeben, wie bei anderen Übereinstimmungsfunktionen in Excel. Im Gegensatz zu den anderen Match-Funktionen unterstützt XLOOKUP ein optionales Argument namens nicht gefunden die verwendet werden kann, um den #N/A-Fehler zu überschreiben, wenn er sonst erscheinen würde. Typische Werte für nicht gefunden könnte 'Nicht gefunden', 'Keine Übereinstimmung', 'Kein Ergebnis' usw. lauten. Bei der Angabe eines Wertes für nicht gefunden , schließen Sie den Text in doppelte Anführungszeichen ('') ein.

Hinweis: Seien Sie vorsichtig, wenn Sie für not_found eine leere Zeichenfolge ('') 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 . Das Match-Verhalten wird durch ein optionales Argument namens . gesteuert match_type, die folgende Optionen hat:

Zeitstempel in Excel auf Datum konvertieren
Übereinstimmungstyp Verhalten
0 (Standard) Genaue Übereinstimmung. Gibt #N/A zurück, wenn keine Übereinstimmung vorliegt.
-1 Genaue Übereinstimmung oder nächstkleinerer Artikel.
1 Genaue Übereinstimmung oder nächstgrößerer Artikel.
2 Wildcard-Match (*,?, ~)

Suchmodus

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

Suchmodus Verhalten
1 (Standard) Suche ab dem ersten Wert
-1 Suche ab letztem Wert (rückwärts)
2 Binäre Suchwerte aufsteigend sortiert
-2 Binäre Suchwerte absteigend sortiert

Binäre Suchen sind sehr schnell, aber 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 im Film abzurufen. Die Formel in H5 lautet:

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

XLOOKUP - einfaches Beispiel für eine exakte Übereinstimmung

Genauere Erklärung hier .

Beispiel #2 – grundlegende ungefähre Übereinstimmung

Um eine ungefähre Übereinstimmung zu ermöglichen, geben Sie einen Wert für das Argument 'match_mode' an. Im folgenden Beispiel wird XLOOKUP verwendet, um einen Rabatt basierend auf der Menge zu berechnen, was eine ungefähre Übereinstimmung erfordert. Die Formel in F5 liefert -1 für match_mode, um eine ungefähre Übereinstimmung mit dem Verhalten 'genaue Übereinstimmung oder nächstkleinere' zu ermöglichen:

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

XLOOKUP - einfaches ungefähres Übereinstimmungsbeispiel

Genauere Erklärung hier .

Beispiel #3 - mehrere Werte

XLOOKUP kann mehr als einen Wert gleichzeitig für dieselbe Übereinstimmung 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)

XLOOKUP - Beispiel mit mehreren Werten

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

Beispiel #4 – Zwei-Wege-Suche

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

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

XLOOKUP - Beispiel für eine bidirektionale Suche

Weitere Details hier .

Beispiel #5 - Nachricht nicht gefunden

Wenn XLOOKUP keinen Wert findet, gibt es wie andere Lookup-Funktionen den Fehler #N/A zurück. Um eine benutzerdefinierte Nachricht anstelle von #N/A anzuzeigen, geben Sie einen Wert für das optionale Argument 'nicht gefunden' ein, eingeschlossen in doppelte Anführungszeichen (''). Um beispielsweise 'Nicht gefunden' anzuzeigen, wenn kein passender Film gefunden wird, verwenden Sie basierend auf dem folgenden Arbeitsblatt:

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

XLOOKUP - Beispiel nicht gefunden

Sie können diese Nachricht nach Belieben anpassen: 'Keine Übereinstimmung', 'Film nicht gefunden' usw.

Wie addiere ich Spalten in Excel?

Beispiel #6 – komplexe Kriterien

Mit der Fähigkeit, Arrays nativ zu handhaben, kann XLOOKUP mit komplexen Kriterien verwendet werden. Im folgenden Beispiel stimmt XLOOKUP mit dem ersten Datensatz überein, wobei: account beginnt mit 'x' und Region ist 'Osten' und Monat ist nicht April:

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

XLOOKUP - Beispiel für komplexe Kriterien

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

XLOOKUP-Vorteile

XLOOKUP bietet einige wichtige Vorteile, insbesondere im Vergleich zu SVERWEIS:

  • XLOOKUP kann Daten rechts nachschlagen oder links von Nachschlagewerten
  • XLOOKUP kann mehrere Ergebnisse zurückgeben (Beispiel Nr. 3 oben)
  • XLOOKUP ist standardmäßig auf eine genaue Übereinstimmung eingestellt (SVERWEIS ist standardmäßig auf ungefähr)
  • XLOOKUP kann mit vertikalen und horizontalen Daten arbeiten
  • XLOOKUP kann eine Rückwärtssuche durchführen (vom letzten zum ersten)
  • XLOOKUP kann ganze zurückgeben Zeilen oder Spalten , nicht nur ein Wert
  • XLOOKUP kann mit arbeiten Arrays nativ komplexe Kriterien anwenden

Anmerkungen

  1. XLOOKUP kann sowohl mit vertikalen als auch mit horizontalen Arrays arbeiten.
  2. XLOOKUP gibt #N/A zurück, wenn der Nachschlagewert nicht gefunden wird.
  3. Die lookup_array muss eine Dimension haben, die mit dem kompatibel ist return_array -Argument, andernfalls gibt XLOOKUP #VALUE zurück!
  4. Wenn XLOOKUP zwischen Arbeitsmappen verwendet wird, müssen beide Arbeitsmappen geöffnet sein, andernfalls gibt XLOOKUP #REF! zurück.
  5. Wie INDEX-Funktion , XVERWEIS gibt a . zurück Hinweis als Ergebnis.
XLOOKUP ist eine neue Funktion in Excel 365 nur.


^