So verwenden Sie die XLOOKUP-Funktion in Microsoft Excel

Excels neues XLOOKUP ersetzt SVERWEIS, bietet einen leistungsstarken Ersatz für eine der beliebtesten Excel-Funktionen. Diese neue Funktion löst einige der Einschränkungen von SVERWEIS und bietet zusätzliche Funktionen. Das müssen Sie wissen.

Was ist XLOOKUP?

Die neue XLOOKUP-Funktion enthält Korrekturen für einige der größten Einschränkungen von SVERWEIS. Zur selben Zeit, ersetzt auch HLOOKUP. Als Beispiel, XLOOKUP kann nach links schauen, Standardmäßig ist es eine genaue Ü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 alles funktioniert.

Für den Moment, XLOOKUP ist nur für Benutzer des Insiders-Programms aktiviert. Jeder kann Nehmen Sie am Insider-Programm teil um auf die neuesten Excel-Funktionen zuzugreifen, sobald sie verfügbar sind. Microsoft wird es bald für alle Office-Benutzer einführen 365.

So verwenden Sie die XLOOKUP-Funktion

Lassen Sie uns direkt in ein Beispiel für XLOOKUP in Aktion eintauchen. Nehmen Sie die Beispieldaten unten. Wir möchten die Abteilung der 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 nächste Bild zeigt XLOOKUP mit sechs Argumenten, aber nur die ersten drei werden für eine genaue Übereinstimmung benötigt. Also konzentrieren wir uns auf sie:

• Suchwert: Nach was suchst Du.
• Lookup_array: Wo zu 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 SVERWEIS hier 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 Hindernis mehr, da Sie mit XLOOKUP den Bereich auswählen können, aus dem Sie zurückkehren möchten. (Spalte F in diesem Beispiel).

Und nicht vergessen, XLOOKUP kann die verbleibenden Daten der ausgewählten Zelle sehen, im Gegensatz zu SVERWEIS. Mehr dazu weiter unten.

Zur selben Zeit, habe nicht mehr das Problem einer kaputten Formel beim Einfügen neuer Spalten. Wenn das in Ihrer Tabelle passiert ist, der Rücklaufbereich würde automatisch angepasst.

Genaue Übereinstimmung ist die Standardeinstellung

Beim Lernen von SVERWEIS war es immer verwirrend, warum man eine genaue Übereinstimmung angeben musste.

Glücklicherweise, XLOOKUP ist standardmäßig auf eine genaue Übereinstimmung eingestellt, der viel häufigere Grund, eine Suchformel zu verwenden). Dies reduziert die Notwendigkeit, dieses fünfte Argument zu beantworten, und sorgt für weniger Fehler von Benutzern, die neu in der Formel sind..

Dann, Zusammenfassend, XLOOKUP stellt weniger Fragen als SVERWEIS, es ist einfacher zu bedienen und ist auch langlebiger.

XLOOKUP kann nach links schauen

Die Möglichkeit, einen Suchbereich auszuwählen, macht XLOOKUP vielseitiger als SVERWEIS. Mit XVERWEIS, die Reihenfolge der Tabellenspalten spielt keine Rolle.

SVERWEIS wurde eingeschränkt, indem in der äußersten linken Spalte einer Tabelle gesucht wurde und dann von einer angegebenen Anzahl von Spalten nach rechts zurückgekehrt wurde.

Im folgenden Beispiel, wir müssen nach einer 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 Sie es nicht finden können

Benutzer von Suchfunktionen sind mit der Fehlermeldung bestens vertraut # n / Begrüßen Sie sie, wenn ihre SVERWEIS- oder MATCH-Funktion nicht findet, was sie brauchen. Und das hat oft einen logischen Grund.

Weil, Benutzer recherchieren schnell, wie man diesen Fehler ausblenden kann, da er weder richtig noch nützlich ist. UND, auf jeden Fall, es gibt Alternativen dazu.

XLOOKUP kommt mit einem eigenen Argument “falls nicht gefunden” integriert, um solche Fehler zu behandeln. Sehen wir es uns mit dem obigen Beispiel in Aktion an, aber mit falsch geschriebener ID.

In der nächsten Formel wird der Text angezeigt. “Falsche ID” Anstelle der Fehlermeldung: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Verwenden von XLOOKUP für eine Bereichssuche

Auch wenn es nicht so häufig vorkommt wie die genaue Übereinstimmung, Eine sehr effektive Verwendung einer Suchformel besteht darin, nach einem Wert in Bereichen zu suchen. Nehmen Sie das folgende Beispiel. Wir möchten den Rabatt basierend auf dem ausgegebenen Betrag zurückerstatten.

