Mir kommt die XVERWEIS Formel ein wenig wie ein Zauberstab vor mit dem man fast alles machen kann… sie ist aus meiner Sicht die wichtigste neue Formel in der Excel Landschaft seit der ersten Version von Excel!

Wie komme ich zu so einer Aussage?

Lies weiter und vielleicht wirst Du am Ende dieses Beitrags meine Meinung teilen!

Aber zuerst mal einen kurzen Rückblick auf die heutigen “Zauberformeln”…

Der SVERWEIS wird von vielen Excel Trainer und auch bei Anstellungsgesprächen als Know-How Gradmesser verwendet. Wer die Formel kennt, und einzusetzen weiss, wird als durchschnittlich kompetenter Excel User eingestuft. Wer die INDEX / VERGLEICH Formel Kombination kennt, kann sich als Excel User mit überdurchschnittlichem Wissen wähnen.

Doch der SVERWEIS wird jetzt hinfällig, denn in Zukunft genügt es den XVERWEIS zu kennen!

Der SVERWEIS geht in den wohlverdienten Ruhestand!

Was macht die XVERWEIS Funktion

Mit der XVERWEIS Formel kann man nach einem Wert suchen in einer Zeile, Spalte, oder in einer gesamten Tabelle (mit geschachtelten XVERWEIS Formeln). Sowohl exakte Werte wie auch dem nächst höherem oder tieferem Wert. Und die Tabelle muss dafür nicht mal sortiert sein! Wenn ein Wert mehrmals in einer Tabelle vorkommt, kann auch das entscheiden ob man die letzte oder erste Instanz, die gefunden wird, zurückbekommen möchte! XVERWEIS kann auch eine ganze Matrix zurückgeben… was Hilft, wenn man Summieren möchte über Zeilen oder Spalten hinweg.

Sie Formel ersetzt auf einen Schlag all diese anderen Formeln:

• SVERWEIS Formel
• HVERWEIS Formel
• VERWEIS Formel
• INDEX / VERGLEICH Formel Kombinationen

…zum Teil kann sie sogar folgende Formeln ersetzen in gewissen Situationen

• SUMMEWENNS
• SUMMENPRODUKT

Ein wahres BIEST einer Formel!

Ab welcher Excel Version gibt es den XVERWEIS?

Der XVERWEIS ist seit dem 28. August 2019 als Beta-Feature verfügbar und deshalb nur für einen Teil der Office Insider zu sehen. Es sollen jedoch alle Office 365 Versionen von Excel und die Online Excel Version die Funktion bekommen. Den aktuellen XVERWEIS Roll-Out Status gibt Microsoft auf Ihrer Webseite.

Syntax der XVERWEIS Formel

UPDATE: 07.11.2019 – Der angekündigte 6. Parameter wurde eingeführt… leider nicht am Ende, sondern an 4. Stelle.

Der Aufbau der Formel ist simpel und elegant, deshalb auch gut merkbar:

=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [Wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])

Suchkriterium: ist der Wert nach dem man sucht. Gleich wie beim Sverweis.

Suchmatrix: ist die Matrix in der Gesucht wird. Notabene: Es ist eine MATRIX… es kann also eine Spalte ODER eine Zeile sein! Zu vergleichen mit der ersten Spalte beim Sverweis.

Rückgabematrix: Die Matrix, also ein Zellen Bereich der auch mehr als eine Spalte oder Zeile gross sein kann, in dem man in der ersten Spalte oder Zeile das Suchkriterium findet. Bedingung ist dass die Suchmatrix und Rückgabematrix die gleiche Anzahl Spalten oder Zeilen hat.

Wenn_Nicht_Gefunden: Im wesentlichen erspart uns dieser Parameter die Notwendigkeit eine WENNFEHLER() Formel um die XVERWEIS Formel zu schreiben.

Vergleichsmodus: ob man den exakten, nächst kleineren, den nächst grösserem Wert, oder ein Platzhalter zurückbekommen möchte. Dieses Kriterium ist optional. Anders als beim Sverweis ist der exakte Wert jetzt als der Standard definiert, was heisst das man es in den meisten Fällen weglassen kann!

Suchmodus: Definiert in welcher Reihenfolge gesucht wird. Standard ist vom ersten zum letzten Element. Dieses Kriterium ist optional. Der Sverweis musste, wenn man nicht den genauen Wert zurückbekommen wollte, immer sortiert sein. Ansonsten bestand die Gefahr, dass das Resultat nicht stimmte. Damit ist jetzt Schluss! Der XVERWEIS lässt genau definieren welcher Wert zurückkommen soll.

Was die Formel alles kann sieht man am besten anhand von Beispielen.

Alle Beispiele habe ich auch noch zusätzlich in einem YouTube Video erklärt:

