So verwenden Sie die NEUE & ERWEITERTE XLOOKUP-Funktion in Excel (10 Beispiele)

Die neue Excel-XLOOKUP-Funktion ist die leistungsstärkste und einfachste Suchfunktion, die Excel zu bieten hat. Nach unermüdlichen Bemühungen hat Microsoft sie endlich veröffentlicht, um VLOOKUP, HLOOKUP, INDEX+VERGLEICH und andere Suchfunktionen abzulösen.

In diesem Tutorial zeigen wir Ihnen, welche Vorteile XLOOKUP bietet und wie Sie diese nutzen können, um verschiedene Suchaufgaben zu meistern.

Wie erhält man XLOOKUP?

Syntax

Beispiele

• Beispiel 1: Exakte Übereinstimmung
  • Führen Sie eine exakte Übereinstimmung mit XLOOKUP durch
  • Mit nur wenigen Klicks zur exakten Übereinstimmung

• Beispiel 2: Ungefähre Übereinstimmung
  • Führen Sie eine ungefähre Übereinstimmung mit XLOOKUP durch

• Beispiel 3: Platzhalter-Übereinstimmung
  • Führen Sie eine Platzhalter-Übereinstimmung mit XLOOKUP durch

• Beispiel 4: Suche von rechts nach links
  • Von rechts nach links suchen mithilfe von XLOOKUP
  • Suchen Sie Werte von rechts nach links mit wenigen Klicks

• Beispiel 5: Vertikale oder horizontale Suche
  • Führen Sie eine vertikale oder horizontale Suche mit XLOOKUP durch

• Beispiel 6: Zwei-Wege-Suche
  • Führen Sie eine Zwei-Wege-Suche mit XLOOKUP durch

• Beispiel 7: Anpassen der Meldung bei Nichtvorhandensein
  • Passen Sie die Meldung für nicht gefundene Werte mithilfe von XLOOKUP an
  • Passen Sie den #NV-Fehler mit einer praktischen Funktion an

• Beispiel 8: Mehrere Werte
  • Geben Sie mehrere Werte mit XLOOKUP zurück

• Beispiel 9: Mehrere Kriterien
  • Führen Sie eine Suche mit mehreren Kriterien mithilfe von XLOOKUP durch
  • Suche - Mehrere Bedingungen suchen mit einer schnellen Methode

• Beispiel 10: Letzte Übereinstimmung finden
  • Rufen Sie das letzte übereinstimmende Ergebnis mit XLOOKUP ab
  • Suchen Sie den letzten übereinstimmenden Wert mühelos mit einem hervorragenden Werkzeug

Laden Sie die XLOOKUP-Beispieldatei herunter

Wie erhält man XLOOKUP?

Da die XLOOKUP-Funktion ausschließlich in Excel für Microsoft 365, Excel 2021 und neueren Versionen sowie in Excel für das Web verfügbar ist, empfiehlt sich ein Upgrade, wenn Sie Excel 2019 oder eine frühere Version nutzen – so erhalten Sie Zugriff auf XLOOKUP.

Syntax

Die Funktion durchsucht einen Bereich oder ein Array und gibt den Wert des ersten übereinstimmenden Ergebnisses zurück. Die Syntax lautet wie folgt:

Argumente:

1. Lookup_value (required)Der gesuchte Wert kann sich in einer beliebigen Spalte des Tabellenmatrixbereichs befinden.
2. Lookup_array (required)Der Bereich oder das Array, in dem Sie nach dem gesuchten Wert suchen.
3. Return_array (required)Der Bereich oder das Array, aus dem Sie den gewünschten Wert abrufen möchten.
4. If_not_found (optional): Der Wert, der zurückgegeben wird, wenn keine gültige Übereinstimmung gefunden wird. Passen Sie den Text im Argument [Wenn_nicht_gefunden] an, um klarzustellen, dass keine Übereinstimmung vorliegt.
   Andernfalls ist der Standardrückgabewert #NV.
5. Match_mode (optional)Hier legen Sie fest, wie der Suchwert mit den Werten im Suchbereich verglichen wird.
   • 0 (Standard) = Exakte Übereinstimmung. Wird keine Übereinstimmung gefunden, wird #NV zurückgegeben.
   • -1 = Exakte Übereinstimmung. Wird keine Übereinstimmung gefunden, wird der nächstkleinere Wert zurückgegeben.
   • 1 = Exakte Übereinstimmung. Wird keine Übereinstimmung gefunden, wird der nächstgrößere Wert zurückgegeben.
   • 2 = Teilweise Übereinstimmung. Verwenden Sie Platzhalterzeichen wie *, ? und ~, um eine Übereinstimmung mit Platzhaltern durchzuführen.
