Goodbye SVERWEIS, willkommen XVERWEIS! 23

Diese neue Funktion ist wie der SVERWEIS, nur um Klassen besser!
 

Gefürchtet, geliebt und irgendwie unausweichlich. Die allseits bekannte SVERWEIS-Funktion könnte auch aus dem Buch „Der Herr der Ringe“ stammen:

„Eine Funktion, sie zu knechten, sie alle zu finden, ins Dunkel zu treiben und ewig zu binden“

So fühlt sich zumindest mancher Excel-Einsteiger, wenn er Bekanntschaft mit dem SVERWEIS macht. Wenn man ihn jedoch einmal gemeistert hat, kommt man nur schwer wieder davon los.

Nun, du kannst den SVERWEIS getrost in den Schicksalsberg werfen. Denn jetzt gibt es den

XVERWEIS

Was diese neue Excel-Mehrzweckwaffe kann, erfährst du in diesem Artikel.

Die Beschränkungen des SVERWEIS

Wer die SVERWEIS-Funktion häufig einsetzt, kennt auch den größten Schwachpunkt: Man kann in der Zieltabelle nur von links nach rechts suchen. Befindet sich die gesuchte Information links von der Suchspalte, kann man den SVERWEIS leider nicht nutzen. Hier kommt dann in der Regel das Dreamteam INDEX + VERGLEICH zum Einsatz.

Aber das ist nicht die einzige Einschränkung. Vergisst man beispielsweise, den dritten Parameter in SVERWEIS auf FALSCH bzw. 0 zu setzen (und das passiert nicht nur Excel-Anfängern!), liefert die Funktion in unsortierten Zieltabellen falsche Ergebnisse:

Ein vergessener Parameter im SVERWEIS ist gefährlich

Ein vergessener Parameter im SVERWEIS ist gefährlich

Auf diesen Parameter kann man nur dann verzichten, wenn die zu durchsuchende Tabelle aufsteigend sortiert ist. In allen anderen Fällen muss der dritte Parameter auf FALSCH (= exakte Übereinstimmung) gesetzt werden:

FALSCH ist fast immer richtig!

FALSCH ist fast immer richtig!


Merke:
In 99,9% der Fälle ist beim SVERWEIS FALSCH richtig. Klingt komisch, ist aber so.

Dritte Einschränkung: Die Funktion kann immer nur einen Ergebniswert zurückliefern. Wenn man beispielsweise zu einer gesuchten Kundennummer sowohl den Vornamen als auch den Nachnamen ausgeben möchte, muss man zwei angepasste SVERWEIS-Funktionen verwenden:

Mehrere Werte erfordern mehrere Formeln

Mehrere Werte erfordern mehrere Formeln


Bei zwei Feldern geht das ja noch, wenn jedoch neben den Namen auch noch die Adresse und drei weitere Felder benötigte werden, kann das schon etwas mühsam sein.

Vierte Einschränkung:
Wenn der SVERWEIS kein Ergebnis findet, liefert er den unschönen Fehlerwert #NV! zurück. Möchte man den vermeiden, muss die Formel mit der zusätzlichen WENNFEHLER-Funktion umschlossen werden.

Und zu guter Letzt durchsucht der SVERWEIS nur die erste Spalte und liefert dann einen Wert aus einer Zelle daneben. Hat man jedoch einen vertauschten Tabellenaufbau und muss die erste Zeile durchsuchen und möchte den Wert aus einer Zelle darunter, muss man auf eine andere Funktion zurückgreifen. Nämlich den selteneren WVERWEIS.

Aber jetzt kommt die gute Nachricht:
Der neue XVERWEIS hat keine dieser Einschränkungen! Und er kann noch einiges mehr.

Bevor ich dir zeige, wie genial diese neue Funktion ist, muss ich auf die einzige echte Einschränkung hinweisen. In den Genuss dieser Funktion kommen nur Anwender von Office 365. Wer den monatlichen Kanal von Office 365 nutzt, hat diese Funktion jetzt schon zur Verfügung, für Anwender des halbjährlichen Kanals wird sie ab Juli 2020 ausgerollt.