Beispiel 1: XVERWEIS ersetzt normalen SVERWEIS und HVERWEIS

Hier ersetzen wir die klasische Funktion des SVERWEIS…

…und hier das mit dem HVERWEIS:

Beispiel 2: Eine Suche nach Links ist mit XVERWEIS unproblematisch

Da die Rückgabematrix irgendwo stehen kann (und lediglich gleich wie die Suchmatrix sein muss) kann sie irgendwo stehen… also auch Links!

Beispiel 3: Vergleichsmodus

In diesen Beispielen zeige ich die unterschiedlichen Vergleichsmodi der Formel.

Die Vergleichsmodus Optionen sind wie folgt:

 0 = Genaue Übereinstimmung
-1 = Exakte Übereinstimmung oder nächst kleineres Element
 1 = Exakte Übereinstimmung oder nächst grösseres Element
 2 = Platzhalterzeichenübereinstimmung

Verlgeichsmodus 0, -1, 1: liefert exakten, nächst kleineren, oder nächst grösserem Wert

Zuerst die bereits bekannten Werte welche wir auch mit dem Sverweis bilden konnten… wenn auch auf mühsamere weise.

Bei der ersten Formel (1) bekommen wir dasselbe Resultat mit dem exakt gleichen Wert, der gesucht wird, wie wenn wir den SVERWEIS mit 0 erstellt hätten. In der XVERWEIS Formel können wir auch die 0 ganz weglassen da dies der Standard Option entspricht (hier also aus rein didaktischen Gründen aufgeführt)!

Die zweite Formel (2) gibt uns den nächst tieferem Wert, wenn wir das Konto 1003 suchen. Da es kein 2003 Konto gibt, liefert die Formel den Wert des Konto 1002.

Bei der dritten Formel (3), in der wir wiederum Konto 1003 suchen, ist es genau umgekehrt. Hier haben wir spezifiziert, dass es den nächst höherem Wert liefern soll, also den des Konto 1005.

Vergleichsmodus: 2 Platzhalter und Sonderzeichen

Die letzte Variante ist die des Platzhalters…

Die Option Werte die Platzhalter haben zu finden gab es auch bereits mit dem SVERWEIS. Beim XVERWEIS müssen wir jedoch spezifizieren dass wir tatsächlich die Platzhalter zeichen an und für sich suchen… dann liefert die Formel Resultate mit Platzhalter.

In unserem Beispiel:

1. Mit * (Sternchen) bekommen wir das erste Resultat das ein Platzhalterzeichen enthält
2. Mit ? (Fragezeichen) bekommen wir kein Resutlat…. Auch dann nicht wenn es ein Resutlat gäbe mit lediglich einem Buchstaben (hier nicht abgebildet)
3. Bei der Suche nach dem exakten Wert der ein Platzhalterzeichen enthält liefrt die Formel den gewünschten Wert.
4. Bei der Suche nach 11? Und 1* bekommen wir wiederum den erstn Wert in der Liste der ein Platzhalter enthält.

Diese Option ist sehr nützlich in Situationen in denen wir tatsächlich Listen druchsuchen in denen wir direkt nach Sonderzeichen / Platzhalter haben.

Beispiel 4: Suchmodus

In diesem Beispielen schauen wir uns die verschiedenen Suchmodi der XVERWEIS Formel an.

 1 = Von erstem zu letztem Element suchen (Standard Option)
-1 = Von letztem zu erstem Element suchen
 2 = Binärsuche (sortieren in aufsteigender Reihenfolge)
-2 = Binärsuche (sortieren in absteigender Reihenfolge)

Das frappante an dem neuen Suchmodus der Formel, zumindest wenn wir es mit dem SVERWEIS vergleichen, ist dass die Liste nicht mehr sortiert sein muss, um das richtige Ergebnis zu liefern.

Wichtig wird für die allermeisten User die beiden ersten Optionen sein.

Wie man sieht im Beispiel, wir können für den Ersten oder Letzen «1001» Wert suchen, und jeweils die richtige Antwort bekommen!

Die Binären-Suchmodi sind interessant bei grossen Datenmengen da diese extrem schnell sind… aber auch etwas schwieriger zum Einsetzen, zumal die Tabelle wieder sortiert werden muss unter diesen Optionen. Ich bin mich mit den Binären Optionen noch weiter am auseinandersetzen…

Beispiel 5: Ersetzt INDEX / VERGLEICH Formel Kombination

Die Index Vergleich Formel Kombination diente dazu, dass man sowohl die Spalte wie auch die Zeile eines zurückzugebenden Wertes automatisch bestimmen konnte.

Auch das kann die XERWEIS Formel blendend.

