So verwenden Sie die XLOOKUP-Funktion in Excel

Was ist die Xlookup-Funktion in Excel ? Wie verwende ich Xlookup in Excel ? Finden wir es mit LuckyTemplates.com heraus!

Wenn Sie regelmäßig Excel verwenden, sind Sie wahrscheinlich mit Nachschlagefunktionen vertraut. Wenn Sie es nicht wissen, müssen Sie es einfach als eine Excel-Funktion verstehen, die zum Suchen nach bestimmten Daten oder Werten in Zeilen oder Spalten in einer Tabelle verwendet wird.

Diese Funktion ist besonders nützlich, wenn Sie viele Daten in einer Excel-Tabelle verarbeiten müssen. In Excel gibt es mehrere Arten von LOOKUP-Funktionen. XLOOKUP ist einer von ihnen. Was ist also die XLOOKUP-Funktion, was ist die XLOOKUP-Formel und wie wird sie verwendet? Finden wir es mit LuckyTemplates.com heraus!

Wissenswertes über die XLOOKUP-Funktion in Excel

• Was ist XLOOKUP?
  • So greifen Sie auf die XLOOKUP-Funktion zu
  • Syntax der XLOOKUP-Funktion
• Vor- und Nachteile der XLOOKUP-Funktion in Excel
  • Vorteile der XLOOKUP-Funktion
  • Nachteile der XLOOKUP-Funktion
• So verwenden Sie die XLOOKUP-Funktion
  • Es gibt keine zusätzlichen Spaltenindexnummern
  • Standardmäßig exakte Übereinstimmung
  • XLOOKUP finden Sie auf der linken Seite
  • Verwenden Sie XLOOKUP, um nach Bereichen zu suchen
  • XLOOKUP ersetzt die HLOOKUP-Funktion
  • XLOOKUP kann von unten nach oben betrachtet werden

Was ist die XLOOKUP-Funktion in Excel?

Im Wesentlichen ersetzt XLOOKUP alte Funktionen wie HLOOKUP, VLOOKUP und LOOKUP. Es unterstützt die vertikale und horizontale Suche nach Daten.

Mit anderen Worten: Die XLOOKUP-Funktion in Excel hilft Ihnen, schnell Werte im bereitgestellten Datensatz zu finden, sowohl horizontal als auch vertikal. Anschließend wird der entsprechende Wert für verschiedene Zeilen und Spalten zurückgegeben.

Es gibt viele Möglichkeiten, die Nutzung der XLOOKUP-Funktion zu maximieren.

So greifen Sie auf die XLOOKUP-Funktion zu

Derzeit ist XLOOKUP nur für Benutzer des Insiders-Programms verfügbar. Jeder, der dem Insider-Programm beitritt, kann auf die neuesten Excel-Funktionen zugreifen, sobald diese verfügbar sind. Microsoft wird diese Funktion bald für alle Office 365-Benutzer bereitstellen. Leider ist XLOOKUP nicht für Benutzer verfügbar, die die Versionen Office 2010, 2013, 2016 und 2019 ausführen.

Wenn Sie die Microsoft 365-Suite nicht auf Ihrem Computer verwenden, müssen Sie möglicherweise ein Upgrade auf Microsoft 365 durchführen, um Zugriff auf die XLOOKUP-Funktionalität zu erhalten. Und wenn Sie Microsoft 365 verwenden, wird die Option in Excel aktiviert angezeigt. XLOOKUP ist auch auf Office 365 Online verfügbar.

Syntax der XLOOKUP-Funktion

Die Syntax der XLOOKUP-Funktion ähnelt der Syntax der VLOOKUP- und HLOOKUP-Funktionen. Wenn Sie sie schon einmal verwendet haben, wird die Verwendung von XLOOKUP für Sie bequemer sein. Hier ist die Syntax für die XLOOKUP-Funktion in Excel:

= XLOOKUP ( lookup_value , lookup_array , return_array , [if_not_found], [match_mode], [search_mode])

Die XLOOKUP-Funktion unterstützt bis zu 6 Argumente und hier sind ihre Werte.

1. lookup_value (erforderlich): Der Wert, den Sie suchen möchten.

2. lookup_array (erforderlich): Das Array, in dem Sie nach dem Suchwert suchen möchten.

3. return_array (erforderlich): Array, von dem Sie Werte abrufen und zurückgeben möchten, wenn der Suchwert gefunden wird.

4. [if_not_found] (optional): Wenn keine Übereinstimmung gefunden wird, ist dies der zurückgegebene Wert.

5. [match_mode] (optional): Mit diesem Argument können Sie den gesuchten Match-Typ angeben. Zur Angabe gibt es verschiedene Werte:

