SVERWEIS, wie du ihn garantiert noch nicht kennst! 95

Mit dem aufgebohrten "Super"-SVERWEIS lassen sich auch mehrere Suchkriterien verwenden
 

Eine meiner Lieblingsfunktionen in Excel ist zweifellos der SVERWEIS. Wer dieses universelle Werkzeug einmal verstanden hat, kann sich nicht mehr vorstellen, jemals ohne diese Funktion ausgekommen zu sein:

Suchkriterium eingeben, zu durchsuchenden Tabellenbereich eingeben, gewünschte Ergebnisspalte festlegen, fertig!

Was macht man aber, wenn man statt einem Suchkriterien eigentlich zwei in seiner SVERWEIS-Funktion bräuchte? Oder drei?

Geht nicht, glaubst du?

Geht doch! Und zwar mit dem “Super”-SVERWEIS.

Der “normale” SVERWEIS

Für diejenigen unter meinen Lesern, die mit dem SVERWEIS nicht ganz so vertraut sind, kommt hier ein kleines Anwendungsbeispiel (die Datei kannst du dir hier herunterladen).

Ich habe eine kleine Tabelle mit den Einwohnerzahlen Deutschlands, aufgegliedert nach Altersgruppen:

Die Ausgangstabelle

Die Ausgangstabelle

Nun möchte ich in Zelle F2 eine beliebige Altersgruppe eingeben und Excel soll mir dazu die passende Einwohnerzahl liefern. Ein Paradebeispiel für den SVERWEIS!

=SVERWEIS(Suchkriterium; Matrix; Spalte; Übereinstimmung)

Das Suchkriterium ist die Altersgruppe, die ich in Zelle F2 eintrage (um Fehleingaben auszuschließen, habe ich die gültigen Werte in einer Dropdownliste hinterlegt).

Die Matrix, also der Tabellenbereich, den ich nach meinem Suchkriterium durchsuchen möchte, ist der Bereich A2:B21. Wichtig beim SVERWEIS ist, dass das Suchkriterium immer nur in der ersten Spalte gesucht werden kann.

Die Einwohnerzahl steht in der zweiten Spalte meiner Matrix, daher lautet mein dritter Parameter “2”.

Und da ich nur an einer exakten Übereinstimmung mit meinem Suchkriterium interessiert bin, lautet der letzte Parameter FALSCH (alternativ kann die Zahl 0 eingeben werden). Hier also die fertige Formel:

Der einfache SVERWEIS

Der einfache SVERWEIS

Damit erhalte ich folgendes Ergebnis:

Das gewünschte Ergebnis

Das gewünschte Ergebnis

Wenn ich die Altersgruppe in F2 ändere, liefert mir Excel sofort die dazu passende Einwohnerzahl:

Automatische Neuberechnung

Automatische Neuberechnung

Soweit, so gut.

Etwas kniffeliger wird es, wenn man zwei Suchkriterien hat, wie z.B. Altersgruppe und Geschlecht. Meine leicht abgewandelte Tabelle sieht nun so aus:

Die neue Ausgangstabelle

Die neue Ausgangstabelle

Wie lässt sich diese Aufgabe lösen?

Option 1: Die Hilfsspalte

Da man im SVERWEIS nur ein Suchkriterium angeben kann, wäre die naheliegendste Option, mit einer Hilfsspalte zu arbeiten. So könnte ich die beiden Spalten mit der Altersgruppe und dem Geschlecht in einer Spalte zusammenfassen:

Option 1: Die Hilfsspalte

Option 1: Die Hilfsspalte

Folglich muss ich auch meine beiden Suchkriterien in einer Hilfszelle zusammenfassen, bevor ich den SVERWEIS anwenden kann:

Vorbereitung des Suchkriteriums

Vorbereitung des Suchkriteriums

Und erhalte auf diesem Weg wieder mein gewünschtes Ergebnis:

Der SVERWEIS mit Hilfsspalte

Der SVERWEIS mit Hilfsspalte

Ergebnis

Ergebnis

Aber Hilfsspalten sind eben nicht sehr elegant und blähen die Ursprungstabelle unnötig auf. Mit Hilfe eines cleveren Tricks können wir auf dieses unschöne Hilfskonstrukt verzichten!

Option 2: Der Super-SVERWEIS

Auf diese Anwendungsmöglichkeit bin ich übrigens erstmals bei meinem großen Excel-Vorbild Chandoo aufmerksam geworden.

Dazu kombinieren wir den SVERWEIS mit der WAHL-Funktion, geben das ganze als Matrix-Formel ein (d.h. wir schließen die Eingabe mit STRG+Umschalt+ENTER ab) und erhalten (Trommelwirbel!) folgendes Ergebnis:

Der "Super"-SVERWEIS

Der “Super”-SVERWEIS

Wie funktioniert nun diese “magische” Formel?

Zerlegen wir das schöne Stück in seine Bestandteile. Die beiden Suchkriterien werden – ähnlich, wie oben in Option 1 beschrieben – zu einem einzigen zusammengefasst. Somit lautet der erste Parameter F2&F3.

Für den zweiten Parameter, der ja die zu durchsuchende Tabelle definiert, nutzen wird die WAHL-Funktion. Allgemein gesprochen wählt man bei der WAHL-Funktion aus einer Liste von Werten mit Hilfe eines Index einen Wert aus:
=WAHL(Index; Wert1; Wert2; ...)

Bezogen auf unser Beispiel sieht das also so aus:

Die WAHL-Funktion

Die WAHL-Funktion

Für den ersten Parameter – den Index – geben wir in geschweiften Klammern die Werte 1 und 2 an, getrennt durch einen Punkt. Was bedeutet das?

In Excel-Funktionen lassen sich Wertelisten entweder als Bezug in der Form A1:A5 eingeben, oder durch die direkte Eingabe der konstanten Werte, getrennt durch ein Semikolon: Somit sind =SUMME(A1:A5) und =SUMME(10;4;5;3) gültige Eingaben.

Wenn es sich dabei um eine Matrix handelt, werden die Konstanten in geschweifte Klammern gesetzt. Spalten werden dabei durch einen Punkt getrennt, Zeilen durch ein Semikolon. So lässt sich die Tabelle auf der linken Seite wie im folgenden Bild auch in einer Kurzschreibweise darstellen:

Matrix-Konstanten

Matrix-Konstanten

Das bringt uns zurück zu unserer WAHL-Funktion: Die Angabe von {1.2} im ersten Parameter bedeutet also, dass wir an zwei Spalten interessiert sind.

In den folgenden Parametern werden die eigentlichen Werte übergeben, wobei für den ersten Wert die Spalten A und B zusammengefasst werden (A2:A42&B2:B42) und danach kommt die Wertespalte mit den Einwohnerzahlen (C2:C42).

Ich weiß, das ist ein sehr abstraktes Konstrukt. Etwas deutlicher wird es vielleicht, wenn man sich das Ganze mit Hilfe der Formelauswertung anzeigen lässt:

Mehr Klarheit mit "Formel auswerten"

Mehr Klarheit mit “Formel auswerten”

Hier sieht man die Wertepaare, die in den geschweiften Klammern ermittelt wurden:

  • “0 – 5männlich”.1693000;
  • “0 – 5weiblich”.1604000;
  • “5 – 10männlich”.1747000;

und so weiter. Das ist also die Tabelle, die an den SVERWEIS zum Durchsuchen übergeben wird.

Der Rest der SVERWEIS-Funktion ist dann wieder bekannter Stoff: Es wird die zweite Spalte durchsucht, und es wird wieder nach genauer Übereinstimmung gesucht (“FALSCH”)

Wichtig ist, dass die SVERWEIS-Funktion als Matrix-Funktion eingeben wird, also die Eingabe mit STRG+Umschalt+ENTER abgeschlossen wird.

So, damit habe ich dich für heute genug strapaziert. Nur noch soviel zum Schluß: Mit diesem “Super”-SVERWEIS lassen sich auch mehr als zwei Kritieren verwenden. Du musst nur alle Suchkriterien mit der &-Verknüpfung zusammenfassen.

Viel Spaß beim Ausprobieren!

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.