Diesmal suchen wir keinen bestimmten Wert. Wir müssen wissen, wo die Werte von Spalte B innerhalb der Bereiche von Spalte E liegen. Das bestimmt den erhaltenen Rabatt.

XLOOKUP hat ein optionales fünftes Argument (erinnern, Standardmäßig wird die genaue Übereinstimmung verwendet) genannt Matching-Modus.

Sie können sehen, dass XLOOKUP bei Fuzzy-Matches größere Fähigkeiten hat als SVERWEIS.

Es gibt eine Option, um die nächste Übereinstimmung mit weniger als zu finden (-1) oder näher größer als (1) der gesuchte Wert. Es gibt auch eine Option, Platzhalterzeichen zu verwenden (2) Was? o la *. Diese Einstellung ist nicht standardmäßig aktiviert wie bei SVERWEIS.

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: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Trotz dieses, es gibt einen Fehler in Zelle C7, wo der Fehler zurückgegeben wird # n / EIN (das Argument 'wenn nicht gefunden' wurde nicht verwendet). Dies hätte zurückkommen sollen 0% aus, weil du ausgibst 64 erfüllt nicht die Kriterien für einen Rabatt.

Ein weiterer Vorteil der SEARCH X-Funktion besteht darin, dass der Suchbereich nicht wie bei SVERWEIS aufsteigend sortiert sein muss..

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

Die Formel behebt den Fehler sofort. Es ist kein Hindernis, die “0” am unteren Ende des Bereichs.

Persönlich, würde die Tabelle immer noch nach der Nachschlagespalte sortieren. Haben Sie eine “0” unten würde ich verrückt werden. Aber die Tatsache, dass die Formel nicht gebrochen wurde, ist genial.

XLOOKUP überschreibt auch die HLOOKUP-Funktion

Wie erwähnt, die XLOOKUP-Funktion ist auch hier, um HLOOKUP zu ersetzen. Eine Funktion ersetzt zwei. Exzellent!

HLOOKUP-Funktion ist horizontale Suche, Wird verwendet, um zeilenübergreifend zu suchen.

Er ist nicht so bekannt wie sein Bruder SVERWEIS, aber es ist nützlich für Beispiele wie das unten gezeigte, wobei die Überschriften in Spalte A und die Daten in den Zeilen stehen 4 und 5.

XLOOKUP kann in beide Richtungen schauen: Spalten nach unten und weiter entlang der Zeilen. Wir brauchen nicht mehr zwei verschiedene Funktionen.

In diesem Beispiel, Die Formel wird verwendet, um den Verkaufswert in Bezug auf den Namen in Zelle A2 zurückzugeben. Suche entlang der Linie 4 um den Namen zu finden und den Wert der Zeile zurückzugeben 5: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP kann von unten nach oben schauen

Im Allgemeinen, Sie müssen eine Liste durchsuchen, um die erste zu finden (oft einzigartig) Erscheinen eines Wertes. XLOOKUP hat ein sechstes Argument namens Suchmodus. Dies ermöglicht es uns, die Suche so zu ändern, dass sie unten beginnt, und eine Liste zu durchsuchen, um das letzte Vorkommen eines Werts zu finden.

Im folgenden Beispiel, wir möchten den Lagerbestand jedes Produkts in Spalte A finden.

Die Nachschlagetabelle ist nach Datum sortiert und es gibt mehrere Bestandsprüfungen pro Produkt. Wir möchten den Lagerbestand von der letzten Überprüfung zurückgeben (letztes Vorkommen der Produkt-ID).

Das sechste Argument der Funktion SEARCH X bietet vier Optionen. Wir sind daran interessiert, die Option “Suche vom Letzten zum Ersten”.

Die vollständige Formel wird hier angezeigt: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

In dieser Formel, das vierte und fünfte Argument wurden ignoriert. Es ist optional und wir wollten den Standardwert einer genauen Übereinstimmung.

Rundung

Die XLOOKUP-Funktion ist die mit Spannung erwarteter Nachfolger zu den SVERWEIS- und HVERWEIS-Funktionen.

In diesem Beitrag wurden mehrere Beispiele verwendet, um die Vorteile von XLOOKUP . zu demonstrieren. Einer davon ist, dass XLOOKUP auf Blättern verwendet werden kann, Arbeitshefte und auch mit Tabellen. Die Beispiele wurden im Beitrag einfach gehalten, um unser Verständnis zu erleichtern.

Weil dynamische Arrays, die in Excel eingegeben werden früh, es kann auch einen Wertebereich zurückgeben. Bestimmt, Dies ist etwas, das hervorzuheben ist, um es weiter zu erkunden.

SVERWEIS-Tage sind gezählt. XLOOKUP ist da und wird bald die De-facto-Suchformel sein.