• 0 – Sucht nach einer genauen Übereinstimmung und der Wert muss genau mit dem Wert in „lookup_array“ übereinstimmen. Dieser Wert wird auch als Standardwert festgelegt, wenn er nicht erwähnt wird.
• -1 – Sucht nach einer genauen Übereinstimmung und kehrt, sobald sie gefunden wurde, zum nächstkleineren Wert zurück.
• 1 – Sucht nach einer genauen Übereinstimmung und gibt, wenn sie gefunden wird, den nächstgrößeren Wert zurück.
• 2 – Führen Sie einen teilweisen Abgleich mit Platzhaltern durch, wobei *, ? und ~ hat eine besondere Bedeutung.

6. [search_mode] (optional): Wird verwendet, um den Suchmodus von XLOOKUP in lookup_array anzugeben. Es gibt verschiedene Werte, um dasselbe anzugeben:

• 1 – Führen Sie eine Suche beginnend mit dem ersten Element durch. Dieser Wert wird als Standardwert festgelegt, wenn nichts angegeben wird.
• -1 – Führt eine Rückwärtssuche beginnend mit dem letzten Element durch.
• 2 – Führen Sie eine binäre Suche in „lookup_array“ durch , wobei die Daten in aufsteigender Reihenfolge sortiert werden müssen. Wenn Daten nicht sortiert sind, kann es zu Fehlern oder falschen Ergebnissen kommen.
• -2 – Führen Sie eine binäre Suche in „lookup_array“ durch , wobei die Daten in absteigender Reihenfolge sortiert werden müssen. Wenn Daten nicht sortiert sind, kann es zu Fehlern oder falschen Ergebnissen kommen.

Vor- und Nachteile der XLOOKUP-Funktion in Excel

XLOOKUP ist immer noch eine vorteilhafte Funktion im Vergleich zu VLOOKUP und INDEX/ATCH. Aber es hat auch seine eigenen Nachteile.

Vorteile der XLOOKUP-Funktion

• Der Kiefer arbeitet sowohl vertikal als auch horizontal.
• Es werden nur 3 Argumente benötigt, statt 4 wie bei VLOOKUP und INDEX MATCH.
• Standardmäßig wird immer eine exakte Übereinstimmung verwendet.
• Teilweise Suchvorgänge können mithilfe von Platzhaltern durchgeführt werden.
• Suchvorgänge können in absteigender Reihenfolge durchgeführt werden.
• Verwenden Sie in INDEX MATCH eine Funktion statt zwei.

Nachteile der XLOOKUP-Funktion

• Optionale Argumente können für Anfänger kompliziert erscheinen.
• Es kann zeitaufwändiger sein, wenn Sie zwei Bereiche auswählen und zu viele Zellen in der Tabelle haben.
• Gibt einen Fehler zurück, wenn ein Array gesucht und zurückgegeben wird, das nicht die gleiche Länge hat.
• Merken Sie sich sowohl den Such- als auch den Rückgabebereich.

Vorteile der Verwendung der XLOOKUP-Excel-Funktion

• XLOOKUP macht die am häufigsten verwendeten Formeln in Excel einfacher und weniger fehleranfällig . Sie müssen nur =XLOOKUP (zu findende Daten, Liste, Ergebnisliste) schreiben und erhalten die Antwort oder #N/A, wenn der Wert nicht gefunden wird.
• Die Standardsuchergebnisse sind exakt . Einer der Nachteile von VLOOKUP besteht darin, dass Sie FALSE als letzten Parameter angeben müssen, um das richtige Ergebnis zu erhalten. XLOOKUP behebt dieses Problem, indem es standardmäßig eine exakte Übereinstimmung durchführt. Sie können den Match-Modus-Parameter verwenden, um das Suchverhalten bei Bedarf zu ändern.
• Der vierte Parameter unterstützt den Fall, dass der Wert nicht gefunden wird . In den meisten Geschäftssituationen werden Sie gezwungen sein, die Suchformel mit IFERROR oder IFNA zu „umschließen“, um Fehler zu beseitigen. XLOOKUP bietet einen vierten Parameter, damit Sie das gewünschte Standardergebnis ermitteln können, wenn der Wert nicht gefunden wird.
• XLOOKUP stellt Auswahlparameter zur Verfügung, um spezielle Situationen zu finden . Sie können von oben oder unten nach Sonderzeichen und schnelleren Optionen suchen, um gefilterte Daten zu finden.

So verwenden Sie die XLOOKUP-Funktion

Schauen Sie sich das folgende Beispiel an, um zu verstehen, wie XLOOKUP funktioniert. In diesem Beispiel müssen wir den Teil aus Spalte F für jede ID in Spalte A zurückgeben.

