XVerweis in Excel - einfach erklärt mit vielen Beispielen

Der König ist tot, lange lebe der König! Der SVerweis verabschiedet sich nach 34 jähriger Herrschaft im Excel Reich und macht Platz für den Nachfolger: der XVerweis ist da!

Kann es der Nachfolger mit dem bisherigen König aufnehmen, ja eventuell sogar einige Schwächen der Majestät beseitigen? 

Alle Vor- und Nachteile der neuen Formel in Excel erläutere ich in meinem Tutorial anhand von einigen Beispielen .

Spoiler: Der Neue hat es in sich :)

Nur verwendbar als Office 365 Nutzer. 

Mit den automatisierten Excel Dashboards der Excel Dashboard Mastery, brauchst du dank automatisierten Regeln nur noch minimalen Einsatz von Formeln. Komme in den Kurs und spare dir manuelle Excel Tätigkeiten ein!

Excels XVerweis Formel (Xlookup in der englischen Version) ist ein richtiger Allrounder. Mit ihm kannst du nicht nur nach einem Wert in einer Spalten suchen... nein sondern auch in Zeilen oder Tabellen. Egal ob horizontal (von oben nach unten, von unten nach oben) oder vertikal (von links nach rechts, von rechts nach links), der XVerweis findet, was er finden soll und spielt den gewünschten Wert zurück.

Somit macht er nicht nur den SVerweis überflüssig, sondern auch alle näheren Verwandten, wie: WVerweis, Verweis oder die mächtige Index/Vergleich Kombination.

Lange befand sich der XVerweis in der Preview Version für Microsoft Office Insiders. Letzte Woche wurde der XVerweis nun jedoch offiziell gelauncht und wird für alle Office 365 User freigeschaltet (bei User im monatlichen Update Rhytmus). Sollte der XVerweis bei euch trotz Office 365 Abo nicht auftauchen, kann es sein, dass dieser erst in einigen Monaten erscheint (halbjähriger Update Rhytmus). 

Schlechte Nachrichten für Excel Nutzer ohne Office 365: der Xverweis wird nicht verwendbar sein für Office 2019 und allen vorangegangen Excel Versionen - leider nur für Office 365 Abonnenten. 

Solltest du es nicht aushalten ohne den XVerweis, kannst du hier über meinen Affiliate Link bei Amazon das Office365 Abo besorgen.

Die Formel ist folgendermaßen aufgebaut:

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

Sieht auf dem ersten Blick erst einmal erschreckend lang aus. Du wirst jedoch in 95% der Fälle nur 3 der 6 Argumente verwenden müssen.

Den Sverweis und seine Freunde kannst du nämlich komplett ersetzten durch die 3 Pflichtargumente, der Rest ist Kür.

Kommen wir zu den einzelnen Positionen:

1. Suchkriterium: Der Wert nach dem du suchst (wie beim SVerweis)

2. Suchmatrix: Markiert die Spalte/Zeile, in der nach dem Suchkriterium gesucht werden soll

3. Rückgabematrix: Markiert die Spalte/Zeile, in der nach dem gewünschten Rückgabewert gesucht werden soll

Optional:

4. Wenn_Nicht_gefunden: Welcher Wert soll angezeigt werden, wenn das gesuchte Suchkriterium nicht in der Suchmatrix auftaucht. Ersetzt die Wennfehler() Formel.

5. Vergleichsmodus: Wenn der exakte Wert nicht verfügbar ist, kannst du auswählen, ob der nächst höhrere (Wert = 1), nächst kleinere (Wert= -1) oder ein Platzhalter (Wert = 2) zurückgespielt werden soll. Als Standard ist immer die genauer Übereinstimmung (Wert = 0) hinterlegt.

6. Suchmodus: Hier kannst du entscheiden in welcher Reihenfolge gesucht werden soll. Standardmäßig wird vom ersten bis zum letzten Element gesucht (Wert = 1), rückwärts geht das jedoch auch (Wert = -1)

Der einfache XVerweis kommt nur mit den 3 Pflichtangaben aus.

Achte bei der Auswahl der Suchmatrix (in diesem Fall Spalte B) darauf, das diese gleich lang ist, wie die Rückgabematrix (hier Spalte C). Wenn du nun anstatt des Vornamens lieber den Betrag (Spalte E) ausweisen möchtest, so musst du nur die Rückgabematrix nach rechts verschieben. 

Beim einfach XVerweis sind die optionalen Argumente auf Standard gesetzt.