95 Gedanken zu “SVERWEIS, wie du ihn garantiert noch nicht kennst!

  • Matthias

    Hallo Martin,
    bin gestern auf deine Seite gestoßen und schon jetzt ein großer Fan Deiner Darstellungsweise/Didaktik. Obwohl ich schon paar Jahre mit Excel zu tun hatte, habe ich in den zwei Tagen viel neues gelernt. Die Alternativdarstellung von Tabellen/Matrizen und die kluge Verwendung wie in diesem Artikel war mir vollkommen neu.
    Vielen Dank!
    Matthias

    • Martin Weiß Autor des Beitrags

      Hallo Matthias,

      vielen Dank, freut mich sehr! Geht mir übrigens auch noch oft so: Man lernt mit Excel nie aus 🙂

      Schöne Grüße,
      Martin

      • Matthias

        Bei dem Thema kam mir die Frage: Gibt es eigentlich eine Funktion um die Inhalte eines Bereiches (mehrere Zeilen und Spalten) in der erwähnten Matrizen-Kurzschreibweise in eine einzelne Zelle zu schreiben/konvertieren/kodieren?

        Z.B., wenn jemand Excel nutzen würde, um Rechnungen zu verfassen, und die in der Vergangenheit erstellen Rechnungen in einem Erfassungsbogen erfasst werden sollen inklusive einer Kurzform aller in der jeweiligen Rechnung auftauchenden Rechnungsposten?

        • Martin Weiß Autor des Beitrags

          Hallo Matthias,

          eine interessante Frage, auf die ich momentan leider auch keine Antwort habe. Ansonsten wäre eine mögliche Lösung, über die VERKETTEN-Funktion bzw. & die entsprechenden Zellen zusammenzuführen.

          Grüße,
          Martin

  • Michael

    Ich bekomme es nicht hin ;-/
    Meine Tabelle besteht mittlerweile aus nur noch 3 Spalten und 2 Zeilen

    In Zeile 2 steht in den Zellen A2, A3 und A4 der Text: Monat, Mitarbeiter, Urlaub genommen
    In Zeile 4 steht: 1,00, Name1, 2
    In Zeile 5 steht: 1,00, Name3, 5

    In F2 steht 1,00
    In F3 steht Name1

    Die Funktion ist: {=SVERWEIS(F2&F3;WAHL({1.2};A4:A10&B4:B10;C4:C10);3;0)}

    Erwartet hätte ich als Ausgabe “2”, also die Angabe, wieviel Urlaub “Name1” im Monat “1” genommen hat.
    Bekommen tue ich aber #BEZUG!
    Die Fehlerverfolgung warnt m.E. bei dem Schritt C4:C10 (wobei mir dieser auch nicht klar ist).
    Was ist falsch, warum ist es falsch und was kann ich dagegen tun?

    Danke!

    Michael

  • Michael

    Ohhh man. Da Ja Spalte A und B zu einem Ausdruck zusammengefasst werden, ist für sverweis die Spalte C ja die 2. Spalte und nicht die dritte.
    Erledigt, sorry fürs Posten und Danke fürs lesen.

  • Christine

    hallo,
    ich bin noch Anfängerin in Excel und habe das mit dem SVERWEIS hinbekomen, dank der guten Erklärung. Nur weiß ich nicht, wie das mit dem Dropdownfeld funktioniert. Für ein Beschreibung wäre ich dankbar.
    Grüße
    Christine

  • Sebastian

    Hallo Martin!
    Wie sieht es denn aus, wenn ich deine Tabelle erweitere. In deinem Bsp. hast du die Daten von einem Land. Wie sieht es aus, wenn ich die Tabelle (Alter,Geschlecht,Einwohner) nicht nur von einem Land besitze sondern von drei oder vier Ländern. Ein weiteres Suchkriterium wäre dann “Land”. Die Tabellen würden nebeneinander stehen.
    Excel müsste dann erst die richtige Tabelle(Matrix) identifizieren und dann deinen sverweis anwenden.
    Gruß
    Sebastian

    • Martin Weiß Autor des Beitrags

      Hallo Sebastian,

      in diesem Fall wäre der relevante Tabellenbereich nicht fix, sondern könnte mit der Funktion BEREICH.VERSCHIEBEN um die jeweilige Anzahl von Spalten nach rechts verschoben werden. Zum Beispiel so:
      Bild 1

      Schöne Grüße,
      Martin

  • Aurahs

    hallo, ich komme via google auf Ihre Seite, weil ich mit einem Excel-Problem nicht weiter komme, da ich ganz selten damit arbeite.
    In zwei Tabellenblättern stehen Mitgliedernamen; die erste TAB hat die Gesamtmitglieder, die zweite TAB nur Jubilare; diese aber ohne das Eintrittsdatum.
    Jetzt will ich das Eintrittsdatum in die zweite TAB übertragen mit SVERWEIS. Ich habe aber Namen mehrmals und muß auch über den Vornamen suchen. Wie mache ich das bei EXCEL. Es muß trivial sein, damit ich es behalte 🙂 Danke für eine rasche Rückmeldung. mfG J. Aurahs

    • Martin Weiß Autor des Beitrags

      Hallo Herr Aurahs,

      dieser Artikel beschreibt eigentlich ziemlich genau, wie so etwas geht. Also entweder ganz trivial mit einer Hilfsspalte, in der Sie Name und Vorname zusammenfassen. Oder – nicht ganz so trivial – mit einer Kombination von SVERWEIS und WAHL. Beide Optionen sind oben Schritt für Schritt erläutert.

      Schöne Grüße,
      Martin

  • Matthias

    Hallo Martin, Mit S-Verweis und Super S-Verweis bekomme ich mein Problem nicht in den Griff. Kurz: Ich suche für 30 verschiedene Kunden aus verschiedenen Pivot Listen mit Darstellung von Wochenumsätzen diese Umsätze heraus und stelle Sie auf einem Tabellenblatt zum Report zusammen. Die 30 dazu notwendigen Formeln enthalten dann die Angaben zum Jahr, Quartal und die Woche {Bsp: =GETPIVOTDATA(“EUR Extended Book Price”;Bookings!$A$8;”Week Id”;”2016 Q1 W01″;”Customer Name”;”GEOTEAM1″)}. Hier möchte ich die Wunschwoche in einer Zelle quasi als Variable vorgeben und die 30 Formeln sollen auf diese Woche W02… umgestellt werden. Also ein Teil der Formel soll als Variable auf die “Wochenzelle” zugreifen…. Idee? Wäre super, wenn Du eine Antwort hast.

    • Martin Weiß Autor des Beitrags

      Hallo Matthias,

      du kannst in der Funktion PIVOTDATENZUORDNEN (bzw. GETPIVOTDATA) ja auch mit Zellbezügen arbeiten. Zumindest bei mir mit Excel 2013 klappt das ohne Probleme. Bezogen auf dein Beispiel wäre das also z.B.

      =GETPIVOTDATA(“EUR Extended Book Price”;Bookings!$A$8;”Week Id”;A1;”Customer Name”;”GEOTEAM1″).

      In Zelle A1 müsste dann also “2016 Q1 W02” stehen, oder wie auch immer die genauen Spaltenbezeichnungen bei dir lauten. Es kommt lediglich auf die exakte Bezeichnung an.

      Grüße,
      Martin

      • Matthias

        Danke Martin für den Tipp. Also bisher war ich damit noch nicht erfolgreich. Noch mal etwas genauer beschrieben: Die Pivots (alle in einer Datei) ändern sich jeden Tag nach Refresh. Damit ergeben sich jede Woche weitere Spalten in verschiedenen Pivot Datenblättern mit Wochenumsätzen, also aus “2016 Q1 W01” werden in der Spalten rechts daneben neue erzeugt die dann “2016 Q1 W02” heisst usw. In einem separaten Datenblatt (Report), jedoch in der gleichen Datei wo auch die Pivots als Datenblätter abgelegt sind, suche ich mit der beschriebenen Formel Umsätze aus verschiedenen Pivots heraus (hier stelle ich also Zahlen zusammen, aus denen dann Weiteres berechnet wird). Das Problem ist, dass ich nun in jeder neuen Woche die Formeln (und das sind locker mal 30) manuell auf die folgende Woche umstellen muss, damit mein Report die neuen Wochenumsätze erkennt. Wenn ich jetzt also irgendwo diesen immer zu ändernden Formel Bestandteil “2016 Q1 W01” auf meine Report-Seite als einen Zellbezug schreibe wo sich alle 30 Formeln automatisch aktualisieren, wäre das die Lösung. Das klappt aber bei mir so bisher nicht. Gerne kann ich auch mal eine Datei schicken und die Felder markieren. Danke für Deine Mühe. Matthias.

        • Matthias

          Hallo Martin, hast Du noch einen Tipp für mich? Wäre Dir sehr dankbar, denn ich bin noch immer nicht weiter mit meinem Thema von oben. Gruß, Matthias.

          • Martin Weiß Autor des Beitrags

            Hallo Matthias,

            tut mir leid, wenn es mit dem Tipp nicht geklappt hat. Es scheint sich hier in der Tat um eine sehr individuelle Angelegenheit zu handeln und da müsste ich wirklich tiefer einsteigen. Spontan kann ich dir also keine bessere Lösung bieten. Aber vielleicht hat ein anderer Leser hier noch eine gute Idee.

            Schöne Grüße,
            Martin

  • Gerd

    ..zumindest der Screenschot mit dem Super-SVERWEIS ist nicht lesbar, die anderen schon. Ich habe ihn mir aber zusammenbasteln können, wie wohl die meisten…

    • Martin Weiß Autor des Beitrags

      Hallo Gerd,

      danke erstmal für den Hinweis. Ich habe mir gerade nochmal alle Screenshots angesehen und eigentlich sind alle gut lesbar. Ich traue es mich fast nicht zu fragen, aber hast Du denn die kleinen Vorschaubilder angeklickt? Dann gibt es nämlich eine (hoffentlich) gut lesbare Vergrößerung.

      Schöne Grüße,
      Martin

      • Gerd

        Hallo Martin,
        ich hatte gar nichts angeclickt, sondern nach dem diagonalen Durchlesen auf die gute Idee der Kombination mit WAHL gleich meine Anwendung programmiert, ohne dein Beispiel nachzuvollziehen. Ich muss auch noch dateiübergreifend arbeiten, und es kommt noch INDIREKT mit ins Spiel, d.h. es baut sich noch einiges mehr drumherum. Das ging auch fast auf Anhieb. Ich war dann aber nochmal auf der Site. Irritiert hatte mich im nachhinein dabei dann, dass einige Vorschaubilder scharf, andere unscharf dargestellt wurden. Das liegt aber wohl nur an den kruden Auflösungsverhältnissen mit der Grafikkarte. Vorschaubilder im Fließtext? Naja, schön ist das nicht gerade für die Lesbarkeit. Sparen lässt sich damit eh nichts. Fakt bleibt, dass deine Idee voll bei mir ins Konzept passte, bingo!!

        • Martin Weiß Autor des Beitrags

          Hallo Gerd,

          ok, verstanden. Über Vorschaubilder kann man sicherlich streiten. Der Grund ist für mich einfach ein etwas schnelleres Laden der Seite, was insbesondere bei mobilen Geräten einen Unterschied macht. Aber wie auch immer: Es freut mich, wenn Dir der Artikel ein wenig weiterhelfen konnte.

          Schöne Grüße,
          Martin

  • Thomas

    Hallo Martin

    Habe es jetzt genauso gemacht wie beschrieben, bekomme aber immer den Fehler #Bezug! Weiss langsam nicht mehr, was ich falsch mache.

    Voran kann es liegen? Habe exakt die gleiche Formel eingegebe wie Du, aber es funktioniert nicht. Kann es an der Excelversion liegen?

    Danke und Gruss
    Thomas

    • Martin Weiß Autor des Beitrags

      Hallo Thomas,

      ich denke nicht, dass es an der Excel-Version liegt (welche hast Du denn?).
      Und Du bist ganz sicher, dass Du die Formel korrekt als Array-Formel eingegeben hast: Also ohne die geschweiften Klammern am Anfang und Ende, sondern stattdessen die Formel mit Strg+Umschalt+Enter abgeschlossen hast?

      Falls Du Dir die Beispieldatei noch nicht heruntergeladen hast, dann solltest Du das nachholen. Dort ist nochmal alles enthalten.

      Schöne Grüße,
      Martin

    • Martin Weiß Autor des Beitrags

      Hallo Alexander,

      letztendlich stößt auch jede normale SVERWEIS-Funktion irgendwann an Performance-Grenzen. Wenn zwei Tabellen mit je 100.000 oder mehr Datensätzen gegeneinander abgeglichen werden sollen, kann eine Zwangs-Kaffepause notwendig werden.
      Und ja, bei der Array-Variante wird das bedeutend früher der Fall sein.

      Grüße,
      Martin

  • Sven

    Bekommt noch jemand einen #WERT-Fehler ausgegeben? Laut EXEL-Hilfe kommt der Fehler wenn Zahlen und andere Zelleninhalte in einer Formel vermischt werden.
    Aber das ist ja bei Martins Beispiel auch der Fall.
    Gibt es hier ein Workaround?
    Meine EXEL-Version ist 2013.
    Danke im Voraus

    • Martin Weiß Autor des Beitrags

      Hallo Sven,

      bist Du sicher, dass Du die Formel über STRG+Umschalt+Eingabe abgeschlossen hast? Ansonsten funktioniert es definitiv nicht.

      Schöne Grüße,
      Martin

  • Stephen Vögeli

    Hallo Martin
    Ich find deine Anleitungen super, und probier einiges aus. Diesen Super-SVERWEIS bekomme ich allerdings nicht hin. Irgendwo harkts bei mir. Ich bekomme immer #bezug ausgegeben. Wenn ich beim Spaltenindex eine 1 eingebe, bekomme ich 10 – 15männlich.
    Aber irgendwie will er nicht auf Spalte C zugreifen. Bei den Berechnungsschritten auch nicht. Ich habe glaub alles so gemacht wie gesagt, mit STRG Shift Enter abgeschlossen, aber es will nicht. Die Formel sieht genau so aus wie in der Vorgabe.
    Besten Dank im Voraus.

    • Stephen Vögeli

      Und aussehen tut sie so:
      {=SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C45);2;FALSCH)} (nach STRG-C STRG-V geschweifte Klammern am Anfang und Ende natürlich von Hand eingegeben)

      • Stephen Vögeli

        Ok, habs rausgefunden…
        Bei mir zeigts die Formel original so an:
        {=SVERWEIS(F2&F3;WAHL({1|2};A2:A41&B2:B41;C2:C41);2;FALSCH)}
        Also mit einem Backslash {1|2} anstelle von einem Punkt. Da ichs nun beim Nachbauen immer mit dem Punkt versucht habe, gings nicht. Ob das wohl an der Schweizer Tastatur liegt?

        • Martin Weiß Autor des Beitrags

          Hallo Stephen,

          der Punkt wäre auf jeden Fall wichtig. Ob es da bei der Schweizer Tastatur an dieser Stelle Unterschiede gibt, kann ich nicht sagen. Mir ist aber aufgefallen, dass in deinen ersten Nachrichten die Bezüge unterschiedlich groß waren:
          A2:A41
          B2:B41
          C2:C45

          Das dürfte ebenfalls nicht sein.

          Grüße,
          Martin

          • Stephen Vögeli

            Ja, das war mir ebenfalls aufgefallen, ist beim rumprobieren passiert. Funktioniert allerdings auch so:-)
            Aber der Punkt im Index war der Punkt… Wenn ich den mit Backslash ersetze, funktionierts.
            Danke

  • Fabian

    Moin Martin,

    großartig erklärt, vielen Dank dafür!
    Bei mir taucht nun gerade folgendes Problem auf:
    In der Zelle mit der Formel wird mir die Fehlermeldung #NV angezeigt.
    Wenn ich aber die Funktionsargumente-Prüfung öffne wird mir dort das richtige Ergebnis wiedergespiegelt.
    In einer weiteren betroffenen Zelle wird mir ein falscher Wert angezeigt – auch hier in der Fuktionsargumente-Prüfung der richtige Wert.

    Woher kann dieser Fehler kommen und wie kann ich den beheben?

    Danke Dir!

  • Bernd Knoll

    Vorsicht, dieser Super SVerweis kann falsche Ergebnisse liefern, wie folgendes Beispiel zeigt:
    Wertematrix: (1;112), Suchwerte: 11;12. Es ist offensichtlich dass die gesuchten Werte in der Matrix nicht vorkommen. Durch die Kombination mit & entsteht aber eine neue “Hilfsspalte” mit dem Inhalt 1112 und ein Suchwert mit demselben Inhalt 1112. Der Super SVerweis wird also einen Treffer liefern, obwohl er das nicht sollte. Dies wäre auch mit einer zusätzlichen Spalte nicht lösbar. Ein Workaraound könnte die Trennung der Werte mit einem zusätzlichen Zeichen sein, das sicher nicht als Inhalt vorkommt (z.B.”| “), falls es das gibt..

    • Martin Weiß Autor des Beitrags

      Hallo Bernd,

      erwischt 🙂
      Aber Du hast vollkommen Recht: Die Kombination von zwei (oder mehreren) Feldern birgt ein gewisses Risiko auf falsche Treffer. Der vorgeschlagene Workaround mit einem Sonderzeichen minimiert dieses Risiko nochmal deutlich. Vielen Dank für den Hinweis.

      Schöne Grüße,
      Martin

    • Martin Weiß Autor des Beitrags

      Hallo Rainer,

      SUMMEWENN (bzw. SUMMEWENNS) würde zwar in diesem konkreten Beispiel funktionieren, da es sich bei der gesuchten Spalte um eine Wertespalte handelt. Wenn sich dort stattdessen jedoch Text befindet, klappt es mit diesen Funktionen nicht mehr.

      Schöne Grüße,
      Martin

  • Sandra

    Hallo Martin,

    vielen Dank für die super Erklärung.

    Leider klappt es bei mir bisher noch nicht. Allerdings handelt es sich bei meinen Werten des ersten Parameters um Werte, die in einer Dropdown-Liste stehen. Des Weiteren sind die Werte des zweiten Parameters auf einem zweiten Tabellenblatt angegeben.

    Könnte es evtl. daran liegen? Oder muss ich den Fehler woanders suchen?

    Besten Dank schon mal!

    Viele Grüße
    Sandra

    • Martin Weiß Autor des Beitrags

      Hallo Sandra,

      das sollte grundsätzlich keinen Unterschied machen. Wichtig ist nur, dass die Anzahl der Zeilen in allen Bereichen immer gleich groß ist. Vielleicht liegt hier das Problem.

      Grüße,
      Martin

    • Martin Weiß Autor des Beitrags

      Hallo Dave,

      tut mir, aber da bist Du tatsächlich zu spät dran. Die Garantie ist schon am 03.11.2015 abgelaufen 🙂

      Schöne Grüße,
      Martin

  • Stephan

    Ich habe mehrere Tabelle
    “Profil” mit einem Feld Level (Mögliche Eingträge hier A, B, C, oder D aus einem Drop-Down Feld
    “Fragen_All” mit geclusterten Fragen (Überschrift dann eine Anzahl Fragen) Jede Frage kann mit einer Drop-Down Auswahl beantwortet werden! Jede Frage hat einen oder mehrere Einträge zum Feld Level in Tabelle “Profil” A, B, C, oder D manche auch mehrere manche auch nur A oder B. Je nach Beantwortung der Frage ergeben sich Punkte die kummuliert werden.

    Ziel ist es jetzt ein dynamisches Tabellenblatt zu bauen “Dyn-Blatt” welches je nach Selektion in Blatt Profil z.B. “A” alle Fragen inkl. der Drop-Down-Felder und Punkte Analyse die Dyn-Blatt Tabelle kopiert.

    Wenn ich die gleiche Funktion mit einem Ausblenden von Zeilen in Tabelle “Fragen_All” lösen kann würde ich auch in einem Blatt arbeiten.

    Hauptziel ist ein dynamischer Fragebogen mit der dazugehörigen Auswertung.
    z.b. Auswahl “A” dann 75 Fragen oder Auswahl “B” dann 100 Fragen.

  • Marce

    Hallo Martin,

    super Anleitung, vielen Dank. Auch die anderen Tipps hier sind übersichtlich und verständlich erklärt. Weiter so.

    Allerdings hat das oben gezeigte Beispiel für meine Datei nicht funktioniert (Ergebnis #NV), obwohl es sich bis auf die Zelladressen um genau das gleiche Beispiel handelt. Sprich, es werden zwei Werte verglichen und der Dritte Wert angegeben, nur das meine Quelltabelle weiter hinten im Blatt liegt. Das sollte jedoch nicht stören.

    Meine Formel schaut so aus, sie wurde auch als Array-Formel abgeschlossen (geschweifte Klammern sind da):
    =SVERWEIS(B1&B2;WAHL({8.9};H2:H10&I2:I10;J2:J10);2;FALSCH)

    Bei mir kommt allerdings hinzu, dass B1 und B2 (also die Quellzellen) nicht fix sind, sondern es sich um Dropdown Listen handelt. B1 ist dabei eine statische Dropdown Liste, B2 ist eine (wie von Dir hier auch beschriebene) dynamische DropDownliste (via INDIREKT) mit Bezug auf die Erste. Ich vermute, dass der Super-SVerweis deshalb nicht funktioniert.

    Hast Du dafür eine Lösung?

    Vielen Dank im Voraus,
    Gruß Marcel

    • Martin Weiß Autor des Beitrags

      Hallo Marcel,

      wie Du weiter unten geschrieben hast, bist Du ja doch noch auf eine Lösung gekommen. Warum die im Beitrag vorgestellte Variante nicht funktioniert, kann ich nicht ganz nachvollziehen. Denn auch im Beispiel arbeite ich mit Dropdown-Feldern. Aber vielleicht liegt es ja wirklich an den dynamischen Dropdown-Listen. Aber Hauptsache, Du hast eine funktionierende Lösung.

      Schöne Grüße,
      Martin

  • Marcel

    Hallo Martin,

    ich habe die Herausforderung nun auf einem anderen Weg gelöst und zwar damit:
    =INDEX(J:J;VERGLEICH(B1&B2;H1:H10&I1:I10;FALSCH)) … natürlich mit dem obligatorischen CSE-Kommando.

    Dabei muss man die zu vergleichenden Matrizen jedoch immer mit der ersten Zeile (wie oben H1) beginnen lassen, sonst verschiebt sich das Ergebnis um eins nach unten.

    Über eine Antwort zu meinem vorherigen Kommentar freue ich mich dennoch.

    Gruß Marcel

  • Unger

    Hallo, ich habe heute versucht ebenso aus einer Tabelle2 etwas mit Parameter in eine Tabelle1 einzulesen. Dieses habe ich mit dem Sverweis (normal) versucht und wollte angaben zu dem Excel – File und den unterschiedlichen Arbeitsblättern Parametrisieren. Habe es nicht geschafft. Gibt (und ich bin mir sicher das es sie gibt) eine Lösung. Manuell funktioniert es natürlich, da ich ja nur in das zweite Excelfile gehen muss und den Zellenbezug mit sverweis herstellen kann, aber automatisch eben nicht.

    • Martin Weiß Autor des Beitrags

      Hallo Unger,

      ohne zu wissen, wie die Tabellen aussehen und was genau von wo geholt werden soll, wird eine Antwort schwierig. Wenn Du willst, kannst Du mir die Tabelle gerne per E-Mail (info@tabellenexperte.de) zusenden und ich werden einen kurzen Blick darauf werfen.

      Schöne Grüße,
      Martin

  • PeterK

    Hallo Martin,

    ich habe eine Liste mit 90.000 Einträgen. Kann das sein, dass WAHL so viele Zeilen nicht unterstützt? Wenn ich jetzt zum Beispiel nur die ersten 200 Zeilen in meine Matrizen der Formel aufnehme, dann funktioniert es und ich bekomme ich Werte ausgegeben.

    • Martin Weiß Autor des Beitrags

      Hallo PeterK,

      das kann ich so nicht bestätigen. Ich habe gerade eine Liste mit 90.000 Einträgen getestet und die Formel funktioniert bei mir einwandfrei. Vielleicht hast Du irgendwo einen kleinen Wurm mit den Bezügen reingebracht.

      Schöne Grüße,
      Martin

  • Benjamin

    Hallo Herr Weiß,
    ich bin über Google auf Ihre Seite gestoßen.
    Eine Frage hätte ich noch.
    Wie könnte ich die Einwohnerzahl summieren?
    Angenommen ich habe zwei Zeilen
    5 -10 100000
    5- 10 200000

    Ich mache einen SVERWEIS auf 5 -10 und möchte den Wert 300000 rausbekommen.
    Mein aktuelles Statement zeigt mir nur die 100000 an.
    =SVERWEIS(A1;WAHL({1.2};Sheet1!A$2:A$3170;Sheet1!V$2:V$3170);2;FALSCH)
    In Sheet1 A… stehen z.B. die Werte 0-15 mehrere male. In A1 steht auch die 5-10. In Sheet 1 V… stehen die Werte 100000, 200000 etc.

    Beste Grüße

    • Martin Weiß Autor des Beitrags

      Hallo Benjamin,

      SVERWEIS liefert immer nur den ersten gefundenen Wert zurück. In Ihrem Fall wäre die SUMMEWENN-Funktion das geeignete Mittel.

      Bezogen auf Ihr Beispiel würde das so z.B. aussehen:
      =SUMMEWENN(A1:A100;”5-10″;V1:V100)

      Damit werden alle Werte in Spalte V summiert, bei denen in Spalte A der Eintrag “5-10” steht.

      Wichtig ist lediglich, dass die Schreibweise des Suchkriteriums (also “5-10”) exakt so ist, wie sie auch in Spalte A vorkommen (also auf Leerzeichen etc. achten)

      Schöne Grüße,
      Martin

  • Gernot

    Hallo Martin,
    ich bin sehr begeistert von Super-SVERWEIS. Ich habe die Funktion überall eingesetzt. Mittlerweile habe ich aber festgestellt, dass wenn der Rückgabewert größer als 255 Zeichen ist, wird nur #WERT angezeigt. Gibt es da ein work around?

    • Martin Weiß Autor des Beitrags

      Hallo Gernot,

      diese Beschränkung war mir bisher noch nicht bekannt, daher kann ich mit einem Workaround leider auch nicht dienen…

      Tut mir leid.

      Schöne Grüße,
      Martin

  • Ali Nada

    Hallo Martin,

    ich versuche gerade deinen Super-SVERWEIS hinzubekommen, aber etwas scheint nicht zu funktionieren. Ich habe den Datensatz nicht wie du auf demselben Tabellenblatt. Ich habe extra ein Tabellenblatt “Datensatz” für die nötigen Daten angelegt. Diese Formel benutze ich derzeit: =SVERWEIS(F2&F3;WAHL({1.2};Tabelle1!A2:A41&Tabelle1!B2:B41;Tabelle1!C2:C41);2;FALSCH)
    Bei mir kommt jedoch kein Wert sondern #NV 🙁
    Woran liegt das und wie kann ich das Problem beheben?

    Vielen Dank im voraus!
    Ali Nada

    • Ali Nada

      Tut mir leid, das war die falsche Formel. Ich benutze folgende Formel:
      =SVERWEIS(B1&B2;WAHL({1.2};Datensatz!A2:A65&Datensatz!B2:B65;Datensatz!C2:C65);2;FALSCH)

      • Martin Weiß Autor des Beitrags

        Hallo Ali,

        die Formel funktioniert grundsätzlich auch, wenn sich die Daten in einem anderen Arbeitsblatt befinden. Ich sehe also zwei Fehlermöglichkeiten:
        1. Die gesuchten Daten sind tatsächliche nicht in der Referenztabelle (Tippfehler, Leerzeichen etc.)
        2. Du hast die Formel nicht mit STRG+Umschalt+Eingabe abgeschlossen

        Ich tippe eher mal auf Punkt 2 🙂

        Schöne Grüße,
        Martin

  • Sascha

    Guten Tag!
    Zunächst mal, danke für die tolle Seite und die klasse Erklärungen. Das hat mri schon an mancher Stelle weiter geholfen. Auch der Super-SVERWEIS würde sich als extrem hilfreich erweisen, allerdings klemmt es noch an einer ganz bestimmten Stelle.
    Meine Formel lautet: {=SVERWEIS(C7&E12;WAHL({1.2};’Drop Down Data’!A26:A33&’Drop Down Data’!B26:B33;’Drop Down Data’!C26:C33);2;FALSCH)}

    C7 und E12 sind DropDown-Felder, und C26:C33 auf dem Tab Drop Down Data sind Zellen mit mehrzeiligem Text darin. In dieser Konstellation erhalte ich als Ausgabe #WERT!. Ersetze ich den Text durch einfache Zahlen, funktioniert es. Es scheint also mit meinem Text zusammen zu hängen – kann ich auf diese Weise eine Zelle nicht mit Text füllen? Oder liegt das Problem in der Formatierung? Was könnte ich sonst noch falsch gemacht haben?

    Vielen Dank im Voraus für jedweden Tipp. 🙂

    • Martin Weiß Autor des Beitrags

      Hallo Sascha,

      das Problem liegt ziemlich sicher in der Textlänge der Einträge in Spalte C. Die Formel kann mit maximal 255 Zeichen umgehen, alles was länger ist, führt zu dem #WERT!-Fehler.

      Schöne Grüße,
      Martin

      • Sascha

        Hallo Martin,

        ja, danke für die schnelle Antwort, das war das Problem. Nachdem ich die Texte entsprechend gekürzt habe, funktioniert es auch damit.

        Leider habe ich jetzt ein neues Problem: ich habe mir selber eine Zellformatierungsfalle gestellt. Damit die Zelle, in die die Texte kopiert werden müssen, überhaupt groß genug ist, muss ich sie mit anderen verbinden. Leider streikt Excel beim Versuch, dort den Super-SVERWEIS anzuwenden mit der Meldung “Sie können keine Arrayformel über verbundene Zellen eingeben”.

        Jetzt gibt es zwei Möglichkeite: entweder gibt es noch einen Trick, mit dem ich das trotzdem da unterbringen kann, oder ich muss mein Formular aufwändig neu gestalten; ich hoffe ganz feste auf einen Trick?

        Nochmal danke, auch im Voraus.

        Beste Grüße,
        Sascha

        • Sascha

          Und noch ne dumme Frage: wenn ich beim Kopieren der Formel möchte, dass die selben Zellen (A26:A33, etc.) benutzt werden, wie müsste die Formatierung dann aussehen? $A26:A33 funktioniert irgendwie nicht, der Bereich wird beim Kopieren trotzdem dynamisch verschoben, weshalb ich dann #NV als Ergebnis kriege.

          Das Problem mit der Arrayformel in verbundenen Zellen habe ich jetzt erstmal so gelöst, dass ich erst die Formel eingeb und dann die Zellen verbinde – geht das vielleicht irgendwie eleganter?

          Danke!
          Sascha

          • Martin Weiß Autor des Beitrags

            Hallo Sascha,

            Du hast mit den Zellbezügen schon richtig begonnen, aber nicht bis zum Ende umgesetzt. Das Dollarzeichen nur am Anfang reicht nicht, es muss heißen $A$26:$A$33.
            Damit sind sowohl Spalten- als auch Zeilenbezug fixiert, und zwar von Anfang bis Ende.

            Schöne Grüße,
            Martin

        • Martin Weiß Autor des Beitrags

          Hallo Sascha,

          ich fürchte, da sieht es schlecht aus. Einen Trick hierfür kenne ich leider auch nicht.

          Schöne Grüße,
          Martin

          • Sascha

            Hallo Martin.

            Okay, alles klar, danke nochmal für die tollen Tipps, das hat mir wirklich sehr weitergeholfen!

            Schönes Wochenende,
            Sascha

        • Ruben

          Hallo Sascha,

          Diese Formel bringt das gleiche Ergebnis, kann aber auch in verbundenen Zellen genutzt werden:
          =INDEX(‘Drop Down Data’!$C$1:$C$33;SUMMENPRODUKT((‘Drop Down Data’!$A$26:$A$33=E3)*(‘Drop Down Data’!$B$26:$B$33=F6)*ZEILE(‘Drop Down Data’!$C$26:$C$33)))

          • Ruben

            Tut mr leid, mir ist ein kleiner Fehler unterlaufen…hier die Formel, die das gleiche tut
            =INDEX(‘Drop Down Data’!$C$1:$C$33;SUMMENPRODUKT((‘Drop Down Data’!$A$26:$A$33=C7)*(‘Drop Down Data’!$B$26:$B$33=E12)*ZEILE(‘Drop Down Data’!$C$26:$C$33)))
            Im übrigen lässt sich so die 255 Zeichen Beschränkung umgehen

  • Thomas

    Hallo Martin,

    Klasse Arbeit!
    Dein Beispiel habe ich umgesetzt und es funktioniert auch einwandfrei.
    In meinem Fall habe ich aber Daten auf 2 Reitern in Excel.

    Basierend auf Werten aus dem ersten Reiter (2 Suchkriterien) und einem Wert auf dem zweiten Reiter (immer ein ‘x’), also insgesamt 3 Suchkriterien. möchte ich ein Feld aus dem 2. Reiter auf dem ersten Reiter darstellen.
    Meine Formel sieht wie folgt aus:

    {=SVERWEIS(A16&B16&”x”;WAHL({1.2.3};Rohdaten!A2:A500&Rohdaten!B2:B499&Rohdaten!H2:H499; Rohdaten!A2:H499);5;FALSCH)}

    Ale Ergebnis erhalte ich: #NV
    Ich setze also die Werte aus den Spalten A+B (aus dem ersten Reiter) +”x” zusammen und nehme das als Suchbegriff.
    Die jeweiligen Suchwerte stehen dann auf dem Reiter 2 (Name:Rohdaten):
    Spalte A, B und Spalte H (H steht das gesuchte “x”).
    Ich setze als Suchbereich die spalten A bis H auf dem 2. Reiter.
    Das gesuchte feld mit dem gesuchten Inhalt steht in Spalte D (=4) auf dem 2. Reiter.

    Beispieldaten:
    Reiter 1:
    Stichtag Meilenstein Plan Start
    21.12. ABCD (hier soll das Ergebnis meiner Suche rein)

    Reiter 2:
    Stichtag Vorgangsname Status Anfang Text13 Ende Text12 Relevant
    21.12. ABCD 3 19.12.2016 16:30:00 (Feld ist leer) 20.12.2016 12:30:00 (Feld ist leer) x

    • Thomas

      Kurzer Nachtrag:
      In derie Formel muss natürlich …;4;FALSCH) anstatt …;5;FALSCH) heißen, da ich den Wert aus der 4. Spalte benötige.

      Gruß
      Thomas

      • Martin Weiß Autor des Beitrags

        Hallo Thomas,

        so wie ich es sehe, sind die Datenbereiche nicht gleich groß: Der erste Bereich geht von Zeile 2 bis Zeile 500, alle anderen von Zeile 2 bis Zeile 499. Die Bereiche müssen alle gleich groß sein, damit es funktioniert.

        Schöne Grüße,
        Martin

        • Thomas Mauritz

          Hallo Martin,
          ich habe das noch einmal geprüft. Die bereiche sind identscih von zeile 2 bsi zeile 499.

          Ich habe daraufhin die Formel neu aufgebaut, zuerst ohne dem 3. Suchkriterium “x”.
          Dann gibt mir die Formel auch ein Ergebnis aus.

          Da die Zeile aber mehrfach vorkommen kann, benötige ich aber das Suchkriterium “x”.
          Wenn ich die Formel dann umbaue:

          Beispiel:
          =SVERWEIS(A56&B56&”x”;WAHL({1.2.3};Übersicht!A2:A499&Übersicht!B2:B499&Übersicht!F2:F499;Übersicht!D2:D499);3;0) und diese mit STRG+SHIFT+ENTER bestätige, erhalte ich als Ergebnis:

          #NV
          Ich habe daraufhin einmal alle 3 Spalten, nach denen ich in dem 2. Register ‘Rohdaten’ suche, direkt hintereinander angeordnet (Also Spalte A+B+C –> war vorher Spalte A+B+F).
          Dann bekomme ich auch das gewünschte Ergebnis.

          Damit passt das einwandfrei, denn die Spaltenreihenfolge kann ich über das Exportscheme aus MS Projekt selbst bestimmen.

          Im Endergebnis: Diese verrückte Formel haut astrein hin (freu, freu freu).

          Vielen Dank
          Gruß
          Thomas

          • Martin Weiß Autor des Beitrags

            Freut mich, wenn’s doch noch geklappt hat.

            Schöne Grüße,
            Martin

  • Ruben

    Hallo!

    Vielen Dank für die schöne Erklärung der Funktion. Aber wäre für solche Operationen nicht diese Formel einfacher zu verstehen?
    =SUMMENPRODUKT((F3=B:B)*(F2=A:A)*(C:C))
    Ist nur so ein Gedankengang. Ich bevorzuge Summenprodukt an vielen stellen, so benutzt ist es für mich halt nur eine Anreihung von Bedingungen

    Viele Grüße

    Ruben

    • Martin Weiß Autor des Beitrags

      Hallo Ruben,

      die Variante mit SUMMENPRODUKT ist hierfür eine ausgezeichnete Lösung. Die Funktion ist wirklich unfassbar vielseitig und führt leider oft ein Schattendasein.

      Vielen Dank für den guten Tipp!

      Schöne Grüße,
      Martin

  • Guido

    Hallo Martin,
    das ist ein super Tipp.
    Ich wollte nur nochmal anmerken, dass auch bei mir mit deiner exakten Tabelle ein #Bezug Fehler beim Super-Sverweis auftaucht.
    Ein anderes Kommentar hatte erwähnt, dass der sverweis mit einem Backslash funktioniert und das ist tatsächlich so.
    {=SVERWEIS(BM6&BM7;WAHL({1.2};BI6:BI13&BJ6:BJ13;BK6:BK13);2;FALSCH)} ergibt “#BEZUG!”
    {=SVERWEIS(BM6&BM7;WAHL({1\2};BI6:BI13&BJ6:BJ13;BK6:BK13);2;FALSCH)} ergibt das gewollte Ergebnis.
    Excel Version: 2016.
    Vielleicht hat sich da ja was geändert in der neuen Version?!
    Grüße

  • Benjamin

    Vielen Dank für die tolle Formel.

    Allerdings liefert die Formel ein falsches Ergebnis wenn die Einwohnerzahl “LEER” ist. Dann ist das Ergebnis 0 und nicht “LEER”.

    Gibt es eine Möglichkeit, dass in diesem Fall “LEER” zurückgegeben wird?

    • Martin Weiß Autor des Beitrags

      Hallo Benjamin,

      ja, das geht. Dazu musst Du die Formel einfach noch um eine WENN-Funktion erweitern, in der Du auf das Ergebnis 0 prüfst und für diesen Fall “Leer” oder eine Leerzeichen ausgibst:
      {=WENN(SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C41);2;FALSCH)=0;”Leer”;SVERWEIS(F2&F3;WAHL({1.2};A2:A41&B2:B41;C2:C41);2;FALSCH))}

      Schöne Grüße,
      Martin

  • Mosterrider

    Vielen Dank für die schön geschriebene Lektüre. Ich stehe vor dem Problem, dass ich gern eine Zahl eingeben möchte also Alter 0-5; statt dem dropdown, tippe ich die Zahl 3 in das Feld und es sollte mir 1.693.000 Einwohner anzeigen. Ich benötige dies für Mengenangaben und zeige dann den Endpreis des Artikels an. Was kann man hier tun?

    • Martin Weiß Autor des Beitrags

      Hallo Mosterrider,

      damit das funktioniert, müsstest Du die Altersspalte auf zwei Spalten aufteilen (Alter von / Alter bis) und der “Super-SVERWEIS” muss durch eine andere Variante ersetzt werden. In der Kurzform:
      Spalte A: Alter von (numerischer Wert)
      Spalte B: Alter bis (numerischer Wert)
      Spalte C: Geschlecht
      Spalte D: Einwohner
      G25: gewünschtes Alter
      G25: gewünschtes Geschlecht
      G27: gesuchte Einwohner: =SVERWEIS(G3;INDIREKT(“C”&VERGLEICH(G2;A2:A42;1)&”:D”&”41″);2;FALSCH)

      Nähere Erklärungen würden den Rahmen hier sprengen, ich werde vielleicht mal einen Blogartikel dazu schreiben.

      Schöne Grüße,
      Martin