Dies ist ein Beispiel, das nach genauen Ergebnissen sucht, die XLOOKUP-Funktion erfordert jedoch nur drei Arten von Informationen.

Das Bild unten zeigt XLOOKUP mit 5 Argumenten, aber die ersten drei Argumente werden für korrekte Ergebnisse benötigt. Konzentrieren wir uns also auf sie:

• Lookup_value : Suchwert
• Lookup_array : Suchbereich
• Return_array : Bereich, der den zurückzugebenden Wert enthält

In diesem Beispiel verwenden wir die Formel:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Lassen Sie uns nun einige der Vorteile von XLOOKUP gegenüber VLOOKUP erkunden.

Es gibt keine zusätzlichen Spaltenindexnummern

Das dritte Argument von VLOOKUP besteht darin, die Anzahl der Informationsspalten anzugeben, die aus dem Tabellenbereich zurückgegeben werden sollen. Dies ist mit XLOOKUP kein Problem mehr, da die Funktion es dem Benutzer ermöglicht, den Rückgabebereich auszuwählen. (Spalte F in diesem Beispiel).

Und vergessen Sie nicht, dass XLOOKUP im Gegensatz zu SVERWEIS die verbleibenden Daten der ausgewählten Zelle sehen kann.

Beim Hinzufügen neuer Spalten treten auch keine Formelfehler auf. Wenn dies in der Tabelle geschieht, wird der Rückgabebereich automatisch angepasst.

Standardmäßig exakte Übereinstimmung

Bei Verwendung von VLOOKUP muss der Benutzer auf Wunsch eine genaue Übereinstimmung angeben. Aber bei XLOOKUP ist die Standardeinstellung eine exakte Übereinstimmung. Dies reduziert das vierte Argument und sorgt für weniger Fehler für neue Benutzer. Kurz gesagt, XLOOKUP erfordert weniger Fragen als SVERWEIS und ist benutzerfreundlich.

XLOOKUP finden Sie auf der linken Seite

Die Möglichkeit, Suchbereiche auszuwählen, verleiht XLOOKUP mehr Flexibilität als VLOOKUP. Bei XLOOKUP spielt die Reihenfolge der Spalten in der Tabelle keine Rolle.

SVERWEIS wird dadurch eingeschränkt, dass die Spalte ganz links in der Tabelle durchsucht wird und dann aus einer bestimmten Anzahl von Spalten auf der rechten Seite zurückgekehrt wird.

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

Verwenden Sie die folgende Formel:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Verwenden Sie XLOOKUP, um nach Bereichen zu suchen

Obwohl dies nicht so häufig vorkommt wie die exakte Übereinstimmung, können Sie eine Suchformel verwenden, um einen Wert innerhalb eines Bereichs zu finden. Beispielsweise möchten wir abhängig vom ausgegebenen Betrag einen Rabatt zurückerstatten.

Dieses Mal suchen wir nicht nach einem bestimmten Wert und müssen wissen, in welchen Bereich die Werte in Spalte B in Spalte E fallen, um den Rabatt zu ermitteln.

XLOOKUP hat ein optionales viertes Argument (denken Sie daran, es ist standardmäßig die absolute Übereinstimmung) namens match_mode.

Sie sehen, dass XLOOKUP ungefähre Übereinstimmungen besser finden kann als VLOOKUP.

Es besteht die Möglichkeit, die nächste Übereinstimmung kleiner als (-1) oder die nächste Übereinstimmung größer als (1) des Suchwerts zu finden. Es besteht auch die Möglichkeit, Platzhalter (2) zu verwenden, z. B. ? oder *. Diese Einstellung ist nicht standardmäßig aktiviert, wie dies bei VLOOKUP der Fall ist.

Die Formel in diesem Beispiel gibt den nächsten Wert zurück, der kleiner als der Suchwert ist, wenn keine genaue Übereinstimmung gefunden wird:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Es gab jedoch einen Fehler in Zelle C7. Der Rabatt von 0 % hätte zurückgegeben werden sollen, da nur 64 ausgegeben wurden, was die Kriterien für den Erhalt des Rabatts nicht erfüllte.

Ein weiterer Vorteil der XLOOKUP-Funktion besteht darin, dass sie keinen Suchbereich in aufsteigender Reihenfolge wie bei VLOOKUP erfordert.

Geben Sie unten in der Nachschlagetabelle eine neue Zeile ein und öffnen Sie dann die Formel. Erweitern Sie den Einsatzbereich durch Klicken und Ziehen der Ecken.

Die Formel korrigiert den Fehler sofort. Es ist kein Problem, wenn am Ende der Bereichstabelle eine 0 steht.