Version und Kanal prüfen

Version und Kanal prüfen


Wer jedoch überhaupt kein Office 365 einsetzt, muss auf den XVERWEIS leider ganz verzichten.

Darf ich vorstellen: Der XVERWEIS!

Nun aber zum XVERWEIS (die Datei mit den folgenden Beispiele kannst du dir hier herunterladen). Die allgemeine Syntax lautet so:
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht gefunden]; [Vergleichsmodus]; [Suchmodus])

Die erste Besonderheit ist, dass anders als beim SVERWEIS hier zwischen einer Suchmatrix (also der zu durchsuchenden Spalte) und einer Rückgabematrix (also der Spalte mit den gewünschten Ergebnissen) unterschieden wird. Und damit wird bereits der größte Schwachpunkt des SVERWEIS überwunden, denn man kann nun auch Ergebnisse aus Spalten zurückgeben, die links von der Suchspalte liegen:

XVERWEIS in seiner einfachsten Form

XVERWEIS in seiner einfachsten Form

Wer sich das Beispiel im Bild oben genauer ansieht, dem wird auffallen, dass ich hier auf den im SVERWEIS fast immer notwendigen FALSCH-Parameter verzichtet habe. XVERWEIS geht nämlich schlauerweise davon aus, dass nach einer genauen Übereinstimmung gesucht werden soll, wenn der optionale Parameter „Vergleichsmodus“ nicht angegeben ist!

Integrierte Fehlerbehandlung

Sehr praktisch ist auch der optionale dritte Parameter. Damit kann man festlegen, was passieren soll, wenn der gesuchte Wert nicht gefunden wurde:

Die integrierte Fehlerbehandlung

Die integrierte Fehlerbehandlung


Die beim SVERWEIS notwendige zusätzliche WENNFEHLER-Funktion ist hier also überflüssig!

Große Flexibilität beim Vergleichsmodus

Wie wir ja schon gesehen haben, wird beim Weglassen des Vergleichsmodus-Parameters eine exakte Übereinstimmung angenommen. Darüber hinaus sind aber noch weitere Möglichkeiten für den Vergleichsmodus gegeben.

  • 0 = Exakte Übereinstimmung. Gilt auch, wenn der Parameter weggelassen wird
  • -1 = Exakte Übereinstimmung oder nächst kleineres Element
  • 1 = Exakte Übereinstimmung oder nächst größeres Element
  • 2 = Platzhalterzeichenübereinstimmung

So lassen sich mit dem Wert 1 beispielsweise ganz problemlos Staffelpreislisten realisieren:

Verschiedene Vergleichsmethoden

Verschiedene Vergleichsmethoden

Und mit dem Wert 2 ist sogar noch eine Joker-Suche möglich. Angenommen, wir wissen nicht die genau Schreibweise des Verkäufers Maier. Dann setzen wir einfach zwei Fragezeichen als Platzhalter ein und verwenden den Wert 2 im Parameter für den Suchmodus:

Suche mit Joker-Zeichen

Suche mit Joker-Zeichen


Ein Fragezeichen steht dabei für genau ein beliebiges Zeichen. Für eine beliebige Anzahl von beliebigen Zeichen kann auch der Stern (*) eingesetzt werden.

Horizontale Suche

Bisher war, wie eingangs schon beschrieben, für eine horizontale Suche die WVERWEIS-Funktion notwendig. Darauf kann man beim XVERWEIS verzichten, denn der kann in alle Richtungen arbeiten:

XVERWEIS ersetzt auch WVERWEIS

XVERWEIS ersetzt auch WVERWEIS

Horizontale und vertikale Suche

Und wem das noch nicht reicht, kann durch zwei verschachtelte XVERWEIS-Funktionen seine Tabelle auch in beide Richtungen durchsuchen:

Suche in beide Richtungen

Suche in beide Richtungen

Eine einzige Formel liefert mehrere Werte

Anders als SVERWEIS, der immer nur einen Rückgabewert liefert, kann der XVERWEIS in einem Rutsch gleich mehrere Felder zurückgeben:

Eine Formel liefert mehrere Werte

