XLOOKUP-Funktion
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.
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 |
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.
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 3 fügt dem vorherigen Beispiel ein if_not_found- Argument hinzu.
———————————————————————————
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.
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.
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.
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.
———————————————————————————
Benötigen Sie weitere Hilfe?
Siehe auch
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
No comments:
Post a Comment