Die SVERWEIS-Funktion war schon immer eine der mächtigsten Funktionen von Excel. Damit können Sie nach Werten in der ersten Sp alte einer Tabelle suchen und Werte aus Feldern auf der rechten Seite zurückgeben. Aber Excel hat auch eine Funktion namens XVERWEIS, mit der Sie in jeder Sp alte oder Zeile nach einem Wert suchen und Daten aus jeder anderen Sp alte 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 Ergebnissp alte den gesamten Bereich angeben können.
Mit dieser Funktion können Sie auch sowohl eine Sp alte als auch eine Zeile durchsuchen und den Wert in der sich überschneidenden Zelle finden.
Die Parameter der XLOOKUP-Funktion lauten wie folgt:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Der Wert, nach dem Sie suchen möchten
- lookup_array: Das zu durchsuchende Array (Sp alte)
- return_array: Das Ergebnis (Sp alte), aus dem Sie einen Wert abrufen möchten
- match_mode (optional): Wählen Sie eine exakte Übereinstimmung (0), eine exakte Übereinstimmung oder den nächstkleineren Wert (-1) oder eine Wildcard-Übereinstimmung (2) aus.
- search_mode (optional): Wählen Sie, ob die Suche beginnend mit dem ersten Element in der Sp alte (1), dem letzten Element in der Sp alte (-1), binäre Suche aufsteigend sein soll (2) oder binäre Suche absteigend (-2).
Im Folgenden finden Sie einige der häufigsten Suchen, 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, anhand eines Datenpunkts aus einer Sp alte nach einem einzelnen Ergebnis zu suchen.
-
Diese Beispieltabelle ist eine Liste von Bestellungen, die von Vertriebsmitarbeitern eingereicht wurden, einschließlich Artikel, Anzahl der Einheiten, Kosten und Gesamtverkauf.
Image -
Wenn Sie den ersten Verkauf in der Liste finden möchten, die von einem bestimmten Vertriebsmitarbeiter übermittelt wurde, können Sie eine XLOOKUP-Funktion erstellen, die die Sp alte „Rep“nach einem Namen durchsucht. Die Funktion gibt das Ergebnis aus der Sp alte Gesamt zurück. Die XLOOKUP-Funktion dafür lautet:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Zeigt auf die Suchzelle Rep Name Suchzelle
- C2:C44: Dies ist die Rep-Sp alte, die das Lookup-Array ist
- G2:G33: Dies ist die Gesamtsp alte, die das Rückgabearray ist
- 0: Wählt eine exakte Übereinstimmung
- 1: Wählt den ersten Treffer in den Ergebnissen
-
Wenn Sie Enter drücken und den Namen eines Vertriebsmitarbeiters eingeben, zeigt Ihnen die Zelle Gesamtergebnis das erste Ergebnis in der Tabelle für diesen Vertriebsmitarbeiter.
Image -
Wenn Sie nach dem letzten Verkauf suchen möchten (da die Tabelle nach Datum in umgekehrter Reihenfolge geordnet ist), ändern Sie das letzte XLOOKUP-Argument in - 1, was gestartet wird die Suche ab der letzten Zelle im Lookup-Array und liefern Ihnen stattdessen dieses Ergebnis.
Image -
Dieses Beispiel zeigt eine ähnliche Suche, die Sie mit einer SVERWEIS-Funktion durchführen könnten, indem Sie die Rep-Sp alte als erste Sp alte der Nachschlagetabelle verwenden. Mit XLOOKUP können Sie jedoch in beiden Richtungen nach einer beliebigen Sp alte suchen. Wenn Sie beispielsweise den Vertriebsmitarbeiter finden möchten, der die erste Binder-Bestellung des Jahres verkauft hat, würden Sie die folgende XLOOKUP-Funktion verwenden:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Verweist auf die Artikelsuchzelle
- D2:D44: Dies ist die Item-Sp alte, die das Lookup-Array ist
- C2:C44: Dies ist die Rep-Sp alte, die das Rückgabe-Array links vom Lookup-Array ist
- 0: Wählt eine exakte Übereinstimmung
- 1: Wählt den ersten Treffer in den Ergebnissen
-
Diesmal ist das Ergebnis der Name des Vertriebsmitarbeiters, der die erste Ordnerbestellung des Jahres verkauft hat.
Image
Vertikalen und horizontalen Abgleich mit XLOOKUP durchführen
Eine weitere Funktion von XLOOKUP, die SVERWEIS nicht kann, ist die Möglichkeit, sowohl eine vertikale als auch eine horizontale Suche durchzuführen, was bedeutet, dass Sie nach einem Element in einer Sp alte und auch in einer Zeile suchen können.
Diese duale Suchfunktion ist ein effektiver Ersatz für andere Excel-Funktionen wie INDEX, MATCH oder WLOOKUP.
-
In der folgenden Beispieltabelle werden die Verkäufe für jeden Vertriebsmitarbeiter nach Quartalen aufgeteilt. Wenn Sie die Verkäufe des dritten Quartals für einen bestimmten Vertriebsmitarbeiter sehen möchten, wäre diese Art der Suche ohne die XLOOKUP-Funktion schwierig.
Image -
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 Rep-Suchzelle
- B2:B42: Dies ist die Item-Sp alte, die das Sp alten-Lookup-Array ist
- K2: Zeigt auf die Suchzelle des Quartals
- C1:H1: Dies ist das Zeilen-Lookup-Array
- C2:H42: Dies ist das Lookup-Array für den Dollarbetrag in jedem Quartal
Diese verschachtelte XLOOKUP-Funktion identifiziert zuerst den Vertriebsmitarbeiter, und die nächste XLOOKUP-Funktion identifiziert das gewünschte Quartal. Der Rückgabewert ist die Zelle, in der sich diese beiden schneiden.
-
Das Ergebnis dieser Formel ist das Ergebnis des ersten Quartals für den Vertreter mit dem Namen Thompson.
Image
Verwendung der XLOOKUP-Funktion
Die XVERWEIS-Funktion ist nur für Office Insider-Abonnenten verfügbar, wird aber bald für alle Microsoft 365-Abonnenten eingeführt.
Wenn Sie die Funktion selbst testen möchten, können Sie Office Insider werden. Wählen Sie File > Account und dann zum Abonnieren das Dropdown-Menü Office Insider aus.
Sobald Sie dem Office-Insider-Programm beitreten, erhält Ihre installierte Version von Excel die neuesten Updates und Sie können die XLOOKUP-Funktion verwenden.