6. Search_mode (optional)Hier legen Sie die Suchreihenfolge fest.
   • 1 (Standard) = Durchsucht den Suchwert vom ersten bis zum letzten Element im angegebenen Suchbereich.
   • -1 = Durchsucht den Suchwert vom letzten bis zum ersten Element – ideal, wenn Sie das letzte übereinstimmende Ergebnis im Suchbereich benötigen.
   • 2. Führt eine binäre Suche durch, die voraussetzt, dass der Suchbereich in aufsteigender Reihenfolge sortiert ist – andernfalls ist das Ergebnis ungültig.
   • -2 = Führt eine binäre Suche durch, die voraussetzt, dass der Suchbereich in absteigender Reihenfolge sortiert ist – andernfalls ist das Ergebnis ungültig.

Für detaillierte Informationen zu den Argumenten gehen Sie bitte wie folgt vor:

1. Geben Sie die folgende Syntax in eine leere Zelle ein – dabei reicht es aus, nur eine Seite der Klammer einzugeben.

2. Drücken Sie Strg+A. Daraufhin erscheint ein Dialogfeld mit den Funktionsargumenten, und die schließende Klammer wird automatisch vervollständigt.

3. Ziehen Sie das Datenfenster nach unten, um alle sechs Funktionsargumente von XLOOKUP anzuzeigen.

>>>

Beispiele

Sie haben nun sicher die Grundprinzipien von XLOOKUP verstanden – tauchen wir direkt in praktische Beispiele ein!

Beispiel 1: Exakter Treffer

Führen Sie eine exakte Übereinstimmung mit XLOOKUP durch

Haben Sie sich jemals geärgert, weil Sie bei jeder Verwendung von VLOOKUP explizit den exakten Übereinstimmungsmodus angeben mussten? Mit der beeindruckenden XLOOKUP-Funktion gehört dieses Ärgernis der Vergangenheit an – sie liefert standardmäßig einen exakten Treffer.

Angenommen, Sie haben eine Liste Ihres Büromaterialbestands und möchten den Stückpreis eines bestimmten Artikels – etwa einer Maus – herausfinden. So gehen Sie vor:

Geben Sie die folgende Formel in die leere Zelle F2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP(E2,A2:A10,C2:C10)

Sie kennen nun den Stückpreis der Maus – ermittelt mit der fortschrittlichen XLOOKUP-Formel. Da der Übereinstimmungsmodus standardmäßig auf „exakt“ eingestellt ist, müssen Sie ihn nicht extra angeben. Das macht XLOOKUP deutlich einfacher und effizienter als VLOOKUP.

Exakter Treffer mit nur wenigen Klicks

Möglicherweise verwenden Sie eine ältere Excel-Version und planen noch nicht, auf Excel 2021 oder Microsoft 365 zu aktualisieren. In diesem Fall empfehle ich Ihnen eine praktische Funktion: „Wert suchen“. Mit dieser Funktion erhalten Sie das Ergebnis – ganz ohne komplizierte Formeln oder Zugriff auf XLOOKUP.

1. Klicken Sie auf die Zelle, in der das Suchergebnis angezeigt werden soll.

2. Wechseln Sie zur Registerkarte „Kutools“, klicken Sie auf „Formelhelfer“ und anschließend in der Dropdown-Liste erneut auf „Formelhelfer“.

3. Konfigurieren Sie im Dialogfeld „Formelhelfer“ Folgendes:

• Wählen Sie im Abschnitt „Formeltyp“ die Option „Suche“ aus;
• Wählen Sie im Abschnitt „Wählen Sie eine Formel aus“ die Option „Daten in einem Bereich suchen“ aus;
• Führen Sie im Abschnitt „Argumenteingabe“ Folgendes aus:
  • Wählen Sie im Feld „Tabellenmatrix“ die Datenbereich aus, die den Suchwert und den Ergebniswert enthält;
  • Wählen Sie im Feld „Suchwert“ die Zelle oder den Bereich mit dem gesuchten Wert aus. Achten Sie darauf, dass sich dieser Wert in der ersten Spalte der Tabellenmatrix befindet.
  • Wählen Sie im Feld „Spalte“ diejenige Spalte aus, aus der der passende Wert zurückgegeben werden soll.