XLOOKUP ersetzt die HLOOKUP-Funktion

Wie bereits erwähnt, kann die XLOOKUP-Funktion auch die HLOOKUP-Funktion ersetzen. Eine Funktion kann zwei Funktionen ersetzen, was könnte besser sein?

Die HLOOKUP-Funktion sucht horizontal nach oben und wird für die Suche nach Zeilen verwendet.

Diese Funktion ist nicht so bekannt wie VLOOKUP, aber nützlich, wenn sich der Titel in Spalte A und die Daten in den Zeilen 4 und 5 befinden, wie im folgenden Beispiel.

XLOOKUP kann in beide Richtungen suchen, nach unten in Spalten und entlang von Zeilen.

In diesem Beispiel wird eine Formel verwendet, um den Verkaufswert zurückzugeben, der dem Namen in Spalte A2 zugeordnet ist. 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 betrachtet werden

Normalerweise müssen Sie in einer Liste von oben nach unten suchen, um das erste Vorkommen eines Werts zu finden. XLOOKUP hat ein fünftes Argument namens search_mode. Dieses Argument ermöglicht es, die Suche so umzuleiten, dass sie am Ende einer Liste beginnt, um das letzte Vorhandensein eines Werts zu finden.

Im folgenden Beispiel müssen wir den Lagerbestand jedes Produkts in Spalte A ermitteln.

Die Nachschlagetabelle ist nach Datum sortiert. Wir möchten den Lagerbestand der letzten Prüfung (letztes Vorkommen der Produkt-ID) zurückgeben.

Das fünfte Argument der XLOOKUP-Funktion bietet vier Optionen. Hier verwenden wir die Option „Letzte-zu-erste-Suche“ .

In diesem Beispiel verwendete Formel:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

In dieser Formel wird das vierte Argument weggelassen. Dies ist optional und wir möchten, dass die Standardeinstellung eine exakte Übereinstimmung ist.

Die XLOOKUP-Funktion ist der erwartete Nachfolger der VLOOKUP- und HLOOKUP-Funktionen. In diesem Artikel wurde eine Reihe von Beispielen verwendet, um die Vorteile der XLOOKUP-Funktion zu demonstrieren.

XLOOKUP ist eine große Verbesserung, wenn es um Suchfunktionen geht. Die einzige Einschränkung der XLOOKUP-Funktion besteht darin, dass sie nicht abwärtskompatibel ist. Wenn Sie Tabellenkalkulationen mit älteren Versionen der Microsoft Office Suite teilen, erhalten Sie möglicherweise Fehlermeldungen.

Sie können jedoch über Office 365 Online auf XLOOKUP zugreifen. XLOOKUP befindet sich noch in der Entwicklung, daher wird es einige Zeit dauern, bis Excel-Benutzer es übernehmen.

Ich wünsche Ihnen viel Erfolg!

Unterschied zwischen XLOOKUP und VLOOKUP

Warum ist XLOOKUP besser als VLOOKUP?

Obwohl VLOOKUP seine eigenen Verwendungsmöglichkeiten hat, verfügt es über eingeschränktere Funktionen als XLOOKUP. Dies macht XLOOKUP zur beliebtesten Wahl, wenn große Datenmengen verarbeitet werden müssen. Dadurch läuft es schneller und weist weniger Fehler auf.

• XLOOKUP schränkt den Datenbereich nicht ein : XLOOKUP ist flexibler als VLOOKUP. VLOOKUP kann Werte suchen, die sich nur in der Spalte ganz links einer Tabelle befinden, und Werte aus der entsprechenden Spalte rechts zurückgeben. Mittlerweile erfordert XLOOKUP nur einfachere Schritte und kann Werte für jede Spalte in beide Richtungen zurückgeben.
• Das Standardergebnis von XLOOKUP ist der genaueste Wert .
• Standardmäßig findet die SVERWEIS-Funktion in Excel immer ungefähre Ergebnisse . Wenn Sie jedoch genaue Ergebnisse benötigen, sollten Sie im Argument range_lookup FALSE eingeben.
• Die XLOOKUP-Funktion sucht nach einem Suchwert in einem bestimmten Bereich und verwendet standardmäßig immer den genauen Wert. Wenn Sie keinen finden können, können Sie das Argument bearbeiten, um eine ungefähre Übereinstimmung zu finden, die kleiner oder größer als der Suchwert ist.

Andere Gründe:

• XLOOKUP kann das Einfügen oder Löschen von Spalten verarbeiten.
• XLOOKUP implementiert vertikale und horizontale Suchvorgänge.
• XLOOKUP bietet intelligentere ungefähre Übereinstimmungen.
• XLOOKUP benötigt weniger Zellreferenzen.