Eine Formel liefert mehrere Werte

Hier kommt eine spezielle Funktionalität zum Tragen, die auch in anderen neuen Excel-Funktionen Einzug hält: Die sogenannten dynamischen Arrays. Einen kleinen Einblick dazu hatte ich schon ein zwei anderen Artikeln gegeben:

Ich hoffe, die gezeigten Beispiele haben dir einen kleinen Vorgeschmack auf das gegeben, was mit dieser wunderbaren Funktion alles möglich ist. Natürlich muss man, wie generell bei jeder neuen Funktion die fehlende Abwärtskompatibilität beachten. Wenn du also in einer Firmenumgebung arbeitest, die mit unterschiedlichen Excel-Versionen arbeitet und Dateien mit Kollegen austauschen musst, solltest du sehr vorsichtig sein und besser noch auf den XVERWEIS verzichten.

In einer homogenen Office-365-Landschaft oder als Einzelkämpfer wirst du jedoch den SVERWEIS wahrscheinlich demnächst in die Tonne treten 🙂

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

23 Gedanken zu “Goodbye SVERWEIS, willkommen XVERWEIS!

  • Peter

    Herzlichen Dank für die bisherigen 250 Newsletter. Ich habe bisher alle von denen gelesen und auch als nicht Excedl-Anfänger kann man immer was rausnehmen… Klasse Tipp zum XVERWEIS. Ich bin begeistert….
    Viele Grüße Peter

  • Barbara Wenger

    Super, herzlichen Dank! Der Xverweis allein ist ja schon klasse, aber mit Deiner Tabelle und den Beispielen konnte ich ohne Probleme nachvollziehen, wie der Xverweis abläuft, ein extra Beschrieb war bei so einem guten Beispiele-Aufbau nicht nötig …..
    Herzliche Gratulation zum 250 x Newsletter!
    Freue mich auf die nächsten 🙂
    Herzliche Grüsse, Barbara

    • Martin Weiß Autor des Beitrags

      Hallo Barbara,

      vielen Dank, ich werde hart an den nächsten 250 arbeiten 🙂

      Schöne Grüße,
      Martin

  • Michael Risse

    Herzlichen Glückwunsch zum 250ten Newsletter. Ganz lieben Dank für die viele Mühe, die Du Dir machst.
    Habe schon die meisten meiner Programme durch XVERWEIS vereinfachen können. Einfach Super, vor Allem Deine Beispiele und Erläuterungen.

    Bitte, bitte weiter so!!!!!!!

    Micha

    • Martin Weiß Autor des Beitrags

      Hallo Micha,

      das freut mich sehr, dass die Artikel gut ankommen. Und natürlich wird es weitergehen 🙂

      Schöne Grüße,
      Martin

  • Annette

    Hallo Martin, Glückwunsch zum 250. Newsletter. Du hast damit meine Excel-Phobie geheilt und ich bin sogar Pivot-Fan geworden. Weiter so 🙂
    Der neue XVERWEIS ist klasse. Vielen Dank dafür und auch für die Beispieldatei zum Üben. Eine echte Verbesserung!

    Viele Grüße
    Annette

    • Martin Weiß Autor des Beitrags

      Hallo Annette,

      eine geheilte Excel-Phobie und ein Pivot-Fan: was will ich mehr!
      Vielen Dank für dieses schöne Feedback 🙂

      Martin

  • Michael Koulen

    Guten Tag Herr Weiß,

    der XVERWEIS wird sicherlich der Zukunft gehören.
    Ob dieser bereits fehlerfrei funktioniert oder doch noch gewisse Kinderkrankheiten hat erscheint mir fraglich.
    Somit wäre dies sicherlich ein Grund um vorerst einmal darauf zu verzichten.

    Hinzu kommt sicherlich auch das nicht dieser nur unter Office 365 zur verfügung steht, allerdings abhängig vom jeweiligen Abonnentenmodel. Sofern dies ausschließlich zur Eigenbedarf eingesetzt wird könnte dies Sinn machen.
    Sofern aber Daten und Excel-Dateien weiter gegeben werden sollen wäre der Wechsel zum XVERWEIS sicherlich verfrüht.

    • Martin Weiß Autor des Beitrags

      Hallo Herr Koulen,

      für etwaige Kinderkrankheiten sehe ich jetzt das Risiko persönlich als nicht so hoch. Aber ich geben Ihnen beim zweiten Punkt absolut Recht: in dem Moment, wo man Daten weitergeben muss und beim Empfänger keine Office-365-Umgebung erwarten kann, muss man auf diese Funktion aus Kompatibilitätsgründen definitiv verzichten. Und das wird sicherlich noch eine ganze Weile der Fall sein.

      Schöne Grüße,
      Martin

  • Reinhard Hess

    Hallo Martin,
    für alle, die kein Office365 haben: ich habe ich mir mal vor einiger Zeit eine Alternative gebastelt.

    {=INDEX(wertematrix;
    MAX((2*(suchmatrix=suchwert)-1)*(ZEILE(suchmatrix)-(ZEILE(suchmatrix_zelle1)-1)));
    MAX((2*(suchmatrix=suchwert)-1)*(SPALTE(suchmatrix)-(SPALTE(suchmatrix_zelle1)-1))))}

    Der Ausdruck sucht einen in und liefert den korrespondierenden Wert aus zurück. Falls nicht existiert, wird #WERT zurückgegeben. Nicht vergessen: die Formel muß wie angedeutet als Matrixformel mit SHIFT-CTRL-ENTER eingegeben werden. Das Argument in den MAX-Funktionen erzeugt eine Matrix, die überall -1 enthält, nur an der Position des steht dessen Zeilen# bzw. Spalten#. MAX liefert dann genau diese Zeilen#/Spalten# für INDEX zurück. ZEILE(suchmatrix_zelle1)-1 berechnet den Offset von im Blatt. 2*(suchmatrix=suchwert)-1) erzeugt die „-1“-Werte, die benötigt werden, wenn nicht vorhanden ist. Eine „0“ wäre einfacher, tut’s aber leider nicht (s. die Beschreibung von INDEX in der Online-Hilfe). Ich hoffe, die kurze Beschreibung machts verständlich. Deine tollen Animationen kann ich leider nicht erzeugen.
    Ist zugegeben nicht so mächtig wie XVERWEIS, aber wenn man nichts besseres hat… Vielleicht hat Microsoft ja irgendwann ein Einsehen und stellt XVERWEIS allgemein zur Verfügung.

    • Reinhard Hess

      Da hat sich der Fehlerteufel eingeschlichen: jetzt ist es korrigiert
      Hallo Martin,
      für alle, die kein Office365 haben: ich habe ich mir mal vor einiger Zeit eine Alternative gebastelt.

      {=INDEX(wertematrix;
      MAX((2*(suchmatrix=suchwert)-1)*(ZEILE(suchmatrix)-(ZEILE(suchmatrix_zelle1)-1)));
      MAX((2*(suchmatrix=suchwert)-1)*(SPALTE(suchmatrix)-(SPALTE(suchmatrix_zelle1)-1))))}

      Der Ausdruck sucht einen suchwert in suchmatrix und liefert den korrespondierenden Wert aus wertematrix zurück. Falls suchwert nicht existiert, wird #WERT zurückgegeben. Nicht vergessen: die Formel muß wie angedeutet als Matrixformel mit SHIFT-CTRL-ENTER eingegeben werden. Das Argument in den MAX-Funktionen erzeugt eine Matrix, die überall -1 enthält, nur an der Position des steht dessen Zeilen# bzw. Spalten#. MAX liefert dann genau diese Zeilen#/Spalten# für INDEX zurück. ZEILE(suchmatrix_zelle1)-1 berechnet den Offset von im Blatt. 2*(suchmatrix=suchwert)-1) erzeugt die „-1“-Werte, die benötigt werden, wenn nicht vorhanden ist. Eine „0“ wäre einfacher, tut’s aber leider nicht (s. die Beschreibung von INDEX in der Online-Hilfe). Ich hoffe, die kurze Beschreibung machts verständlich. Deine tollen Animationen kann ich leider nicht erzeugen.
      Ist zugegeben nicht so mächtig wie XVERWEIS, aber wenn man nichts besseres hat… Vielleicht hat Microsoft ja irgendwann ein Einsehen und stellt XVERWEIS allgemein zur Verfügung.

      • Martin Weiß Autor des Beitrags

        Hallo Reinhard,

        vielen Dank für diese Alternative und die Erläuterungen dazu!

        Schöne Grüße,
        Martin

  • erichm

    Vielen Dank für die sehr gut aufgebauten Beispiele, die eine weitere Beschreibung grundsätzlich nicht erfordern. Trotzdem ist es immer wieser interessant, etwas „drumherum“ zu kennen. XVERWEIS ist wirklich eine wahnsinnige Erleichterung!!

  • Harald Scherr

    Hallo Martin,
    auch von mir herzlichen Glückwunsch zu deinem Newsletter Jubiläum 😉 Dieser Newsletter verdeutlicht mal wieder, Excel schläft nie!!! Eine sehr gut gelungene Einführung in eine neue Funktion. Man lernt auf deiner Seite nie aus.
    Viele Grüße, Harald.
    P.S. Schade das die neue Funktion nur unter Office 365 funktioniert!

    • Martin Weiß Autor des Beitrags

      Hallo Harald,

      vielen Dank für deine schönes Feedback! (und ja, ich lerne selbst immer viel Neues dazu…)

      Schöne Grüße,
      Martin

  • Katrin

    Hy
    Ich habe ein Problem

    Ich habe mittels Sverweis Kundennummern mit Geräten verbunden.
    Und jetzt möchte ich die Kundennummer mit Geräten und den Gerätenamen anzeigen. In einer Funktion. Ein Kunde hat mehrere Geräte mit mehreren Gerätenamen.

    =SVERWEIS(J24;gesamt!$A$8:$GX$1805;44;0) so sieht die Formel aus, jetzt komme ich aber nicht weiter.

    LG Katrin

  • Monika

    Hallo Martin,

    ich finde deine Newsletter verständlich und Tipps büroalltagstauglich 🙂
    Ich finde allerdings keine Lösung für mein „Problemchen“ – Lösung aus Kombi Index und Sverweis funktioniert, jedoch habe ich 150 Suchkriterien. Ich kann doch nicht in einer Zelle 150 Index/Sverweis-Formeln pflegen.
    Ich habe 2 Datenbanken:
    in Nr. 1 Kosten je KST : Spalte A – Konten
    Zeile B1:MV1 – Kostenstellen
    in Matrix A1:MV600 sind die Beträge der Kostenstellen auf den jeweiligen Konten
    in Nr. 2 Kostenstellenübersicht : Spalte A – Kostenstellen
    Zeile B1:IB1 – Medien
    in Matrix A1:IB300 Schlüssel (Verteilung) der Kostenstellen nach Medien

    als Ergebnis hätte ich gern in Spalte A die Konten und Spalte B1:IB1 Medien
    in der Matrix sollten die geschlüsselten Beträge der Kostenstellen auf Medium in dem jeweiligen Konto.

    Wie gesagt, über sverweis würde ich den Schlüssel für die Kostenstelle finden und über die INDEX-Formel das Konto und den zu schlüsselnden Betrag – der Produkt wäre das Ergebnis, aber eben nur für 1 Kostenstelle.
    hast du was in der Trickskiste?

    Danke im voraus und liebe Grüße
    M.C.

    • Martin Weiß Autor des Beitrags

      Hallo Monika,

      ich kenne jetzt deine Tabelle nicht im Detail, aber mein Tipp wäre Power Query. Damit würde ich die beiden Kreuztabellen entpivotieren. Dann hättest du eine flache Tabelle mit allen Kombinationen Konten/Kostenstellen und eine zweite flache Tabelle mit Kostenstelle/Schlüssel. Beide Tabellen könnte man dann vermutlich über einen Join zusammenfassen und daraus eine Pivot-Tabelle erstellen.

      Wie gesagt: Ich kenne die Details nicht, aber in diese Richtung würde ich es mal versuchen.

      Schöne Grüße,
      Martin