Der klare Vorteil gegenüber dem einfachen SVerweis liegt darin, dass du nicht mehr die gesamte Matrix angeben und dabei dann die Rückgabespalte abzählen musst. Dies kann bei größeren Tabellen schon einmal nerven. Du markierst einfach die gewünschte Rückgabematrix und das wars. Zur Erinnerung hier einmal der einfach XVerweis vs. SVerweis mit obigen Beispiel.

Nun setzen wir mal den XVerweis mit allen 6 Argumenten ein. Hierzu möchten wir im ersten Szenario nur nach exakten Werten suchen und einen bestimmten Wert erhalten, sollte der Wert nicht auffindbar sein. Den Suchmodus belassen wir beim Standard (Suche vom ersten bis zum letzten Element = 1).

Wie ihr sehen könnt verwenden wir nun im Vergleich zum einfachen XVerweis die 3 optionalen Argumente:

• Wenn_Nicht_gefunden: Wenn Wert nicht gefunden, dann "nix da"

• Vergleichsmodus: Bitte gib mir nur exakte Werte = 0
• Suchmodus: Suche vom ersten bis zum letzten Element = 1

Wir nehmen wieder das Beispiel von eben und möchten nun anstatt "nix da" bei exakter Wertsuche nun die nächst höheren Wert erhalten. 

Bei der Suche nach der Frist (April 19) finden wir keinen exakten Treffer, also nehmen wir den nächst höheren Monat als Suchkriterium. In diesem Fall ist das der Mai 19 und dem dazu passenden Rückgabewert Homer.

Wenn der nächst kleinere Wert gesucht werden soll, verwenden wir anstatt "1" dann "-1". 

Moment mal, wie hieß der Kollege noch einmal? Manchmal passiert es einfach, dass wir uns nicht an das Suchkriterium erinnern. Für diesen Fall gibt es den XVerweis mit Platzhalter Funktion (Platzhalterzeichenübereinstimmung)

Für die Platzhalter könnt ihr wie in diesem Beispiel mit * arbeiten oder auch mit dem ?. Hier haben wir vergessen, ob es sich um Pinkman oder punkman oder wie auch immer handelt. Daher arbeiten wir mit zwei Platzhaltern. 

Für das 5. Argument, den Vergleichsmodus gibt ihr nun die 2 an für den Platzhaltermodus. Änderst du diesen Wert hier auf 0, bekommst du deinen festgelegten "wenn nicht gefunden" Wert.

Hier schlägt der XVerweis den SVerweis ein weiteres Mal. Denn bisher konnte hier nur die Index/Vergleich Formel aushelfen. Wir sind nun frei unseren XVerweis auch nach links suchen zu lassen.

Hierzu genügt es einfach deine Suchmatrix und die Rückgabematrix genau so auszusuchen, wie in dem Szenario nach rechts. Es bedarf keiner weiteren Spielereien. Natürlich kannst du den XVerweis nachts links auch mit der vereinfachten XVerweis Version mit nur 3 Argumenten ausführen.

Manchmal bekommst du eine Tabelle, die horizontal verläuft. Dies passiert oft bei Datenreihen (z.B. Umsatzverläufen). Auch hier konnte der SVerweis leider nicht mehr helfen. Für den XVerweis jedoch kein Thema.

In diesem Beispiel setzen wir den Xverweis bei der Suche nach unten ein. Hierzu markierst du einfach, wie in der normalen Version deine Matrizen, diesmal jedoch als Zeilen und nicht als Spalten.

Beim XVerweis nach oben, drehst du die beiden Matrizen einfach um und positionierst die Suchmatrix unter der Rückgabematrix.

Leider nicht. Wenn du also dein nächstes Excel Dashboard mit dem XVerweis aufpimpen willst, dann sei vorgewarnt, dass wohl nicht alle Kollegen etwas mit deiner Arbeitsmappe anfangen können. Die erhalten dann nämlich statt schöner Tabellen, nur Zellen voller Fehlermeldungen. Achte also vor dem Einsatz, ob dein Unternehmen/deine Kunden Office 365 Abos haben. Falls nicht, muss wohl wieder der alte König weiter herrschen.

Der XVerweis wird vielen Excelknechten (mir eingeschlossen) das Leben um einiges erleichtern, da er mehrere Formeln ablösen kann und nicht nur eine. Zudem ist die Formel so einfach zu verstehen, dass auch Einsteiger schnell Freude am Einsatz finden. Der größte Nachteil ist wohl, dass nur Office 365 User in den Genuss der neuen Formel kommen können und es daher zu Problemen kommen kann, wenn Dateien zwischen unterschiedlichen Organisationen geteilt werden. Ich hoffe, dass Microsoft sich hier in der Zukunft noch etwas einfallen lässt.