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:
- Mit * (Sternchen) bekommen wir das erste Resultat das ein Platzhalterzeichen enthält
- Mit ? (Fragezeichen) bekommen wir kein Resutlat…. Auch dann nicht wenn es ein Resutlat gäbe mit lediglich einem Buchstaben (hier nicht abgebildet)
- Bei der Suche nach dem exakten Wert der ein Platzhalterzeichen enthält liefrt die Formel den gewünschten Wert.
- 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.
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!
Im Hinblick auf das “Beispiel 8: Mehrfachwerte zurückbekommen” wäre es nunmehr noch interessant zu erfahren, wie ich den Zeitraum (Jan – Jun) variabel, bzw. dynamisch hinterlegen kann. In dem Beispiel wurde der Auswertungsbereich Jan – März durch die Hinterlegung von B5:D8 “fix” festgelegt.
Habt Ihr dafür auch eine Idee?
Du meinst, nach einem Zeitraumkriterium die Summe bilden zu können, z.B. für Nord die Summe für den Zeitraum Januar bis März oder März bis April oder Januar bis Mai?
Z.B.:
in B12 den Beginn des Zeitraumes eintragen
in B13 das Ende des Zeitraumes eintragen
in B14 dann die Auswertung mit
=SUMME(XVERWEIS(B11;A5:A8;XVERWEIS(B12;B4:F4;B5:F8):XVERWEIS(B13;B4:F4;B5:F8)))
Durch den 2. Xverweis legst Du quasi einen “Startpunkt” und den 3. Xverweis quasi einen “Endpunkt” für einen (Zeit-)Bereich fest. Das es sich um einen Bereich handelt ist durch den “:” erkennbar ;-)
Hi,
vielen Dank für den tollen Artikel!
Ich hätte zwei Fragen..
ich habe waagerecht als Überschriften Abteilungsnamen (Zeile 19) und in der nächsten Zeile waagerecht die dazugehörigen Mitarbeiter (Zeile 20).
Wenn ich nach einer Abteilung suche möchte ich gerne alle Mitarbeiter als Ergebnis angezeigt bekommen. Mit dem XVerweis habe ich es so hinbekommen:
=XVERWEIS($F$19;$B19:$H19;C20:I20;;;)
Wenn ich das nach rechts ziehe passiert daraus
=XVERWEIS($F$19;$B19:$H19;D20:J20;;;)
Ungünstig, dass er von C auf D springt. Wenn ich jedoch in der zweiten Formel C und I eintrage erhalte ich kein Ergebnis mehr.
XVERWEIS($F$19;$B19:$H19;B20:H20;;;)
Gibt es eine Möglichkeit mein Problem auch ohne XVerweis zu lösen? Ich dachte an mögliche Verwendungen von Index/Vergleich? Auf dem anderen Rechner gibt es die Funktion noch nicht.
Hallo Nora,
Die INDEX/VERGLEICH Formelkombination ist die richtige Methode um das gleiche zu erziehlen mit früheren Excel Versionen welche den XVERWEIS noch nicht haben. Ich habe auf dem Blog ein Gastartikel der erklärt wie man diese Kombination einsetzt! Excel Index/Vergleich Kombination
Ich liebe den XVERWEIS, seit ich ihn durch Zufall gefunden habe.
Ich suchte nach einer Beschreibung zum SVERWEIS und Microsoft himself hat mich erfolgreich zum XVERWEIS geschickt.
Microsoft fürt inzwischen öfters wieder neue Formeln ein, aber selten eine die wirklich markant besser ist als der Vorgänger. Der XVERWEIS darf jedoch mit der Latte gemessen werden! :- )
Anmerkung zum Beispiel 6: Nach mehreren Kriterien gleichzeitig suchen
Die Kombination von Felder in der Art “A:A&B:B” sieht man bei Excel ja allgemein oft als Tipp. Allerdings wird dabei übersehen, dass es sich um eine Zeichenkettenverknüfung handelt. Somit können in ungünstigen Konstellationen falsche Werte herauskommen.
Wenn z. B. A1 = “AB” und B1 = “C” ist, kommt auch ein Treffer dabei heraus, wenn man eigentlich Zeilen sucht, bei denen in der Spalte A der Wert “A” steht und in Spalte B der Wert “BC”, denn beides ergibt zusammengefügt “ABC”.
Abhilfe schafft z. B. die Nutzung eines Trennzeichen: A:A&”_”&B:B
Das gewählte Trennzeichen sollte dann natürlich nicht in den eigentlichen Daten vorkommen.
Ein sehr guter Tipp und Hinweis!!! Danke Dir dafür!
Besten Dank für den Bericht!
Folgende Bemerkungen:
● Weil der 6. Parameter nicht am Ende, sondern an 4. Stelle eingefügt wurde, stimmen einige Formeln (in Beispiel 3 + 4) in der Beispieldatei und in obigen Ausführungen nicht mehr (Parameter der 4. und 5. Stelle sind nun an der falschen Position in den Formeln)
● Dass es nun möglich ist, Mehrfachwerte zurückzubekommen (Beispiel 5), oder mehrere Kriterien zurückzugeben (Beispiel 6) ist eine tolle Sache!
● Auch die Möglichkeit die Summe eines gesuchten Bereiches zurückzugeben (Beispiel 7) finde ich cool, obwohl die Formel recht kompliziert wird, wenn man die Formel dynamisch gestalten möchte (s. Beispiel von Michael Kratt). Hier wäre eine Art SUMMENWENNS-Formel, die über Spalten und Zeilen funktioniert, hilfreicher
● Für mich ist die aktuell genialste Formelkombination INDEX und VERGLEICH. In Beispiel 5 wird diese mit XVERWEIS gelöst. Ich gebe zu, XVERWEIS ist ein wenig kürzer, die Index-Formel ist aber – aus meiner Sicht – logischer (zuerst wird die Matrix, dann die Formel für die Zeile und zum Schluss die Formel für die Spalte erfasst). Oder übersehe ich etwas?
Nachstehend die Formeln im Vergleich:
=XVERWEIS(B11;A3:A7;XVERWEIS(B10;A3:M3;A3:M7))
=INDEX(B4:M7;VERGLEICH(B11;A4:A7;0);VERGLEICH(B10;B3:M3;0))
Weil ich sehr viel Gutes über XVERWEIS gelesen hatte, waren meine Erwartungen entsprechend sehr hoch. Nun habe ich (dank Office 365) die Möglichkeit die Funktion zu verwenden. Leider wurde meine Erwartungen nicht erfüllt: dies v.a. wegen meine Punkte 3 + 4, sowie die Ausführungen von IfM001 (eine Formel muss immer das richtige Ergebnis zurückgeben!). Dass zudem Microsoft neue Parametern “hereinquetscht” (und damit zu diesem Zeitpunkt bestehende Formeln “verfälscht”) geht gar nicht – ich hoffe, dass dies einmalig war, weil die Formel noch nicht definitiv fertigerstellt war.
Hi,
sehr gut und nachvollziehbar zusammengefasst.
Eine kleine Nachfrage meinerseits:
Wenn ich einen kombinierten XVerweis habe der kein Ergebnis zurückgibt sage ich Excel mit “-1”, dass der nächst kleinere Wert verwendet werden soll.
Ab jetzt wird aber das 2te Suchkriterium außer Acht gelassen.
Es wird also nach dem nächst kleineren Wert von Suchkriterium A gesucht und der entsprechende Wert zurückgegeben.
Suchkriterium B wird aber nun nicht mehr berücksichtigt.
Gibt es hierfür eine Lösung?
Gute Nachricht an alle:
Ab Februar 2022 gibt es nicht nur die Funktion XVERWEIS als UDF für ältere Excel-Versionen (2007 – 2019), sondern auch eine Funktion XVERWEIS2, die noch mehr kann als XVERWEIS:
1. Suche nach mehreren Kriterien
2. Wildcards in den Suchkriterien
3. Optional Rückgabe aller Treffer
Download und Beispiele:
https://hermann-baum.de/excel/hbSort/de/xverweis.php
Gruß, Hermann