4. Klicken Sie auf „OK“, um Ihr Ergebnis zu erhalten.

Beispiel 2: Ungefähre Übereinstimmung

Führen Sie eine ungefähre Übereinstimmung mit XLOOKUP durch

Um eine ungefähre Suche durchzuführen, setzen Sie den Übereinstimmungsmodus im fünften Argument auf 1 oder -1. Wird keine exakte Übereinstimmung gefunden, gibt die Funktion den nächstgrößeren bzw. nächstkleineren Wert zurück.

In diesem Fall möchten Sie die Steuersätze für das Einkommen Ihrer Mitarbeiter ermitteln. Auf der linken Seite des Arbeitsblatts finden Sie die Bundessteuerklassen für 2021. Wie ermitteln Sie nun den passenden Steuersatz Ihrer Mitarbeiter in Spalte E? Keine Sorge – so geht’s:

1. Geben Sie die folgende Formel in die leere Zelle E2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Passen Sie danach das Format des Ergebnisses nach Bedarf an.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

>>>

✓ Hinweis: Das vierte Argument [Wenn_nicht_gefunden] ist optional und wurde daher weggelassen.

2. Sie kennen nun den Steuersatz der Zelle D2. Um die übrigen Ergebnisse zu erhalten, wandeln Sie die Zellbezüge des Such- und Rückgabebereichs in absolute Bezüge um.

• Doppelklicken Sie auf die Zelle E2, um die Formel =XLOOKUP(D2;B2:B8;A2:A8;;1) anzuzeigen;
• Wählen Sie im Formelbereich den Suchbereich B2:B8 aus und drücken Sie die F4-Taste, um $B$2:$B$8 zu erhalten;
• Wählen Sie im Formelbereich den Rückgabebereich A2:A8 aus und drücken Sie die F4-Taste, um $A$2:$A$8 zu erhalten;
• Drücken Sie die Eingabetaste, um das Ergebnis in Zelle E2 anzuzeigen.

>>>

3. Ziehen Sie anschließend den Ausfüllkorb nach unten, um sämtliche Ergebnisse zu erhalten.

√ Hinweis:

• Durch Drücken der F4-Taste auf Ihrer Tastatur verwandeln Sie den Zellbezug in einen absoluten Bezug – ganz einfach durch automatisches Einfügen von Dollarzeichen vor Zeile und Spalte.
• Nachdem wir absolute Bezüge auf Such- und Rückgabebereich angewendet haben, lautet die Formel in Zelle E2 nun wie folgt:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

• Wenn Sie den Ausfüllknoten von Zelle E2 nach unten ziehen, passt sich die Formel in jeder Zelle der Spalte E automatisch an – und zwar ausschließlich hinsichtlich des Suchwerts.
  Beispielsweise lautet die Formel in Zelle E13 nun wie folgt:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Beispiel 3: Platzhalterübereinstimmung

Führen Sie eine Platzhalter-Übereinstimmung mit XLOOKUP durch

Bevor wir uns mit der Platzhalter-Übereinstimmungsfunktion von XLOOKUP beschäftigen, werfen wir zunächst einen Blick darauf, was Platzhalterzeichen sind.

In Microsoft Excel sind Platzhalter spezielle Zeichen, mit denen Sie Zeichenfolgen in Batches ersetzen können – besonders nützlich, wenn Sie nach teilweisen Übereinstimmungen suchen.

Es gibt drei Arten von Platzhaltern: Sternchen (*), Fragezeichen (?) und Tilde (~).

• Das Sternchen (*) steht für eine beliebige Anzahl von Zeichen im Text;
• Das Fragezeichen (?) steht für ein einzelnes beliebiges Zeichen im Text;
• Die Tilde (~) wandelt Platzhalter (*, ? und ~) in wörtliche Zeichen um. Stellen Sie einfach eine Tilde (~) vor den jeweiligen Platzhalter, um diese Funktion zu aktivieren.

In den meisten Fällen nutzen wir bei der XLOOKUP-Platzhalterübereinstimmung das Sternchen (*). Schauen wir uns nun an, wie die Platzhalterübereinstimmung funktioniert.

