Goodbye SVERWEIS, willkommen XVERWEIS! 48

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 🙂

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

48 Gedanken zu “Goodbye SVERWEIS, willkommen XVERWEIS!

  • Avatar-Foto
    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

  • Avatar-Foto
    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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      Hallo Micha,

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

      Schöne Grüße,
      Martin

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      Hallo Annette,

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

      Martin

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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.

    • Avatar-Foto
      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.

  • Avatar-Foto
    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!!

  • Avatar-Foto
    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!

    • Avatar-Foto
      Martin Weiß

      Hallo Harald,

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

      Schöne Grüße,
      Martin

  • Avatar-Foto
    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

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    A Seim

    Hallo
    Super Funktion, vielen Dank für die einfache Erklärung, es gibt aber ein Problem
    Bei DropDown Listen als Kriterium funktioniert die korrekte Rückgabe des Wertes nicht.
    Wenn ich also in Ihrem Beispiel aus Zeile A3 eine DropDown Liste der gesamten Kriterien mache (E3:E14), müsste sich je nach Auswahl des Kriterium der zugehöriger Wert verändern. (so ist zumindest meine Annahme) das passiert aber nicht.

    Viele Grüße
    Andrew

    • Avatar-Foto
      Martin Weiß

      Hallo Andrew,

      ich nehme an, Sie beziehen sich auf das erste Beispiel zum XVERWEIS oben im Artikel. Es spielt grundsätzlich keine Rolle, ob sie den Buchstaben in Zelle A3 direkt eingeben oder aus einer Dropdownliste auswählen. Die XVERWEIS-Funktion daneben in Zelle B3 greift sich den Buchstaben in A3 und liefert dann das entsprechende Ergebnis zurück. Wenn das bei Ihnen nicht der Fall ist, stimmt entweder etwas mit der Formel nicht. Oder Sie haben vielleicht die automatische Neuberechnung versehentlich deaktiviert (einfach mal nach der Eingabe die Funktionstaste F9 drücken, dann wird in jedem Fall neu berechnet).

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Toshi Abt

    Hallo,

    wie immer ein super Newsletter, vielen Dank dafür.
    Ich empfehle diese Seite auch jedem weiter! 🙂

    Der XVERWEIS ist wirklich genial und macht es deutlich einfacher, als der SVERWEIS.
    Ein gutes Beispiel dafür ist, dass man mit dem XVERWEIS beim Suchkriterium auf mehrere Felder Bezug nehmen kann (indem die Suchparameter mit „&“ verbunden werden).
    Das war beim SVERWEIS zwar auch schon möglich, hier musste dann aber noch mit der WAHL-Funktion gearbeitet werden.
    Da beim XVERWEIS die Such und Ausgabematrix getrennt eingegeben wird, entfällt die WAHL-Funktion.

    So kann man mit der Formel
    =XVERWEIS(I2&H2;A:A&B:B;E:E)
    die Spalten A&B nach den Parametern, die in I2 und H2 eingetragen werden, durchsuchen und als Ergebnis den entsprechenden Eintrag aus der Spalte E ausgeben.

    Das hat mich total begeistert, zumal es dank der getrennten Eingabe von Such- und Rückgabematrix auch völlig egal ist, in welcher Spalten-Reihenfolge die Suchkriterien in der Matrix stehen.

    Viele Grüße
    Toshi Abt

    • Avatar-Foto
      Martin Weiß

      Hallo Toshi,

      danke für dein schönes Feedback und für die Weiterempfehlung 🙂
      Ja, der XVERWEIS ist wirklich eine feine Sache. Die einzige Schwierigkeit ist, dass man sich nach jahrzehntelangem SVERWEIS erst daran gewöhnen muss, ihn auch wirklich einzusetzen.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Toshi Abt

        Gerne 🙂
        Vielleicht an der Stelle noch eine Frage: Beim SVERWEIS war es möglich, mit Hilfe von VERGLEICH, den gewünschten Spaltenindex zu dynamisieren.
        Im XVERWEIS beziehe ich mich bei der Ausgabematrix ja nicht mehr auf eine Spaltenzahl, sondern gebe die Spalten in Form von z.B. „A:A“ an.
        Das kann ich ja dann nicht dynamisieren, oder kennst du da auch einen Trick? 🙂

        Gruß
        Toshi

        • Avatar-Foto
          Martin Weiß

          Hallo Toshi,

          ja, der Trick geht über die INDIREKT-Funktion und über formatierte Tabellen. Das heißt, die zu durchsuchende Tabelle wird als erst formatierte Tabelle angelegt.
          Angenommen, der Name der gewünschten Ausgabespalte steht in Zelle A1:

          =XVERWEIS(L8;Tabelle1[Auszahlungs-ID];INDIREKT(„Tabelle1[„&A1&“]“))

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Xaver

    Hallo Peter,

    danke für den super Beitrag. Du schreibst ja auch, dass sogar horizontale und vertikale Suche zeitgleich möglich ist und gibst dafür ein Beispiel. Weißt du ob es auch möglich wäre, wenn man bei deinem Beispiel in der Spalte neben dem Verkäufer auch noch ein Produkt stehen hätte z.B. Huber | Bananen | 500,00€ ob man dann bei deinem Ergebnisbereich zusätzlich neben Verkäufer, Monat und Umsatz auch noch das Produkt mit ausgeben kann? Also das wäre dann quasi ein drittes Suchkriterium.

    Danke und ein schönes Wochenende

    • Avatar-Foto
      Martin Weiß

      Hallo Xaver,

      ich heiße zwar Martin und nicht Peter, aber das macht ja nichts 😉
      Ja, auch ein drittes Kriterium wäre möglich. Wenn das Produkt in einer neuen Spalte steht, kann man die Suchkriterien und die Suchmatrix für den ersten XVERWEIS einfach mit einem &-Symbol kombinieren.

      Zum Beispiel so:
      XVERWEIS mit 3 Kriterien
      =XVERWEIS(A2&C2;$F$2:$F$8&$G$2:$G$8;XVERWEIS(B2;$H$1:$M$1;$H$2:$M$8))

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Martin

    Hallo Martin,
    ein wirklich klasse Beitrag! Schön kurzweilig mit kurzen und verständlichen Beispielen. Ich nutze den sverweis jeden Tag, und musste nun – fast beschämend feststellen, dass ich den xverweis in meiner Excelversion habe. Da wird doch gleich mal umgesattelt und fleißig probiert. Vielen Dank und weiter so, Gruß (ebenfalls) Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Namenskollege,

      kein Grund zur Scham, da bist du sicherlich nicht der Einzige. Der XVERWEIS ist bei vielen Anwendern noch nicht so richtig bekannt. Aber der Umstieg lohnt sich auf jeden Fall!

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Eva Biegger

    Hallo,
    ich bin Lehrerin und arbeite gerade meine Vorbereitung von Sverweis auf Xverweis um. Und gleich stoße ich auf ein Problem, das mir niemand erklären kann.
    Ich mache eine Schularbeitenauswertung. Die Schüler erhalten die Note aufgrund der erreichten Punkte.
    Suchmatrix: 0 bis 20 Nicht genügend, 21 bis ….
    Nun gibt es aber auch Schüler, die nicht mitgeschrieben haben, bei ihnen steht daher anstatt der Punkte ein NB für nicht beurteilt.
    Bei dieser Lösung erhält der Schüler ein Sehr gut anstelle eines Nicht beurteilt: =XVERWEIS(D19;$E$12:$E$16;$G$12:$G$16;“Nicht beurteilt“;-1)
    Arbeite ich mit den Bis-Punkten, die Matrix beginnt also mit 20 klappt alles perfekt.
    =XVERWEIS(D19;$F$12:$F$16;$G$12:$G$16;“Nicht beurteilt“;1)

    Wie soll ich meinen Schülern das erklären? Wieso wird der Text NB bei der ersten Lösung nicht als falscher Eintrag erkannt?
    Besten Dank für Tipps. Liebe Grüße vom Bodensee Eva

    • Avatar-Foto
      Martin Weiß

      Hallo Eva,

      ich habe mal versucht, dein Beispiel nachzubauen. Und ich gehe davon aus, wenn der Schüler nicht mitgeschrieben hat, dann bleibt die Eingabezelle D19 leer, richtig?
      XVERWEIS und Suchmodus

      Die Matrix wird standardmäßig von oben nach unten durchsucht. Beim Suchmodus -1 wird also bis zur höchsten Punktezahl alles nach einem leeren Eintrag durchsucht. Da es keinen leeren Eintrag in der Matrix gibt, wird am Ende der nächst kleinere Wert genommen, was eben die 51 und damit ein „sehr gut“ ist.

      Beim Suchmodus 1 hingegen wird am Ende der Matrix der nächst größere Wert vom letzten Eintrag (in meinem Bild 60) aus gesehen genommen. Einen größeren Wert gibt es aber nicht, da 60 bereits der letzte Wert in der Liste ist. Daher greift der Text „Nicht beurteilt“.
      Du kannst auch die Gegenprobe machen:
      Falls du in der Matrix den höchsten Wert in der bis-Spalte leer lässt, dann wird auch beim Suchmodus 1 unerwünschter Weise ein „sehr gut“ erscheinen. Denn dann gibt es ja einen leeren Eintrag.

      Ich hoffe, damit wird es ein wenig klarer.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Eva Biegger

        Vielen Dank für die umfassende Antwort. Ich seh immer mehr: Der Xverweis ist nicht nur ein ausgebauter Sverweis nach links. Die Funktion ist so vielfältig, dass ich meine gesamte Unterrichtsvorbereitung komplett umarbeiten muss. Das wird mich und meine Schüler noch eine Weile beschäftigen.
        Herzliche Grüße Eva

  • Avatar-Foto
    Armin Benke

    Hallo,
    ich beim Probieren mit Zeitschwellwerten auf folgenden Excel-Spezialeffekt gestoßen.
    In meiner Tabelle wollte ich das die Messung mindestens 2 Stunden nach der Start erfolgt.(Endzeit minus Startzeit muss größ0er als 2:00 sein)
    Hat nicht geklappt-weil Excel ja mit Bruchstücken vom Tag rechnet.
    Dann habe ich die Rechenergebnisse in Kommazahlen umformatiert und sie da, wenn ich 10 Uhr 8 Uhr „abziehe“ kommt einen andere Zahl raus wie der Subtraktion 8:00 minus 06:00 Uhr..
    Ich habe keine Erklärung wieso?
    Können Sie mir helfen?
    Excelversion 2013, Windows10
    04:00 06:00 0,08333333333333330
    06:00 08:00 0,08333333333333330
    08:00 10:00 0,08333333333333340
    10:00 12:00 0,08333333333333330
    12:00 14:00 0,08333333333333300
    14:00 16:00 0,08333333333333290
    16:00 18:00 0,08333333333333300
    18:00 20:00 0,08333333333333300
    20:00 22:00 0,08333333333333290
    22:00 00:00 0,08333333333333300
    00:00 02:00 0,08333333333332990
    02:00 04:00 0,08333333333334010
    04:00 06:00 0,08333333333332990
    06:00 08:00 0,08333333333333990
    08:00 10:00 0,08333333333333020
    10:00 12:00 0,08333333333332990
    12:00 14:00 0,08333333333332990
    14:00 16:00 0,08333333333333020
    16:00 18:00 0,08333333333333990
    18:00 20:00 0,08333333333332990
    20:00 22:00 0,08333333333333020
    22:00 00:00 0,08333333333331990
    00:00 02:00 0,08333333333332990

    • Avatar-Foto
      Martin Weiß

      Hallo Armin,

      das Problem hier ist, dass Excel generell eine maximale Genauigkeit von 15 Stellen hat. Daher ist alles, was nach der 15 Stelle kommt als Datenmüll zu betrachten. Wie diese Werte allerdings genau zustande kommen, kann ich Ihnen leider auch nicht sagen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Hugo

    man könnte den Hinweis ab welcher Version man die Funktionnützen kann auch irgendwie Userfreundlich am Anfang des Beitrages einbauen, oder man lässt in hier bewusst weg und versteckt ihn im Beitrag wie ein Osterei 🙂

  • Avatar-Foto
    Christoph Huchler

    Hallo Martin,

    XVERWEIS Suuuuuuuuuuuuuuuuuuuuuuuuuuper

    Ich liebe inzwischen die Funktion ABER ich habe noch einen Hänger 🙁

    Ich suche einen bestimmten Eurowert z.B. 175,99 € in zwei Spalten die, die als Min und Max Wert 175,10 und 181,00 haben. Um dann den Wert aus der danebenliegenden Spalte zurückzubekommen.

    Hab keine Idee dazu.
    IST Min Max Output
    175,99 € 178,10 € 181,00 € 12,00%

    Hast Du mir eine Lösung ?

    Herzlichen Dank und weiter so. In meinen jungen Jahren hieß es Excel = Hausfrauenprogramm. Inzwischen müssten die Damen und Herren in der Küche aber verdammt fit sein. 😉

    Gruß Christoph

    • Avatar-Foto
      Bernd Meier

      Uiii. Aber Achtung – Das scheint nach meinen ausgiebigen Tests nicht zu 100% sicher zu funktionieren. Seltsam. Manchmal wird der Treffer nicht gefunden, wenn der Vergleichstext mehr als 255 Zeichen hat. Blöd. Ich bin jetzt erst mal zurück zu VBA bei >255 Zeichen.