Die neue XLOOKUP-Funktion wird ab November 2019 in Office 365 eingeführt. Joe McDaid vom Excel-Team hat XLOOKUP entwickelt, um die Benutzer von VLOOKUP und die Benutzer von INDEX / MATCH zu vereinen. In diesem Abschnitt werden die 12 Vorteile von XLOOKUP erläutert:
- Genaue Übereinstimmung ist die Standardeinstellung.
- Das ganzzahlige dritte Argument von VLOOKUP ist jetzt eine richtige Referenz.
- IFNA ist integriert, um fehlende Werte zu behandeln.
- XLOOKUP hat kein Problem, nach links zu gehen.
- Finden Sie die nächst kleinere oder nächst größere Übereinstimmung, ohne die Tabelle zu sortieren.
- XLOOKUP kann HLOOKUP ausführen.
- Finden Sie die letzte Übereinstimmung, indem Sie von unten suchen.
- Platzhalter sind standardmäßig deaktiviert, Sie können sie jedoch wieder aktivieren.
- Geben Sie alle 12 Monate in einer einzigen Formel zurück.
- Kann eine Zellreferenz zurückgeben, wenn sich das XLOOKUP neben einem Doppelpunkt wie XLOOKUP (); XLOOKUP () befindet.
- Kann ein Zwei-Wege-Match wie INDEX (, MATCH, MATCH) durchführen.
- Kann alle Lookups in einer einzigen Formel zusammenfassen, wie es LOOKUP tun könnte.
Hier ist die Syntax: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).
XLOOKUP Vorteil 1: Standardmäßig genaues Match
99% meiner VLOOKUP-Formeln enden mit, FALSE oder, 0, um eine genaue Übereinstimmung anzuzeigen. Wenn Sie immer die exakte Übereinstimmungsversion von VLOOKUP verwenden, können Sie den match_mode für Ihre XLOOKUP-Funktion deaktivieren.
In der folgenden Abbildung sehen Sie W25-6 in Zelle A4. Sie möchten diesen Artikel in L8: L35 suchen. Wenn es gefunden wird, möchten Sie den entsprechenden Preis aus Spalte N haben. Es ist nicht erforderlich, False als match_mode anzugeben, da XLOOKUP standardmäßig eine genaue Übereinstimmung verwendet.
XLOOKUP-Vorteil 2: Das Results_Array ist eine Referenz anstelle einer Ganzzahl
Denken Sie an die VLOOKUP-Formel, die Sie vor XLOOKUP verwenden würden. Das dritte Argument wäre eine 3 gewesen, um anzuzeigen, dass Sie die dritte Spalte zurückgeben möchten. Es bestand immer die Gefahr, dass ein ahnungsloser Mitarbeiter eine Spalte in Ihre Tabelle eingefügt (oder gelöscht) hätte. Mit einer zusätzlichen Spalte in der Tabelle würde der VLOOKUP, der einen Preis zurückgegeben hat, eine Beschreibung zurückgeben. Da XLOOKUP auf eine Zellreferenz zeigte, schreibt sich die Formel neu, um weiterhin auf den Preis in Spalte O zu verweisen.
XLOOKUP Vorteil 3: IFNA ist als optionales Argument integriert
Der gefürchtete Fehler # N / A wird zurückgegeben, wenn Ihr Suchwert nicht in der Tabelle gefunden wird. In der Vergangenheit mussten Sie IFERROR oder IFNA verwenden, um das VLOOKUP zu wickeln, um # N / A durch etwas anderes zu ersetzen.
Dank eines Vorschlags von Rico auf meinem YouTube-Kanal hat das Excel-Team ein optionales viertes Argument für if_not_found eingefügt. Wenn Sie diese # N / A-Fehler durch Null ersetzen möchten, fügen Sie einfach 0 als viertes Argument hinzu. Sie können auch Text verwenden, z. B. "Wert nicht gefunden".
XLOOKUP Vorteil 4: Kein Problem, wenn Sie links neben dem Schlüsselfeld nachsehen
VLOOKUP kann nicht links vom Schlüsselfeld schauen, ohne auf VLOOKUP zurückzugreifen (A4, WÄHLEN ((1,2), G7: G34, F7: F34), 2, Falsch). Mit XLOOKUP ist es kein Problem, das Results_array links vom Lookup_array zu haben.
XLOOKUP Vorteil 5: Nächste kleinere oder nächste größere Übereinstimmung ohne Sortierung
VLOOKUP hatte die Option, nach der genauen Übereinstimmung oder nur nach einem kleineren Wert zu suchen. Sie können entweder das vierte Argument in VLOOKUP weglassen oder False in True ändern. Damit dies funktioniert, musste die Nachschlagetabelle in aufsteigender Reihenfolge sortiert werden.
VLOOKUP war jedoch nicht in der Lage, die genaue Übereinstimmung oder den nächstgrößeren Gegenstand zurückzugeben. Dazu musste man auf MATCH mit -1 als match_mode umstellen und darauf achten, dass die Nachschlagetabelle absteigend sortiert war.
Das optionale fünfte Argument von XLOOKUP, match_mode, kann nur nach der genauen Übereinstimmung suchen, die gleich oder nur kleiner, gleich oder nur größer ist. Beachten Sie, dass die Werte in XLOOKUP sinnvoller sind als in MATCH:
- -1 findet den Wert gleich oder nur kleiner
- 0 finde eine genaue Übereinstimmung
- 1 findet den Wert gleich oder nur größer.
Das Erstaunlichste ist jedoch, dass die Nachschlagetabelle nicht sortiert werden muss und jeder match_mode funktioniert.
Unten findet ein match_mode von -1 das nächst kleinere Element.
Hier findet ein match_mode von 1, welches Fahrzeug abhängig von der Anzahl der Personen in der Gruppe benötigt wird. Beachten Sie, dass die Nachschlagetabelle nicht nach Passagieren sortiert ist und sich der Fahrzeugname links vom Schlüssel befindet.
Auf dem Tisch steht:
- Bus hält 64 Personen
- Auto hält 4 Personen
- Motorrad hält 1 Person
- Tour Van fasst 12 Personen
- Van halten 6 Personen.
Als Bonus werden die Daten nach Fahrzeug sortiert (in der alten Lösung müsste die Tabelle mit MATCH absteigend nach Kapazität sortiert werden. Außerdem: Das Fahrzeug befindet sich links von Kapazität.
XLOOKUP Vorteil 6: Seitwärts XLOOKUP ersetzt HLOOKUP
Das lookup_array und das results_array können mit XLOOKUP horizontal sein, was das Ersetzen von HLOOKUP vereinfacht.
XLOOKUP Vorteil 7: Suchen Sie von unten nach dem neuesten Spiel
Ich habe ein altes Video auf YouTube, das eine Frage von einer britischen Pferdefarm beantwortet. Sie hatten eine Flotte von Fahrzeugen. Jedes Mal, wenn ein Fahrzeug für Kraftstoff oder Service eintraf, wurden Fahrzeug, Datum und Kilometerstand in einer Tabelle aufgezeichnet. Sie wollten für jedes Fahrzeug den neuesten bekannten Kilometerstand ermitteln. Während die Excel-2017-Ära MAXIFS dies heute lösen könnte, war die Lösung vor vielen Jahren eine arkane Formel unter Verwendung von LOOKUP und beinhaltete die Division durch Null.
Mit dem optionalen sechsten Argument von XLOOKUP können Sie heute festlegen, dass die Suche am unteren Rand des Datensatzes beginnen soll.
Hinweis
Dies ist zwar eine große Verbesserung, aber Sie können nur das erste oder letzte Spiel finden. Einige Leute hofften, dass Sie damit die zweite oder dritte Übereinstimmung finden könnten, aber das ist nicht die Absicht des Arguments search_mode.
Vorsicht
Die obige Abbildung zeigt, dass es Suchmodi gibt, die die alte binäre Suche verwenden. Joe McDaid rät davon ab. Erstens ist der verbesserte Suchalgorithmus von 2018 schnell genug, dass es keinen signifikanten Geschwindigkeitsvorteil gibt. Zweitens laufen Sie Gefahr, dass ein ahnungsloser Mitarbeiter die Nachschlagetabelle sortiert und falsche Antworten einführt.
XLOOKUP Vorteil 8: Platzhalter sind standardmäßig "deaktiviert"
Den meisten Menschen war nicht klar, dass VLOOKUP Sternchen, Fragezeichen und Tilde als Platzhalterzeichen behandelt, wie unter "Verwenden eines Platzhalters in VLOOKUP" auf Seite 143 beschrieben. Bei XLOOKUP sind Platzhalter standardmäßig deaktiviert. Wenn XLOOKUP diese Zeichen als Platzhalter behandeln soll, verwenden Sie 2 als Match_Mode.
XLOOKUP-Vorteil 9: Geben Sie alle 12 Monate in einer einzigen Formel zurück!
Dies ist wirklich ein Vorteil von Dynamic Arrays, aber es ist mein Lieblingsgrund, XLOOKUP zu lieben. Wenn Sie alle 12 Monate in einer Suche zurückgeben müssen, gibt eine einzelne in B6 eingegebene Formel mit einem rechteckigen return_array mehrere Ergebnisse zurück. Diese Ergebnisse werden in benachbarte Zellen übertragen.
In der folgenden Abbildung gibt eine einzelne in B7 eingegebene Formel alle 12 in B7: M7 gezeigten Antworten zurück.
XLOOKUP-Vorteil 10: Kann eine Zellreferenz zurückgeben, wenn sie an Colon angrenzt
Dieser ist komplex, aber wunderschön. In der Vergangenheit gab es sieben Funktionen, die sich von der Rückgabe eines Zellenwerts zur Rückgabe einer Zellreferenz änderten, wenn die Funktion einen Doppelpunkt berührte. Ein Beispiel finden Sie unter Verwenden von A2: INDEX () als nichtflüchtiges OFFSET. XLOOKUP ist die Achterfunktion, die dieses Verhalten bietet und CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET und SWITCH verbindet.
Betrachten Sie die folgende Abbildung. Jemand wählt Cherry in E4 und Fig in E5. Sie möchten eine Formel, die alles von B6 bis B9 summiert.
In der obigen Abbildung sehen Sie, dass ein XLOOKUP von E4 die 15 aus Zelle B6 zurückgibt. Ein XLOOKUP von E5 gibt die 30 von B9 zurück. Wenn Sie jedoch die beiden XLOOKUP-Funktionen aus den Zellen D9 und D10 übernehmen und sie mit einem Doppelpunkt dazwischen zusammenfügen, ändert sich das Verhalten von XLOOKUP. Anstatt 15 zurückzugeben, gibt der erste XLOOKUP die Zellenadresse B6 zurück!
Um dies zu beweisen, habe ich D7 ausgewählt und Formeln, Formel auswerten verwendet. Nach zweimaligem Drücken von Auswerten ist der nächste zu berechnende Teil XLOOKUP ("Cherry", A4: A29, B4: B29), wie hier gezeigt.
Drücken Sie erneut auf Auswerten. Erstaunlicherweise gibt die XLOOKUP-Formel $ B $ 6 anstelle der in B6 gespeicherten 15 zurück. Dies geschieht, weil unmittelbar nach dieser XLOOKUP-Formel ein Doppelpunkt folgt.
Drücken Sie noch zweimal Auswerten, und die Zwischenformel lautet = SUMME (B6: B9).
Dies ist ein erstaunliches Verhalten, von dem die meisten Menschen nichts wissen. Excel MVP Charles Williams sagt mir, dass es mit jedem dieser drei Operatoren neben XLOOKUP ausgelöst werden kann:
- Doppelpunkt
- Leerzeichen (Kreuzungsoperator)
- Komma (Union Operator)
XLOOKUP Vorteil 11: Zwei-Wege-Match wie INDEX (, MATCH, MATCH)
Für alle meine VLOOKUP-Freunde haben die INDEX / MATCH-Leute darauf gewartet, ob XLOOKUP ein Zwei-Wege-Match bewältigen kann. Die gute Nachricht: Es kann es tun. Die schlechte Nachricht: Die Methodik unterscheidet sich ein wenig von den INDEX / MATCH-Fans. Es könnte ein wenig über ihren Köpfen sein. Aber ich bin sicher, dass sie zu dieser Methode kommen können.
Für eine bidirektionale Übereinstimmung möchten Sie herausfinden, welche Zeile die in J3 angegebene Kontonummer A621 enthält. Das XLOOKUP beginnt also ganz einfach: = XLOOKUP (J3, A5: A15. Dann müssen Sie jedoch ein results_array bereitstellen. Sie können den gleichen Trick wie in XLOOKUP-Vorteil 9 anwenden: Alle 12 Monate in einer einzigen Formel oben zurückgeben, aber Verwenden Sie diese Option, um einen vertikalen Vektor zurückzugeben. Ein inneres XLOOKUP sucht in den Monatsüberschriften in B4: G4 nach dem J4-Monat. Das return_array wird als B5: G15 angegeben. Das Ergebnis ist, dass das innere XLOOKUP ein Array wie das in I10 gezeigte zurückgibt : I20 unten. Da A621 in der fünften Zelle des lookup_array und 104 in der fünften Zelle des results_array gefunden wird, erhalten Sie die richtige Antwort aus der Formel. Unten zeigt J6 den alten Weg. J7 gibt den neuen Weg zurück.
XLOOKUP-Vorteil 12: Summieren Sie alle Suchwerte in einer einzigen Formel
Die alte LOOKUP-Funktion bot zwei seltsame Tricks. Wenn Sie versuchen, den Gesamtbetrag der anfallenden Bonuskosten zu ermitteln, können Sie LOOKUP zunächst bitten, alle Werte in einer einzigen Formel nachzuschlagen. In der Abbildung unten führt LOOKUP (C4: C14 11 Suchvorgänge durch. Die LOOKUP-Funktion bot jedoch keine genaue Übereinstimmung und erforderte eine Sortierung der Nachschlagetabelle.
Mit XLOOKUP können Sie einen Bereich angeben, da lookup_value und XLOOKUP alle Antworten zurückgeben. Der Vorteil ist, dass XLOOKUP exakte Übereinstimmungen durchführen kann.
Bonus-Tipp: Was ist mit einem Twisted LOOKUP?
Excel MVP Mike Girvin zeigt häufig einen Trick der LOOKUP-Funktion, bei dem der Lookup_Vector vertikal und der Result_Vector horizontal ist. XLOOKUP unterstützt diesen Trick nicht von Haus aus. Wenn Sie jedoch ein wenig schummeln und das results_array in die TRANSPOSE-Funktion einbinden, können Sie eine verdrehte Suche verwalten.
