Pc Support: XLOOKUP-Funktion - Microsoft Support

Verwenden Sie die XLOOKUP -Funktion, um Dinge in einer Tabelle oder einem Bereich zeilenweise zu finden. Schlagen Sie beispielsweise den Preis eines Autoteils anhand der Teilenummer nach oder finden Sie den Namen eines Mitarbeiters anhand seiner Mitarbeiter-ID. Mit XLOOKUP können Sie in einer Spalte nach einem Suchbegriff suchen und ein Ergebnis aus derselben Zeile in einer anderen Spalte zurückgeben, unabhängig davon, auf welcher Seite sich die Rückgabespalte befindet.

Hinweis: XVERWEIS ist in Excel 2016 und Excel 2019 nicht verfügbar, es kann jedoch vorkommen, dass Sie eine Arbeitsmappe in Excel 2016 oder Excel 2019 mit der darin enthaltenen XVERWEIS-Funktion verwenden, die von einer anderen Person mit einer neueren Version von Excel erstellt wurde.

Ihr Browser unterstützt kein Video. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.

Syntax

Die XLOOKUP-Funktion durchsucht einen Bereich oder ein Array und gibt dann das Element zurück, das der ersten gefundenen Übereinstimmung entspricht. Wenn keine Übereinstimmung vorhanden ist, kann XLOOKUP die nächste (ungefähre) Übereinstimmung zurückgeben.

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

Streit	Beschreibung
Lookup-Wert Erforderlich*	Der zu suchende Wert Wenn weggelassen, gibt XLOOKUP leere Zellen zurück, die es in lookup_array* findet.
lookup_array Erforderlich	Das zu durchsuchende Array oder der Bereich
return_array Erforderlich	Das zurückzugebende Array oder der Bereich
[wenn_nicht_gefunden] Optional	Wenn keine gültige Übereinstimmung gefunden wird, geben Sie den von Ihnen bereitgestellten [if_not_found]-Text zurück. Wenn keine gültige Übereinstimmung gefunden wird und [if_not_found] fehlt, wird #N/A zurückgegeben.
[match_mode] Optional	Geben Sie den Übereinstimmungstyp an: 0 - Genaue Übereinstimmung. Wenn nichts gefunden wird, #N/A zurückgeben. Dies ist die Standardeinstellung. -1 - Genaue Übereinstimmung. Wenn keiner gefunden wird, geben Sie den nächstkleineren Artikel zurück. 1 - Genaue Übereinstimmung. Wenn keiner gefunden wird, senden Sie den nächstgrößeren Artikel zurück. 2 - Ein Wildcard-Match, bei dem *, ? und ~ eine besondere Bedeutung haben .
[Suchmodus] Optional	Geben Sie den zu verwendenden Suchmodus an: 1 - Führen Sie eine Suche beginnend mit dem ersten Element durch. Dies ist die Standardeinstellung. -1 - Führt eine Rückwärtssuche beginnend mit dem letzten Element durch. 2 – Führen Sie eine binäre Suche durch, die darauf beruht, dass lookup_array in aufsteigender Reihenfolge sortiert wird. Wenn nicht sortiert, werden ungültige Ergebnisse zurückgegeben. -2 - Führt eine binäre Suche durch, die darauf beruht, dass lookup_array in absteigender Reihenfolge sortiert wird. Wenn nicht sortiert, werden ungültige Ergebnisse zurückgegeben.

Streit

Beschreibung

Lookup-Wert

Erforderlich*

Der zu suchende Wert

*Wenn weggelassen, gibt XLOOKUP leere Zellen zurück, die es in lookup_array findet.

lookup_array

Erforderlich

Das zu durchsuchende Array oder der Bereich

return_array

Erforderlich

Das zurückzugebende Array oder der Bereich

[wenn_nicht_gefunden]

Optional

Wenn keine gültige Übereinstimmung gefunden wird, geben Sie den von Ihnen bereitgestellten [if_not_found]-Text zurück.

Wenn keine gültige Übereinstimmung gefunden wird und [if_not_found] fehlt, wird #N/A zurückgegeben.

[match_mode]

Optional

Geben Sie den Übereinstimmungstyp an:

0 - Genaue Übereinstimmung. Wenn nichts gefunden wird, #N/A zurückgeben. Dies ist die Standardeinstellung.

-1 - Genaue Übereinstimmung. Wenn keiner gefunden wird, geben Sie den nächstkleineren Artikel zurück.

1 - Genaue Übereinstimmung. Wenn keiner gefunden wird, senden Sie den nächstgrößeren Artikel zurück.

2 - Ein Wildcard-Match, bei dem *, ? und ~ eine besondere Bedeutung haben .

[Suchmodus]

Optional

Geben Sie den zu verwendenden Suchmodus an:

1 - Führen Sie eine Suche beginnend mit dem ersten Element durch. Dies ist die Standardeinstellung.

-1 - Führt eine Rückwärtssuche beginnend mit dem letzten Element durch.

2 – Führen Sie eine binäre Suche durch, die darauf beruht, dass lookup_array in aufsteigender Reihenfolge sortiert wird. Wenn nicht sortiert, werden ungültige Ergebnisse zurückgegeben.

-2 - Führt eine binäre Suche durch, die darauf beruht, dass lookup_array in absteigender Reihenfolge sortiert wird. Wenn nicht sortiert, werden ungültige Ergebnisse zurückgegeben.

Beispiele

