Excels neues XLOOKUP ersetzt SVERWEIS und bietet einen leistungsstarken Ersatz für eine der beliebtesten Funktionen von Excel. Diese neue Funktion löst einige der Einschränkungen von SVERWEIS und bietet zusätzliche Funktionen. Hier ist, was Sie wissen müssen.
Inhaltsverzeichnis
Was ist XLOOKUP?
Die neue XLOOKUP-Funktion bietet Lösungen für einige der größten Einschränkungen von SVERWEIS. Außerdem ersetzt es auch HLOOKUP. XVERWEIS kann beispielsweise nach links schauen, verwendet standardmäßig eine exakte Übereinstimmung und ermöglicht es Ihnen, einen Zellbereich anstelle einer Spaltennummer anzugeben. SVERWEIS ist nicht so einfach zu bedienen oder so vielseitig. Wir zeigen Ihnen, wie das Ganze funktioniert.
XLOOKUP steht derzeit nur Nutzern des Insiders-Programms zur Verfügung. Jeder kann Nehmen Sie am Insider-Programm teil um auf die neuesten Excel-Funktionen zuzugreifen, sobald sie verfügbar sind. Microsoft wird in Kürze damit beginnen, es für alle Office 365-Benutzer bereitzustellen.
So verwenden Sie die XLOOKUP-Funktion
Lassen Sie uns direkt mit einem Beispiel für XLOOKUP in Aktion eintauchen. Nehmen Sie die Beispieldaten unten. Wir möchten die Abteilung aus Spalte F für jede ID in Spalte A zurückgeben.
Dies ist ein klassisches Beispiel für eine exakte Übereinstimmungssuche. Die XLOOKUP-Funktion benötigt nur drei Informationen.
Das Bild unten zeigt XLOOKUP mit sechs Argumenten, aber nur die ersten drei sind für eine genaue Übereinstimmung erforderlich. Konzentrieren wir uns also auf sie:
Lookup_value: Was Sie suchen.
Lookup_array: Wo gesucht werden soll.
Return_array: der Bereich, der den zurückzugebenden Wert enthält.
Die folgende Formel funktioniert für dieses Beispiel: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Lassen Sie uns nun einige Vorteile untersuchen, die XLOOKUP gegenüber SVERWEIS hat.
Keine Spaltenindexnummer mehr
Das berüchtigte dritte Argument von SVERWEIS bestand darin, die Spaltennummer der Informationen anzugeben, die von einem Tabellenarray zurückgegeben werden sollen. Dies ist kein Problem mehr, da Sie mit XLOOKUP den Bereich auswählen können, aus dem zurückgegeben werden soll (Spalte F in diesem Beispiel).
Und vergessen Sie nicht, XVERWEIS kann im Gegensatz zu SVERWEIS die Daten links von der ausgewählten Zelle anzeigen. Mehr dazu weiter unten.
Sie haben auch kein Problem mehr mit einer kaputten Formel, wenn neue Spalten eingefügt werden. Wenn dies in Ihrer Tabelle passiert, wird der Rückgabebereich automatisch angepasst.
Genaue Übereinstimmung ist die Standardeinstellung
Beim Erlernen von SVERWEIS war es immer verwirrend, warum man eine genaue Übereinstimmung angeben musste.
Glücklicherweise verwendet XLOOKUP standardmäßig eine exakte Übereinstimmung – der weitaus häufigere Grund für die Verwendung einer Nachschlageformel). Dies reduziert die Notwendigkeit, dieses fünfte Argument zu beantworten, und sorgt dafür, dass Benutzer, die mit der Formel noch nicht vertraut sind, weniger Fehler machen.
Kurz gesagt, XLOOKUP stellt weniger Fragen als VLOOKUP, ist benutzerfreundlicher und auch langlebiger.
XLOOKUP kann nach links schauen
Die Möglichkeit, einen Nachschlagebereich auszuwählen, macht XLOOKUP vielseitiger als SVERWEIS. Bei XLOOKUP spielt die Reihenfolge der Tabellenspalten keine Rolle.
SVERWEIS wurde eingeschränkt, indem die Spalte ganz links einer Tabelle durchsucht und dann von einer angegebenen Anzahl von Spalten nach rechts zurückgekehrt wurde.
Im folgenden Beispiel müssen wir eine ID suchen (Spalte E) und den Namen der Person (Spalte D) zurückgeben.
Die folgende Formel kann dies erreichen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Was tun, wenn nicht gefunden
Benutzer von Nachschlagefunktionen sind mit der #N/A-Fehlermeldung sehr vertraut, die sie begrüßt, wenn ihre SVERWEIS- oder MATCH-Funktion nicht finden kann, was sie braucht. Und das hat oft einen logischen Grund.
Daher recherchieren Benutzer schnell, wie dieser Fehler ausgeblendet werden kann, da er nicht korrekt oder nützlich ist. Und natürlich gibt es Möglichkeiten, dies zu tun.
XLOOKUP verfügt über ein eigenes eingebautes „Wenn nicht gefunden“-Argument, um solche Fehler zu behandeln. Sehen wir uns das im vorherigen Beispiel in Aktion an, jedoch mit einer falsch eingegebenen ID.
Die folgende Formel zeigt anstelle der Fehlermeldung den Text „Falsche ID“ an: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,“Falsche ID“)
Verwenden von XLOOKUP für eine Bereichssuche
Obwohl nicht so häufig wie die genaue Übereinstimmung, besteht eine sehr effektive Verwendung einer Nachschlageformel darin, nach einem Wert in Bereichen zu suchen. Nehmen Sie das folgende Beispiel. Wir möchten den Rabatt abhängig vom ausgegebenen Betrag zurückerstatten.
Diesmal suchen wir nicht nach einem bestimmten Wert. Wir müssen wissen, wo die Werte in Spalte B innerhalb der Bereiche in Spalte E liegen. Dadurch wird der verdiente Rabatt bestimmt.
XLOOKUP hat ein optionales fünftes Argument (denken Sie daran, es ist standardmäßig die genaue Übereinstimmung) namens Match-Modus.
Sie können sehen, dass XLOOKUP bei ungefähren Übereinstimmungen größere Fähigkeiten hat als SVERWEIS.
Es besteht die Möglichkeit, die nächste Übereinstimmung zu finden, die kleiner als (-1) oder am nächsten größer als (1) der gesuchte Wert ist. Es besteht auch die Möglichkeit, Platzhalterzeichen (2) wie das ? oder der *. Diese Einstellung ist nicht standardmäßig aktiviert, wie es bei SVERWEIS der Fall war.
Die Formel in diesem Beispiel gibt den nächsten Wert zurück, der kleiner als der gesuchte Wert ist, wenn keine genaue Übereinstimmung gefunden wird: =XVERWEIS(B2,$E$3:$E$7,$F$3:$F$7,-1)
Es gibt jedoch einen Fehler in Zelle C7, wo der #N/A-Fehler zurückgegeben wird (das Argument ‚wenn nicht gefunden‘ wurde nicht verwendet). Dies hätte einen Rabatt von 0 % ergeben sollen, da die Ausgabe von 64 die Kriterien für einen Rabatt nicht erfüllt.
Ein weiterer Vorteil der XVERWEIS-Funktion besteht darin, dass der Nachschlagebereich nicht wie SVERWEIS in aufsteigender Reihenfolge sein muss.
Geben Sie unten in der Nachschlagetabelle eine neue Zeile ein und öffnen Sie dann die Formel. Erweitern Sie den verwendeten Bereich durch Klicken und Ziehen der Ecken.
Die Formel korrigiert den Fehler sofort. Es ist kein Problem, die „0“ am unteren Ende des Bereichs zu haben.
Persönlich würde ich die Tabelle immer noch nach der Nachschlagespalte sortieren. Eine „0“ unten würde mich verrückt machen. Aber die Tatsache, dass die Formel nicht kaputt ging, ist brillant.
XLOOKUP ersetzt auch die HLOOKUP-Funktion
Wie bereits erwähnt, ersetzt die XLOOKUP-Funktion auch HLOOKUP. Eine Funktion, um zwei zu ersetzen. Exzellent!
Die HLOOKUP-Funktion ist die horizontale Suche, die zum Suchen entlang von Zeilen verwendet wird.
Nicht so bekannt wie sein Geschwister SVERWEIS, aber nützlich für Beispiele wie unten, wo sich die Überschriften in Spalte A befinden und sich die Daten in den Zeilen 4 und 5 befinden.
XLOOKUP kann in beide Richtungen schauen – nach unten in Spalten und auch entlang von Zeilen. Wir brauchen nicht mehr zwei verschiedene Funktionen.
In diesem Beispiel wird die Formel verwendet, um den Verkaufswert für den Namen in Zelle A2 zurückzugeben. Es sucht in Zeile 4 nach dem Namen und gibt den Wert aus Zeile 5 zurück: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kann von unten nach oben schauen
Normalerweise müssen Sie eine Liste durchsuchen, um das erste (oft einzige) Vorkommen eines Werts zu finden. XLOOKUP hat ein sechstes Argument namens search mode. Dies ermöglicht es uns, die Suche so zu ändern, dass sie unten beginnt und stattdessen eine Liste nach dem letzten Vorkommen eines Werts sucht.
Im folgenden Beispiel möchten wir den Lagerbestand für jedes Produkt in Spalte A ermitteln.
Die Nachschlagetabelle ist nach Datum sortiert, und es gibt mehrere Bestandsprüfungen pro Produkt. Wir möchten den Lagerbestand von der letzten Überprüfung (letztes Vorkommen der Produkt-ID) zurückgeben.
Das sechste Argument der XLOOKUP-Funktion bietet vier Optionen. Wir sind daran interessiert, die Option „Suche von der letzten zur ersten“ zu verwenden.
Die ausgefüllte Formel wird hier angezeigt: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
In dieser Formel wurden das vierte und fünfte Argument ignoriert. Es ist optional, und wir wollten die Standardeinstellung einer genauen Übereinstimmung.
Zusammenfassen
Die XLOOKUP-Funktion ist die mit Spannung erwarteter Nachfolger sowohl auf die SVERWEIS- als auch auf die HVERWEIS-Funktionen.
In diesem Artikel wurden verschiedene Beispiele verwendet, um die Vorteile von XLOOKUP zu demonstrieren. Eine davon ist, dass XLOOKUP blatt-, arbeitsmappen- und auch tabellenübergreifend verwendet werden kann. Die Beispiele wurden im Artikel einfach gehalten, um unser Verständnis zu erleichtern.
Wegen Einführung dynamischer Arrays in Excel bald kann es auch einen Wertebereich zurückgeben. Dies ist definitiv etwas, das es wert ist, weiter untersucht zu werden.
Die Tage von SVERWEIS sind gezählt. XLOOKUP ist da und wird bald die De-facto-Suchformel sein.