Der Aufbau der beiden verschachtelten XVERWEIS Formeln hier kurz erklärt:

=XVERWEIS(B11;A3:A7;XVERWEIS(B10;A3:M3;A3:M7))

Um diese Excel Formel zu verstehen fangen wir bei der inneren an (1):

In der inneren XVERWEIS Formel (1) suchen wir die gewünschte Spalte… also ähnlich als würden wir eine HVERWEIS Formel benutzen. Als Rückgabebereich ist jedoch nicht nur eine andere Zeile definiert, sondern ein ganzer Bereich (alle Gebietswerte für den Februar)

Die äussere XVERWEIS Formel (2) schränkt den Rückgabebereich aus der inneren Formel noch weiter ein, in dem es nur die Zeile zurückgibt für das Gebiet Süd das wir suchen.

Somit ist nur noch ein einziger Wert übrig… nämlich der Wert, bei dem sich die beiden Rückgabebereiche kreuzen: 117

Beispiel 6: Nach mehreren Kriterien gleichzeitig suchen

Der XVERWEIS lässt auch merhfach als Suchargumente zu!

Man muss lediglich wie abgebildet die gesuchten Kriterien zusammen verbinden (1) mit einem & (Ampersand) und danach dasselbe tun bei den Suchbereichen (2)!

Notabene: Die Formel muss nicht mal als Matrix Formel eingegeben werden! Der XVERWEIS ist bereits eine Matrix Formel!

Früher was die SUMMENPRODUKT Formel oder die SUMMEWENNS Formel notwendig wenn man mehrere Kriterien gleichzeitig auswerten wollte. Oder man hat sich eine Schlüssel Spalte gebildet für den SVERWEIS.

Auch diese Optionen sind mit dem XVERWEIS Überholt!

Beispiel 7: Summe eines gesuchten Bereiches zurückgeben

Weil der XVERWEIS eine Matrix Formel ist, kann die Formel auch ganze Bereiche als Resultate zurückgeben. Dies haben wir bisher erst im letzten Beispiel gesehen.

Aber diese Eigenschaft kann für weitere Tricks eingesetzt werden, wie in diesem Beispiel, indem ich die anfallenden Kosten eines Konten-Nummernkreises auswerte:

=SUMME(XVERWEIS(D4;$A$4:$A$10;$B$4:$B$10):XVERWEIS(E4;$A$4:$A$10;$B$4:$B$10))

Der «Trick» bei dieser Formel ist lediglich dass wir die zwei Bereiche welche die XVERWEIS Formeln zurückliefern zusammenfügen mit einem : (Doppelpunkt) und so einen neuen Bereich erstellen. Dieser kann dann wiederum einfach mit der SUMMEN Formel zusammenaddiert werden.

Beispiel 8: Mehrfachwerte zurückbekommen

Mit dem XVERWEIS können wir auch mehrere Werte gleichzeitig zurückbekommen. Bedingung hierfür ist dass die Zellen neben der Formel leer sind (sonst gibt’s ein #ÜBERALUF! Fehler… mehr dazu hier)

Die Formel wird hierfür wie folgt eingegeben:

=XVERWEIS(B11;A5:A8;B5:D8)

Excel meldet beim ersten mal auch gleich dass es sich hier um etwas neues handelt. Einmal bestätigt erscheint diese Meldung nicht mehr.

Der Rückgabewert ist somit automatisch die gesamte Breite der Rückgabematrix!

Dieser Trick der Formel dürfte beim erstellen von dynamischen Diagrammen ebenfalls so einiges vereinfachen!

Und zum Schluss noch ein wenig Historie wie es dazu kam…

Ich möchte noch kurz Hervorheben, dass die XVERWEIS Formel zustande gekommen ist durch eine User Anfrage bei Microsoft.

Wyn Hopkins, ein Excel MVP, hat auf der User Voice Platform von Microsoft danach gefragt. Drei Jahre später ist es bei uns auf dem Laptop.

Herzlichen Dank an Wyn dafür!

…und ein wenig Zukunftsmusik!

Joe McDaid, der Microsoft Excel Senior Programm Manager, hat am 8. Okt bekannt gegeben dass es in Zukunft noch ein 6. Argument geben wird.

XLOOKUP will be getting a 6th [if_not_found] argument. https://t.co/OcobdJ3ywi

— Joe McDaid (@jjmcdaid) October 7, 2019

Update: 07.11.2019

Der neue Parameter wurde am 03. Nov für “Insider” eingeführt. In der deutschen Excel Version ist leider noch ein kleiner Fehler. Die Parameter Bezeichnungen sind vertauscht, aber funktionieren tut die Formel überall gleich, egal welche Sprache!

Es ist wahrlich ein kleines Meisterwerk was uns da Microsoft geliefert hat!