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 :)

Hier geht es zum XVerweis Video

XVerweis Datei zum downloaden und üben

Nur verwendbar als Office 365 Nutzer. 

Was genau macht der XVerweis?

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.

Wieso bekomme ich den XVerweis nicht in Excel angezeigt?

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.

Wie genau verwende ich den XVerweis?

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.

XVerweis Formel

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

Der einfache XVerweis kommt nur mit den 3 Pflichtangaben aus.

XVerweis Formel einfach

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 einfache XVerweis vs. SVerweis

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.

XVerweis einfach vs. Sverweis

Der komplette XVerweis: Exakter Wert nicht gefunden

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).

XVerweis Formel komplett wenn Wert nicht vorhanden

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

Der komplette XVerweis: Zeige mir den nächst höheren/kleineren Wert

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. 

XVerweis nächst höherer Wert suchen

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". 

Der komplette XVerweis mit Platzhalter

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)

XVerweis mit 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.

Der XVerweis nach links

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.

XVerweis nach links

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.

Der horizontale XVerweis nach oben oder unten

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.

Horizontaler XVerweis nach unten

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.

Ist der XVerweis rückwärtskompatibel zu älteren Versionen?

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.

Zusammenfassung

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. 

Weitere Tutorials

Kommentar schreiben

Kommentare: 12
  • #1

    DerLochhofener (Donnerstag, 06 Februar 2020 08:54)

    Warum muss ich als Newsletter Abonnent den Newsletter erneut abonnieren, wenn ich nur die Vorlage laden möchte, wo ist da deer Sinn?

  • #2

    Artur (Donnerstag, 06 Februar 2020 10:03)

    Hi Lochhofener, das brauchst du gar nicht. Du hast den Link zum Download Center bei der Anmeldung erhalten. Wenn du den nicht mehr hast, schreib mir gerne eine Mail: info@excel-koenig.de und ich schicke ihn dir noch einmal.
    Beste Grüße
    Artur

  • #3

    Christian F. (Donnerstag, 06 Februar 2020 11:05)

    Super tolle Funktion, ich habe darauf gewartet. noch viel besser Dein Tutorial. Sehr gut beschrieben und erklärt mit sehr schöne Bilder. Danke schön für diese Arbeit.

  • #4

    Artur (Donnerstag, 06 Februar 2020 20:56)

    Besten Dank Christian für dein positives Feedback!

  • #5

    Viktoria (Freitag, 07 Februar 2020 10:03)

    Super! Vielen Dank für die Info und die tolle Erklärung. Ich freue mich schon auf die Anwendung!

  • #6

    Artur (Freitag, 07 Februar 2020 11:15)

    Hi Viktoria, freut mich, dass dir der Beitrag gefällt.

  • #7

    P (Montag, 24 Februar 2020 00:29)

    Hey, bist Du sicher mit dem 365? Ich habe gerade meine Kaufversion auf dem Mac aktualisiert und in den offiziellen Versions release notes (https://docs.microsoft.com/en-us/officeupdates/release-notes-office-for-mac) steht, dass man es hiermit neu ist. Allerdings gibt er mir einen #name fehler raus, wenn ich die Formel verwenden will

  • #8

    Artur (Montag, 24 Februar 2020 17:43)

    Hi P, das ist zumindest die Antwort vom Office 365 Team, die ich erhalten habe. Wundert mich, das die release notes das aufführen es aber dann nicht verwendbar ist.

  • #9

    Karles (Dienstag, 21 April 2020 12:34)

    Ein wichtiges Detail kommt im Video leider nicht vor:
    Löscht mal im Blatt "Der einfache XVerweis" die Formeln aus den Spalten L bis N und ändert die Formel in der Zelle K11 auf =XVERWEIS($M$8;$B$8:$B$22;C8:F22)
    XVERWEIS kann eine Matrix von Ergebnissen liefern.

  • #10

    Thorsten (Donnerstag, 23 April 2020 08:15)

    Danke für Deinen ausführlichen Beitrag zum xverweis. Ich bin auch kein Freund von Abos - gleich gar nicht bei Microsoft. Zum Glück lässt sich der xverweis in etwa mit sverweis "nachempfinden", wenn ich den Spalten (und/oder Zeilen) Namen gebe, dann findet sverweis die Daten genauso - ohne eine Spaltennummer anzugeben, welche sich ja im laufe der Tabellenbearbeitung bekanntlich verschieben.
    Und wie Du schon richtig drauf hingewiesen hast: Die fehlende Abwärtskompatibilität ist ein echtes Hemmnis, wenn auch Verkäuferisch verständlich.

  • #11

    Netz (Donnerstag, 23 April 2020 15:48)

    Fehlt nur noch der Bezug zu einer anderen Datei. Aber ansonsten tolle Erklärung!

  • #12

    Barb (Mittwoch, 29 April 2020 14:18)

    Hallo, danke dir für die Erklärungen. Kann man mit dem XVerweis auch alle "*man" suchen und auf einmal anzeigen lassen? (in der Liste deines Beispiels stehen, glaub ich 3 Stück drin)