Angenommen, Sie verfügen über eine Liste der Börsenwerte der 50 größten amerikanischen Unternehmen und möchten den Marktwert einiger Unternehmen ermitteln, deren Firmenname jedoch abgekürzt sind. Dies ist das perfekte Szenario für eine Platzhalterübereinstimmung. Folgen Sie mir Schritt für Schritt, um diesen Trick anzuwenden.

✓ Hinweis: Um eine Platzhalterübereinstimmung durchzuführen, setzen Sie das fünfte Argument [Übereinstimmungsmodus] auf 2.

1. Geben Sie die folgende Formel in die leere Zelle H3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP("*"&G3&„*",B3:B52,D3:D52,,2)

>>>

2. Sie kennen nun das Ergebnis der Zelle H3. Um die übrigen Ergebnisse zu erhalten, fixieren Sie den Suchbereich und den Rückgabebereich, indem Sie den Cursor in den jeweiligen Bereich setzen und die F4-Taste drücken. Die Formel in H3 lautet dann:

=XLOOKUP("*"&G3&„*",$B$3:$B$52,$D$3:$D$52,,2)

3. Ziehen Sie den Ausfüllkorb nach unten, um sämtliche Ergebnisse zu erhalten.

√ Hinweis:

• Der Suchwert der Formel in Zelle H3 lautet „*“&G3&„*“. Dabei verknüpfen wir das Sternchen-Platzhalterzeichen (*) mit dem Wert aus Zelle G3 mithilfe des kaufmännischen Und-Zeichens (&).
• Das vierte Argument [Wenn_nicht_gefunden] ist optional, daher lasse ich es weg.

Beispiel 4: Suche nach links

Suche von rechts nach links mithilfe von XLOOKUP

Ein Nachteil von VLOOKUP ist, dass die Suche nur rechts der Suchspalte funktioniert. Wenn Sie versuchen, Werte links davon zu finden, erhalten Sie den Fehlerwert #NV. Keine Sorge – XLOOKUP ist die ideale Lösung, um dieses Problem zu beheben.

XLOOKUP wurde entwickelt, um Werte sowohl links als auch rechts der Suchspalte zu finden – ganz ohne Einschränkungen. Damit erfüllt die Funktion perfekt die Anforderungen von Excel-Nutzern. Im folgenden Beispiel zeigen wir Ihnen den passenden Trick.

Angenommen, Sie haben eine Liste von Ländern mit ihren jeweiligen Telefonvorwahlen und möchten anhand einer bekannten Vorwahl den zugehörigen Ländernamen ermitteln.

Dazu durchsuchen Sie Spalte C und geben den entsprechenden Wert aus Spalte A zurück. Gehen Sie bitte wie folgt vor:

1. Geben Sie die folgende Formel in die leere Zelle G2 ein.

=XLOOKUP(F2,C2:C11,A2:A11)

2. Drücken Sie die Eingabetaste, um Ihr Ergebnis zu erhalten.

✓ Hinweis: Mit der XLOOKUP-Suche nach links können Sie die Kombination aus INDEX und VERGLEICH ersetzen, um Werte links der Suchspalte zu finden.

Suchen Sie Werte von rechts nach links mit wenigen Klicks

Für alle, die sich Formeln nicht merken möchten, empfehle ich hier eine nützliche Funktion: „Wert suchen“. Mit dieser Funktion können Sie innerhalb weniger Sekunden eine Suche von rechts nach links durchführen.

1. Wechseln Sie in Excel zur Registerkarte „Kutools“, wählen Sie „Erweiterte SVERWEIS“ und klicken Sie in der Dropdown-Liste auf „Von rechts nach links suchen“.

2. Konfigurieren Sie im Dialogfeld „Von rechts nach links suchen“ die folgenden Einstellungen:

• Geben Sie im Abschnitt „Such- und Ausgabebereich“ den Suchbereich und Platzierungsbereich der Liste an;
• Geben Sie im Abschnitt „Datenbereich“ Datenbereich ein und legen Sie anschließend „Schlüsselspalte“ und „Rückgabespalte“ fest;

3. Klicken Sie auf die Schaltfläche „OK“, um Ihr Ergebnis zu erhalten.

Beispiel 5: Vertikale oder horizontale Suche

Führen Sie eine vertikale oder horizontale Suche mit XLOOKUP durch

Als Excel-Nutzer kennen Sie wahrscheinlich die Funktionen VLOOKUP und HLOOKUP: VLOOKUP sucht vertikal in einer Spalte, während HLOOKUP horizontal in einer Zeile sucht.

Die neue XLOOKUP-Funktion vereint beides: Mit nur einer einzigen Syntax können Sie sowohl vertikal als auch horizontal suchen. Genial, oder?

Im folgenden Beispiel zeigen wir Ihnen, wie Sie mit nur einer einzigen XLOOKUP-Formel sowohl vertikal als auch horizontal suchen können.

Geben Sie in die leere Zelle E2 die folgende Formel ein und drücken Sie die Eingabetaste, um eine vertikale Suche durchzuführen und das Ergebnis zu erhalten.

=XLOOKUP(E1,A2:A13,B2:B13)

Geben Sie in die leere Zelle P2 die folgende Formel ein und drücken Sie die Eingabetaste, um eine horizontale Suche durchzuführen und das Ergebnis zu erhalten.

=XLOOKUP(P1,B1:M1,B2:M2)

Wie Sie sehen, ist die Syntax identisch – der einzige Unterschied zwischen den beiden Formeln liegt darin, dass Sie bei der vertikalen Suche Spalten und bei der horizontalen Suche Zeilen angeben.

Beispiel 6: Zweiwegsuche

Führen Sie eine Zwei-Wege-Suche mit XLOOKUP durch

Verwenden Sie immer noch die Funktionen INDEX und VERGLEICH , um einen Wert in einer zweidimensionalen Tabelle zu suchen? Entdecken Sie die verbesserte XLOOKUP-Funktion und erledigen Sie Ihre Aufgabe ganz einfach!

XLOOKUP ermöglicht eine doppelte Suche und findet den Schnittpunkt zweier Werte. Indem eine XLOOKUP-Funktion innerhalb einer anderen verschachtelt wird, liefert die innere XLOOKUP-Funktion eine ganze Zeile oder Spalte zurück, die dann als Rückgabearray an die äußere XLOOKUP-Funktion übergeben wird.

Angenommen, Sie haben eine Liste mit den Noten verschiedener Schüler in unterschiedlichen Fächern und möchten Kims Note in Chemie herausfinden.

Sehen wir uns an, wie wir mit der magischen XLOOKUP-Funktion diesen Trick umsetzen.

• Wir führen die „innere“ XLOOKUP-Funktion aus, um einen ganzen Spaltenbereich als Rückgabewert zu erhalten. Mit XLOOKUP(H2;B1:E1;B2:E10) lässt sich der Bereich mit den Chemie-Noten abrufen.
• Wir verschachteln die „innere“ XLOOKUP-Funktion in der „äußeren“ XLOOKUP-Funktion, indem wir die „innere“ XLOOKUP als Rückgabebereich in der vollständigen Formel verwenden.
• Daraus ergibt sich folgende endgültige Formel:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

• Geben Sie die oben genannte Formel in die leere Zelle H3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

Oder Sie gehen umgekehrt vor: Nutzen Sie die „innere“ XLOOKUP-Funktion, um sämtliche Fachnoten von Kim – also den gesamten Zeileninhalt – abzurufen. Anschließend verwenden Sie die „äußere“ XLOOKUP-Funktion, um daraus gezielt die Chemienote zu ermitteln.

• Geben Sie die folgende Formel in die leere Zelle H4 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP(H2;B1:E1;XLOOKUP(H1;A2:A10;B2:E10))

Die Zwei-Wege-Suchfunktion von XLOOKUP zeigt zudem perfekt, wie sie sowohl vertikal als auch horizontal sucht. Probieren Sie es gerne aus!

Beispiel 7: Benutzerdefinierte Meldung bei Nichtvorhandensein

Passen Sie die Meldung für nicht gefundene Werte mithilfe von XLOOKUP an

Genau wie andere Suchfunktionen gibt XLOOKUP bei fehlender Übereinstimmung den Fehlerwert #NV zurück – was für manche Excel-Nutzer verwirrend sein kann. Die gute Nachricht: Sie können diesen Fehler ganz einfach über das vierte Argument der XLOOKUP-Funktion abfangen und individuell behandeln.

Mit dem integrierten Argument [wenn_nicht_gefunden] können Sie eine benutzerdefinierte Meldung festlegen, um das Ergebnis #NV zu ersetzen. Geben Sie den gewünschten Text als optionales viertes Argument ein und setzen Sie ihn in doppelte Anführungszeichen (").

Wenn beispielsweise die Stadt Denver nicht gefunden wird, gibt XLOOKUP normalerweise den Fehlerwert #NV zurück. Nachdem wir das vierte Argument jedoch auf „Keine Übereinstimmung“ festgelegt haben, zeigt die Formel stattdessen diesen benutzerdefinierten Text an.

Geben Sie die folgende Formel in die leere Zelle F3 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP(E2,A2:A11,C2:C11,„No Match")

Passen Sie den #NV-Fehler mit einer praktischen Funktion an

Um den #NV-Fehler schnell durch Ihre benutzerdefinierte Meldung zu ersetzen, ist Kutools für Excel das perfekte Werkzeug in Excel. Mit der integrierten Funktion „0 oder #NV durch Leerzeichen oder einen bestimmten Wert ersetzen“ legen Sie einfach Ihre gewünschte Meldung für fehlende Werte fest – ohne komplexe Formeln erstellen oder auf XLOOKUP zurückgreifen zu müssen.

1. Wechseln Sie in Excel zur Registerkarte „Kutools“, wählen Sie „Erweiterte SVERWEIS“ und klicken Sie in der Dropdown-Liste auf „0 oder #NV durch Leerzeichen oder einen bestimmten Wert ersetzen“.

2. Konfigurieren Sie im Dialogfeld „0 oder #NV durch Leerzeichen oder einen bestimmten Wert ersetzen“ die gewünschten Einstellungen:

• Wählen Sie im Abschnitt „Such- und Ausgabebereich“ den Suchbereich und Platzierungsbereich der Liste aus;
• Aktivieren Sie dann die Option „0 oder #NV durch einen bestimmten Wert ersetzen“ und geben Sie den gewünschten Text ein;
• Wählen Sie im Abschnitt „Datenbereich“ den gewünschten Datenbereich aus und legen Sie anschließend die „Schlüsselspalte“ sowie die „Rückgabespalte“ fest.

3. Klicken Sie auf „OK“, um das Ergebnis zu erhalten. Sobald keine Übereinstimmung gefunden wird, erscheint die benutzerdefinierte Meldung.

Beispiel 8: Mehrere Werte

Geben Sie mehrere Werte mit XLOOKUP zurück

Ein weiterer Vorteil von XLOOKUP ist die Möglichkeit, für dieselbe Übereinstimmung gleichzeitig mehrere Werte zurückzugeben: Geben Sie einfach eine einzige Formel ein, um das erste Ergebnis zu erhalten – alle weiteren Rückgabewerte werden automatisch in die benachbarten leeren Zellen übertragen („Spill“).

Im folgenden Beispiel möchten Sie alle Informationen zur Schüler-ID „FG9940005“ abrufen. Der entscheidende Trick: Geben Sie im Formelargument *return_array* einen Bereich statt einer einzelnen Spalte oder Zeile an. In diesem Fall umfasst der Rückgabebereich B2:D9 drei Spalten.

Geben Sie die folgende Formel in die leere Zelle G2 ein und drücken Sie die Eingabetaste, um sämtliche Ergebnisse zu erhalten.

=XLOOKUP(F2;A2:A9;B2:D9)

In allen Ergebniszellen wird dieselbe Formel angezeigt. Sie können die Formel in der ersten Zelle bearbeiten – in den übrigen Zellen ist dies jedoch nicht möglich, da die Bearbeitungsleiste ausgegraut ist und somit keine Änderungen zulässt.

Alles in allem ist die XLOOKUP-Funktion zum Abrufen mehrerer Werte eine nützliche Verbesserung gegenüber VLOOKUP – Sie müssen nicht mehr für jede Formel einzeln die jeweilige Spaltennummer angeben. Daumen hoch!

Beispiel 9: Mehrere Kriterien

Führen Sie eine Suche mit mehreren Kriterien mithilfe von XLOOKUP durch

Ein weiteres beeindruckendes neues Feature von XLOOKUP ist die Möglichkeit, Suchvorgänge mit mehreren Kriterien durchzuführen. Der entscheidende Trick: Suchwert und Suchbereich werden innerhalb der Formel mithilfe des Operators „&“ separat verknüpft. Das folgende Beispiel verdeutlicht dies.

Sie möchten den Preis der mittelgroßen blauen Vase ermitteln. In diesem Fall sind drei Suchkriterien erforderlich, um eine Übereinstimmung zu finden. Geben Sie die folgende Formel in die leere Zelle I2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP(F2&G2&H2A2:A12&B2:B12&C2:C12;D2:D12)

✓ Hinweis: XLOOKUP kann Arrays direkt verarbeiten – eine Bestätigung der Formel mit STRG + UMSCHALT + EINGABE ist nicht erforderlich.

Suche - Mehrere Bedingungen suchen mit einer schnellen Methode

Gibt es eine noch schnellere und einfachere Methode als XLOOKUP, um in Excel eine Suche mit mehreren Kriterien durchzuführen? Kutools für Excel bietet eine beeindruckende Funktion namens „Suche – Mehrere Bedingungen suchen“. Damit führen Sie komplexe Suchen mit mehreren Kriterien in nur wenigen Klicks durch!

1. Wechseln Sie in Excel zur Registerkarte „Kutools“, wählen Sie „Erweiterte SVERWEIS“ und klicken Sie in der Dropdown-Liste auf „Suche – Mehrere Bedingungen suchen“.

2. Führen Sie im Dialogfeld „Suche – Mehrere Bedingungen suchen“ Folgendes durch:

• Wählen Sie im Abschnitt „Such- und Ausgabebereich“ den Bereich für den Suchwert und die Platzierungsbereich der Liste aus;
• Führen Sie im Abschnitt „Datenbereich“ die folgenden Schritte aus:
  • Wählen Sie nacheinander die entsprechenden Schlüsselspalte, die die Suchwertbereich enthalten, indem Sie die Strg-Taste gedrückt halten, im Feld „Primärschlüsselspalte“;
  • Geben Sie im Feld „Rückgabespalte“ die Spalte an, die den Rückgabewert enthält.

3. Klicken Sie auf „OK“, um Ihr Ergebnis zu erhalten.

√ Hinweis:

• Der Abschnitt „Ersetzen Sie das Ausgaberesultat, das nicht gefunden wird und ‚#N/A‘ zurückgibt, durch den angegebenen Wert“ im Dialogfeld ist optional – Sie können ihn entweder angeben oder weglassen.
• Die Anzahl der im Feld „Schlüsselspalte“ eingegebenen Spalten muss exakt der Anzahl der im Feld „Suchwertbereich“ eingegebenen Spalten entsprechen – und die Reihenfolge der Kriterien in beiden Feldern muss dabei lückenlos eins zu eins übereinstimmen.

Beispiel 10: Wert anhand der letzten Übereinstimmung finden

Rufen Sie das letzte übereinstimmende Ergebnis mit XLOOKUP ab

Um in Excel den letzten übereinstimmenden Wert zu finden, legen Sie das sechste Argument so fest, dass rückwärts gesucht wird.

Standardmäßig ist der Suchmodus in XLOOKUP auf 1 gesetzt – die Suche erfolgt also von oben nach unten. Der entscheidende Vorteil von XLOOKUP liegt jedoch darin, dass sich die Suchrichtung flexibel anpassen lässt. Mithilfe des optionalen Arguments [Suchmodus] steuern Sie die Suchreihenfolge: Legen Sie im sechsten Argument den Wert -1 fest, sucht XLOOKUP von unten nach oben.

Sehen Sie sich dazu das folgende Beispiel an: Wir möchten den letzten Verkauf von Emma in der Datenbank ermitteln.

Geben Sie die folgende Formel in die leere Zelle G2 ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

=XLOOKUP(F2;B2:B11;D2:D11;;;-1)

✓ Hinweis: Das vierte und fünfte Argument sind optional und wurden hier weggelassen. Lediglich das optionale sechste Argument wurde auf -1 gesetzt.

Suchen Sie den letzten übereinstimmenden Wert mühelos mit einem hervorragenden Werkzeug

Wenn Ihnen XLOOKUP nicht zur Verfügung steht und Sie sich zudem keine komplizierten Formeln merken möchten, nutzen Sie einfach die Funktion „Von unten nach oben suchen“ – so erledigen Sie das ganz problemlos.

1. Wechseln Sie in Excel zur Registerkarte „Kutools“, wählen Sie „Erweiterte SVERWEIS“ und klicken Sie in der Dropdown-Liste auf „Von unten nach oben suchen“.

2. Konfigurieren Sie im Dialogfeld „Von unten nach oben suchen“ die folgenden Einstellungen:

• Wählen Sie im Abschnitt „Such- und Ausgabebereich“ den Suchbereich und Platzierungsbereich der Liste aus;
• Wählen Sie im Abschnitt „Datenbereich“ den gewünschten Datenbereich aus und legen Sie anschließend die „Schlüsselspalte“ sowie die „Rückgabespalte“ fest.

3. Klicken Sie auf „OK“, um Ihr Ergebnis zu erhalten.

✓ Hinweis: Der Abschnitt „Ersetzen Sie das Ausgaberesultat, das nicht gefunden wird und ‚#N/A‘ zurückgibt, durch den angegebenen Wert“ ist im Dialogfeld optional – Sie können ihn entweder angeben oder weglassen.

Laden Sie die XLOOKUP-Beispieldatei herunter

XLOOKUP Beispiele.xlsx

Verwandte Artikel:

• Wie verwendet man INDEX und VERGLEICH effizient zusammen in Excel?
• Wie wendet man Fuzzy Match an, um in Excel ein Ergebnis mit ungefährer Übereinstimmung zu finden?
• Wie wendet man eine Zwei-Wege-Suche-Formel in Excel an?
• Wie sucht man in Excel Werte anhand mehrerer Kriterien?
• Wie sucht man in Excel Werte von rechts nach links?

Beste Office-Produktivitätstools

🤖	KUTOOLS AI-Assistent: Revolutionieren Sie Datenanalyse basierend auf:Intelligente Ausführung   |  Code generieren|  benutzerdefinierte Formeln erstellen  |  Daten analysieren und Diagramme erstellen|  Erweiterte Funktionen aufrufen…
	Beliebte Funktionen:Suchen, hervorheben oder Doppelte markieren   |  Leere Zeilen löschen   |  Spalten kombinieren oder Zellen ohne Datenverlust   |   Runden ohne Formel...
	Erweiterte SVERWEIS:VLookup mit mehreren Kriterien  |  VLookup mit mehreren Werten  |   VLookup über mehrere Arbeitsblätter hinweg   |   Fuzzy Match....
	Erweiterte Dropdown-Liste:Schnell eine Dropdown-Liste erstellen   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl-Dropdown-Liste....
	Spalten-Manager:Eine bestimmte Anzahl von Spalten hinzufügen|Spalten verschieben|Sichtbarkeitsstatus ausgeblendeter Spalten umschalten|Bereiche & Spalten vergleichen...
	Hervorgehobene Funktionen:Gitterfokus   |  Entwurf   |Erweiterte Formelleiste   | Arbeitsmappen- & Blatt-Manager   |  Ressourcenbibliothek(AutoText)|  Datumsauswahl   |  Arbeitsblätter konsolidieren  |  Verschlüsseln/Zellen entschlüsseln   | E-Mails per Liste versenden   |  Super Filter   |   Spezialfilter(Zellen mit fettgedruckter Schrift filtern/kursiv/durchgestrichen...) ...
	Top-15-Toolsets:12-Text-Tools(Text hinzufügen,Bestimmte Zeichen löschen, ...)|   50+-Diagramm-Typen(Gantt-Diagramm, ...)|   40+ Praktische Formeln(Alter basierend auf dem Geburtstag berechnen, ...)|   19-Einfüge-Tools(QR-Code einfügen,Bild aus Pfad einfügen, ...)|   12-Konvertierungs-Tools(In Wörter umwandeln,Wechselkursumrechnung, ...)|   7-Vereinigen/Aufteilen-Tools(Erweiterte Zeilen zusammenführen,Zellen aufteilen, ...)|... und mehr

Verwenden Sie Kutools in Ihrer bevorzugten Sprache – unterstützt Englisch, Spanisch, Deutsch, Französisch, Chinesisch und 40+ weitere Sprachen!

Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...

Weitere Details zu Kutools für Excel...Kostenloser Download...

---

Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher

• Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
• Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
• Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!

Weitere Details zu Office Tab...Kostenloser Download...

---

Alle Kutools-Add-Ins – ein Installationsprogramm

Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.

• Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
• Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
• Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
• 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
• Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins

Weitere Details zu Kutools for Office...Kostenloser Download...