So verwenden Sie die XLOOKUP-Funktion in Excel

Die SVERWEIS-Funktion war schon immer eine der mächtigsten Funktionen von Excel. Damit können Sie nach Werten in der ersten Spalte einer Tabelle suchen und Werte aus Feldern auf der rechten Seite zurückgeben. Excel verfügt jedoch auch über eine Funktion namens XLOOKUP, mit der Sie in jeder Spalte oder Zeile nach einem Wert suchen und Daten aus einer anderen Spalte zurückgeben können.

So funktioniert XLOOKUP

Die XVERWEIS-Funktion ist viel einfacher zu verwenden als die SVERWEIS-Funktion, da Sie anstelle eines Werts für die Ergebnisspalte den gesamten Bereich angeben können.

Mit der Funktion können Sie auch sowohl eine Spalte als auch eine Zeile durchsuchen und den Wert in der sich überschneidenden Zelle suchen.

Die Parameter der XLOOKUP-Funktion sind wie folgt:

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

• Lookup-Wert: Der Wert, nach dem Sie suchen möchten
• lookup_array: Das Array (Spalte), das Sie durchsuchen möchten
• return_array: Das Ergebnis (Spalte), aus dem Sie einen Wert abrufen möchten
• match_mode (optional): Wählen Sie eine genaue Übereinstimmung (0), eine genaue Übereinstimmung oder den nächstkleineren Wert (-1) oder eine Platzhalterübereinstimmung (2).
• Suchmodus (optional): Wählen Sie, ob die Suche beginnend mit dem ersten Element in der Spalte (1), dem letzten Element in der Spalte (-1), binäre Suche aufsteigend (2) oder binäre Suche absteigend (-2) erfolgen soll.

Im Folgenden sind einige der gängigsten Lookups aufgeführt, die Sie mit der XLOOKUP-Funktion durchführen können.

So suchen Sie mit XLOOKUP nach einem einzelnen Ergebnis

Der einfachste Weg, XLOOKUP zu verwenden, besteht darin, mit einem Datenpunkt aus einer Spalte nach einem einzelnen Ergebnis zu suchen.

1. Diese Beispieltabelle ist eine Liste der von Vertriebsmitarbeitern eingereichten Bestellungen, einschließlich Artikel, Stückzahl, Kosten und Gesamtverkauf.

   

2. Wenn Sie den ersten Verkauf in der von einem bestimmten Vertriebsmitarbeiter eingereichten Liste finden möchten, können Sie eine XVERWEIS-Funktion erstellen, die die Spalte Vertreter nach einem Namen durchsucht. Die Funktion gibt das Ergebnis aus der Spalte Total zurück. Die XLOOKUP-Funktion dafür ist:

   =XVERWEIS(I2,C2:C44,G2:G44,0,1)
   

   • I2: Weist auf die Vertretername Suchzelle
   • C2: C44: Dies ist die Rep-Spalte, die das Lookup-Array ist
   • G2: G33: Dies ist die Total-Spalte, die das Rückgabe-Array ist
   • 0: Wählt eine genaue Übereinstimmung aus
   • 1: Wählt das erste Spiel in den Ergebnissen aus

3. Wenn du drückst Eintreten und geben Sie den Namen eines Vertriebsmitarbeiters ein. Die Zelle Gesamtergebnis zeigt Ihnen das erste Ergebnis in der Tabelle für diesen Vertriebsmitarbeiter an.

   

4. Wenn Sie nach dem letzten Verkauf suchen möchten (da die Tabelle in umgekehrter Reihenfolge nach Datum sortiert ist), ändern Sie das letzte XVERWEIS-Argument in -1, die die Suche ab der letzten Zelle im Lookup-Array startet und Ihnen stattdessen dieses Ergebnis liefert.

   

5. Dieses Beispiel zeigt eine ähnliche Suche, die Sie könnten mit einem SVERWEIS durchführen Funktion, indem Sie die Spalte Rep als erste Spalte der Nachschlagetabelle verwenden. Mit XLOOKUP können Sie jedoch nach jeder beliebigen Spalte in beide Richtungen suchen. Wenn Sie beispielsweise den Vertriebsmitarbeiter finden möchten, der die erste Binder-Bestellung des Jahres verkauft hat, verwenden Sie die folgende XVERWEIS-Funktion:

   =XVERWEIS(I2,D2:D44,C2:C44,0,1)
   

   • D2: Zeigt auf die Zelle für die Artikelsuche
   • D2: D44: Dies ist die Item-Spalte, die das Lookup-Array ist
   • C2: C44: Dies ist die Rep-Spalte, die das Rückgabe-Array links vom Lookup-Array ist
   • 0: Wählt eine genaue Übereinstimmung aus
   • 1: Wählt das erste Spiel in den Ergebnissen aus

6. Das Ergebnis ist diesmal der Name des Vertriebsmitarbeiters, der den ersten Binderauftrag des Jahres verkauft hat.

   

Führen Sie einen vertikalen und horizontalen Match mit XLOOKUP. durch

Eine weitere Funktion von XLOOKUP, die SVERWEIS nicht bietet, ist die Möglichkeit, sowohl eine vertikale als auch eine horizontale Suche durchzuführen, dh Sie können nach einem Element in einer Spalte und auch in einer Zeile suchen.

1. In der folgenden Beispieltabelle werden die Verkäufe für jeden Vertriebsmitarbeiter nach Quartalen aufgeteilt. Wenn Sie den Umsatz des dritten Quartals für einen bestimmten Vertriebsmitarbeiter ohne die XLOOKUP-Funktion anzeigen möchten, wäre diese Art der Suche schwierig.

   

2. Mit der XLOOKUP-Funktion ist diese Art der Suche einfach. Mit der folgenden XLOOKUP-Funktion können Sie nach den Verkäufen des dritten Quartals für einen bestimmten Vertriebsmitarbeiter suchen:

   =XVERWEIS(J2,B2:B42,XVERWEIS(K2,C1:H1,C2:H42))
   

   • J2: Verweist auf die Repräsentantensuchzelle
   • B2: B42: Dies ist die Item-Spalte, das ist das Spalten-Lookup-Array
   • K2: Zeigt auf die Suchzelle Quartal
   • C1: H1: Dies ist das Zeilen-Lookup-Array
   • C2: H42: Dies ist das Lookup-Array für den Dollarbetrag in jedem Quartal

   Diese verschachtelte XVERWEIS-Funktion identifiziert zuerst den Vertriebsmitarbeiter und die nächste XVERWEIS-Funktion identifiziert das gewünschte Quartal. Der Rückgabewert ist die Zelle, in der sich diese beiden schneiden.

3. Das Ergebnis für diese Formel ist der Quartalsgewinn für den Vertreter mit dem Namen Thompson.

   

Verwenden der XLOOKUP-Funktion

Die XLOOKUP-Funktion steht nur Office-Insider-Abonnenten zur Verfügung, wird aber in Kürze auf alle Microsoft 365-Abonnenten ausgerollt.

Wer die Funktion selbst testen möchte, kann Office Insider werden. Auswählen Datei > Konto, dann wählen Sie die Büro-Insider Dropdown-Liste zum Abonnieren.

Sobald Sie dem Office Insider-Programm beitreten, erhält Ihre installierte Excel-Version alle aktuellen Updates und Sie können die XVERWEIS-Funktion verwenden.