SVERWEIS, wie du ihn garantiert noch nicht kennst! 63

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.



Kommentar erstellen

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

63 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ß Beitragsautor

      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ß Beitragsautor

          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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

            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ß Beitragsautor

      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ß Beitragsautor

          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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

          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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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ß Beitragsautor

      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