Verwendung der XLOOKUP-Funktion in Microsoft Excel

Das neue XLOOKUP von Excel ersetzt VLOOKUP und bietet einen leistungsstarken Ersatz für eine der beliebtesten Funktionen von Excel. Diese neue Funktion löst einige der Einschränkungen von VLOOKUP und verfügt über zusätzliche Funktionen. Folgendes müssen Sie wissen.

Was ist XLOOKUP?

Die neue XLOOKUP-Funktion bietet Lösungen für einige der größten Einschränkungen von VLOOKUP. Außerdem ersetzt es HLOOKUP. Beispielsweise kann XLOOKUP nach links schauen, standardmäßig eine genaue Übereinstimmung festlegen und anstelle einer Spaltennummer einen Zellenbereich angeben. VLOOKUP ist nicht so einfach zu bedienen oder so vielseitig. Wir zeigen Ihnen, wie das alles funktioniert.

Derzeit steht XLOOKUP nur Benutzern des Insider-Programms zur Verfügung. Jeder kann Treten Sie dem Insider-Programm bei 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.

Verwendung der XLOOKUP-Funktion

Lassen Sie uns gleich mit einem Beispiel von XLOOKUP in Aktion eintauchen. Nehmen Sie die folgenden Beispieldaten. Wir möchten die Abteilung aus Spalte F für jede ID in Spalte A zurückgeben.

Dies ist ein klassisches Beispiel für die Suche nach exakten Übereinstimmungen. 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-Wert: Wonach suchen Sie.
• Lookup_array: Wo soll man suchen?
• 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 VLOOKUP hat.

Keine Spaltenindexnummer mehr

Das berüchtigte dritte Argument von VLOOKUP war die Angabe der Spaltennummer der Informationen, 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 Sie zurückkehren möchten (Spalte F in diesem Beispiel).

Und vergessen Sie nicht, dass XLOOKUP im Gegensatz zu VLOOKUP die Daten anzeigen kann, die von der ausgewählten Zelle übrig bleiben. Mehr dazu weiter unten.

Sie haben auch nicht mehr das Problem einer fehlerhaften Formel, wenn neue Spalten eingefügt werden. In diesem Fall wird der Rückgabebereich automatisch angepasst.

Genaue Übereinstimmung ist die Standardeinstellung

Es war immer verwirrend, wenn man VLOOKUP lernte, warum man eine genaue Übereinstimmung angeben musste.

Glücklicherweise wird bei XLOOKUP standardmäßig eine exakte Übereinstimmung verwendet (der weitaus häufigere Grund für die Verwendung einer Suchformel). Dies reduziert die Notwendigkeit, dieses fünfte Argument zu beantworten, und stellt sicher, dass Benutzer, die neu in der Formel sind, weniger Fehler machen.

Kurz gesagt, XLOOKUP stellt weniger Fragen als VLOOKUP, ist benutzerfreundlicher und langlebiger.

XLOOKUP kann nach links schauen

Durch die Auswahl eines Suchbereichs ist XLOOKUP vielseitiger als VLOOKUP. Bei XLOOKUP spielt die Reihenfolge der Tabellenspalten keine Rolle.

VLOOKUP wurde eingeschränkt, indem die am weitesten links stehende Spalte einer Tabelle durchsucht und dann von einer bestimmten Anzahl von Spalten nach rechts zurückgekehrt wurde.

Im folgenden Beispiel müssen wir eine ID suchen (Spalte E) und den Namen der Person zurückgeben (Spalte D).

Die folgende Formel kann dies erreichen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Was tun, wenn nicht gefunden?

Benutzer von Suchfunktionen sind mit der Fehlermeldung # N / A sehr vertraut, die sie begrüßt, wenn ihr VLOOKUP oder ihre MATCH-Funktion nicht finden können, was sie benötigen. Und oft gibt es dafür einen logischen Grund.

Daher suchen 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 integriertes Argument, wenn solche Fehler nicht behandelt werden. Lassen Sie es uns mit dem vorherigen Beispiel in Aktion sehen, aber mit einer falsch eingegebenen ID.

Die folgende Formel zeigt den Text «Falsche ID» anstelle der Fehlermeldung an: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Verwenden von XLOOKUP für eine Bereichssuche

Obwohl nicht so häufig wie die exakte Übereinstimmung, besteht eine sehr effektive Verwendung einer Suchformel darin, nach einem Wert in Bereichen zu suchen. Nehmen Sie das folgende Beispiel. Wir möchten den Rabatt in Abhängigkeit vom ausgegebenen Betrag zurückgeben.