Beispiel 1 verwendet XLOOKUP, um einen Ländernamen in einem Bereich zu suchen und dann seine Telefon-Ländervorwahl zurückzugeben. Es enthält die Argumente lookup_value (Zelle F2), lookup_array (Bereich B2:B11) und return_array (Bereich D2:D11). Es enthält nicht das Argument match_mode , da XLOOKUP standardmäßig eine exakte Übereinstimmung erzeugt.

Beispiel für die XLOOKUP-Funktion, die verwendet wird, um einen Mitarbeiternamen und eine Abteilung basierend auf der Mitarbeiter-ID zurückzugeben. Die Formel lautet =XVERWEIS(B2,B5:B14,C5:C14).

Hinweis: XLOOKUP verwendet ein Sucharray und ein Rückgabearray, während SVERWEIS ein einzelnes Tabellenarray gefolgt von einer Spaltenindexnummer verwendet. Die äquivalente SVERWEIS-Formel in diesem Fall wäre: =SVERWEIS(F2,B2:D11,3,FALSCH)

———————————————————————————

Beispiel 2 sucht Mitarbeiterinformationen basierend auf einer Mitarbeiter-ID-Nummer. Im Gegensatz zu SVERWEIS kann XVERWEIS ein Array mit mehreren Elementen zurückgeben, sodass eine einzelne Formel sowohl den Mitarbeiternamen als auch die Abteilung aus den Zellen C5:D14 zurückgeben kann.

Beispiel für die XLOOKUP-Funktion, die verwendet wird, um einen Mitarbeiternamen und eine Abteilung basierend auf der Mitarbeiter-IDt zurückzugeben. Die Formel lautet: =XVERWEIS(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Beispiel 3 fügt dem vorherigen Beispiel ein if_not_found- Argument hinzu.

Beispiel für die XLOOKUP-Funktion, die verwendet wird, um einen Mitarbeiternamen und eine Abteilung basierend auf der Mitarbeiter-ID mit dem Argument if_not_found zurückzugeben. Die Formel lautet =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Mitarbeiter nicht gefunden")

———————————————————————————

Beispiel 4 sucht in Spalte C nach dem in Zelle E2 eingegebenen persönlichen Einkommen und findet einen passenden Steuersatz in Spalte B. Es setzt das if_not_found- Argument so, dass es 0 (Null) zurückgibt, wenn nichts gefunden wird. Das Argument match_mode ist auf 1 gesetzt, was bedeutet, dass die Funktion nach einer genauen Übereinstimmung sucht und, wenn sie keine finden kann, das nächstgrößere Element zurückgibt. Schließlich wird das Argument search_mode auf 1 gesetzt, was bedeutet, dass die Funktion vom ersten bis zum letzten Element sucht.

Bild der XLOOKUP-Funktion, die verwendet wird, um einen Steuersatz basierend auf dem maximalen Einkommen zurückzugeben. Dies ist eine ungefähre Übereinstimmung. Die Formel lautet: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Hinweis: Die lookup_array- Spalte von XARRAY befindet sich rechts von der return_array- Spalte, während VLOOKUP nur von links nach rechts suchen kann.

———————————————————————————

Beispiel 5 verwendet eine verschachtelte XLOOKUP-Funktion, um sowohl einen vertikalen als auch einen horizontalen Abgleich durchzuführen. Es sucht zuerst in Spalte B nach Bruttogewinn , dann in der obersten Zeile der Tabelle (Bereich C5:F5) nach Qtr1 und gibt schließlich den Wert am Schnittpunkt der beiden zurück. Dies ähnelt der gemeinsamen Verwendung der Funktionen INDEX und MATCH .

Tipp: Sie können auch XLOOKUP verwenden, um die HLOOKUP- Funktion zu ersetzen.

Bild der XLOOKUP-Funktion, die verwendet wird, um horizontale Daten aus einer Tabelle durch Verschachteln von 2 XLOOKUPs zurückzugeben. Die Formel lautet: =XVERWEIS(D2,$B6:$B17,XVERWEIS($C3,$C5:$G5,$C6:$G17))

Hinweis: Die Formel in den Zellen D3:F3 lautet: =XVERWEIS(D2,$B6:$B17,XVERWEIS($C3,$C5:$G5,$C6:$G17)) .

———————————————————————————

Beispiel 6 verwendet die SUM-Funktion und zwei verschachtelte XLOOKUP-Funktionen, um alle Werte zwischen zwei Bereichen zu summieren. In diesem Fall möchten wir die Werte für Trauben, Bananen und Birnen, die zwischen den beiden liegen, summieren.

Verwenden von XLOOKUP mit SUM zum Summieren eines Bereichs von Werten, die zwischen zwei Auswahlen fallen

Die Formel in Zelle E3 lautet: =SUMME(XVERWEIS(B3,B6:B10,E6:E10):XVERWEIS(C3,B6:B10,E6:E10))

Wie funktioniert es? XLOOKUP gibt einen Bereich zurück, sodass die Formel bei der Berechnung so aussieht: =SUM($E$7:$E$9) . Sie können selbst sehen, wie dies funktioniert, indem Sie eine Zelle mit einer ähnlichen XLOOKUP-Formel wie dieser auswählen, dann Formeln > Formelüberwachung > Formel auswerten und dann Auswerten auswählen, um die Berechnung schrittweise zu durchlaufen.

Hinweis: Vielen Dank an Microsoft Excel MVP, Bill Jelen , für den Vorschlag zu diesem Beispiel.

———————————————————————————