Diesmal suchen wir keinen 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 wird standardmäßig die genaue Übereinstimmung verwendet) mit dem Namen Übereinstimmungsmodus.

Sie können sehen, dass XLOOKUP über größere Funktionen mit ungefähren Übereinstimmungen verfügt als VLOOKUP.

Es besteht die Möglichkeit, die nächstgelegene Übereinstimmung zu finden, die kleiner als (-1) oder am nächsten größer als (1) der gesuchten Wert ist. Es besteht auch die Möglichkeit, Platzhalterzeichen (2) wie das? oder der *. Diese Einstellung ist standardmäßig nicht wie bei VLOOKUP aktiviert.

Die Formel in diesem Beispiel gibt den nächsten Wert zurück, der unter dem gesuchten Wert liegt, wenn keine genaue Übereinstimmung gefunden wird: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

In Zelle C7 ist jedoch ein Fehler aufgetreten, bei dem der Fehler # N / A zurückgegeben wird (das Argument «Wenn nicht gefunden» wurde nicht verwendet). Dies hätte einen Rabatt von 0% ergeben sollen, da die Ausgaben 64 die Kriterien für einen Rabatt nicht erfüllen.

Ein weiterer Vorteil der XLOOKUP-Funktion besteht darin, dass der Suchbereich nicht in aufsteigender Reihenfolge sein muss, wie dies bei VLOOKUP der Fall ist.

Geben Sie eine neue Zeile am Ende der Nachschlagetabelle ein und öffnen Sie die Formel. Erweitern Sie den verwendeten Bereich, indem Sie auf die Ecken klicken und sie ziehen.

Die Formel korrigiert den Fehler sofort. Es ist kein Problem, wenn die „0“ am unteren Rand des Bereichs liegt.

Persönlich würde ich die Tabelle immer noch nach der Nachschlagespalte sortieren. «0» unten zu haben würde mich verrückt machen. Aber die Tatsache, dass die Formel nicht gebrochen hat, ist brillant.

XLOOKUP Ersetzt auch die HLOOKUP-Funktion

Wie bereits erwähnt, ersetzt die XLOOKUP-Funktion auch HLOOKUP. Eine Funktion, um zwei zu ersetzen. Ausgezeichnet!

Die HLOOKUP-Funktion ist die horizontale Suche, die zum Suchen entlang von Zeilen verwendet wird.

Nicht so bekannt wie sein Geschwister VLOOKUP, 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 – in Spalten nach unten und auch entlang von Zeilen. Wir brauchen keine zwei verschiedenen Funktionen mehr.

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

In der Regel müssen Sie eine Liste durchsuchen, um das erste (häufig einzige) Auftreten eines Werts zu ermitteln. XLOOKUP hat ein sechstes Argument namens Suchmodus. Auf diese Weise können wir die Suche so ändern, dass sie unten beginnt, und stattdessen eine Liste nachschlagen, um das letzte Vorkommen eines Werts zu ermitteln.

Im folgenden Beispiel möchten wir den Lagerbestand für jedes Produkt in Spalte A ermitteln.

Die Nachschlagetabelle ist in Datumsreihenfolge und es gibt mehrere Bestandskontrollen pro Produkt. Wir möchten den Lagerbestand vom letzten Zeitpunkt der Überprüfung (letztes Auftreten der Produkt-ID) zurückgeben.

Das sechste Argument der XLOOKUP-Funktion bietet vier Optionen. Wir sind daran interessiert, die Option «Last-to-First suchen» zu verwenden.

Die vollständige 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 für eine genaue Übereinstimmung.

Zusammenfassen

Die XLOOKUP-Funktion ist die mit Spannung erwarteter Nachfolger zu den Funktionen VLOOKUP und HLOOKUP.

In diesem Artikel wurden verschiedene Beispiele verwendet, um die Vorteile von XLOOKUP zu demonstrieren. Eines davon ist, dass XLOOKUP über Blätter, Arbeitsmappen und auch mit Tabellen hinweg verwendet werden kann. Die Beispiele wurden im Artikel einfach gehalten, um unser Verständnis zu verbessern.

Durch dynamische Arrays werden in Excel eingeführt Bald kann es auch einen Wertebereich zurückgeben. Dies ist definitiv etwas, das es wert ist, weiter erforscht zu werden.

Die Tage von VLOOKUP sind gezählt. XLOOKUP ist da und wird bald die De-facto-Lookup-Formel sein.