Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2) 24

INDEX + VERGLEICH nochmal aufgebohrt: Ein kleiner Trick ermöglicht mehrere Suchkriterien.
 

Der erste Beitrag dieses kleinen Zweiteilers endete mit einem Cliffhanger. Ich habe dir zuerst den Mund wässrig gemacht und dich dann auf den zweiten Teil vertröstet. Nicht sehr schön…

Auf der anderen Seite, so funktionieren eben echte Thriller. Was Dan Brown und Co können, klappt auch mit Excel 😉

Doch genug der Vorrede. Hier kommt die Auflösung zu der Frage, wie sich mehrere Suchkriterien in einer INDEX-VERGLEICH-Formel verarbeiten lassen.

Ein kurzer Rückblick

Zur Erinnerung:
Aus einer tapetenartigen Tabelle sollen per Formel ganz gezielt bestimmte Werte herausgepickt werden. Dazu habe ich in Teil 1 dieser zweiteiligen Artikelserie eine Kombination aus einer INDEX und zwei VERGLEICH-Funktionen verwendet. Diese Lösung funktioniert soweit ganz gut, hat jedoch zwei Schönheitsfehler.

Um in der Rohdaten-Tapete nach Ist-, Vorjahres- und Planwerten unterscheiden zu können, musste der Bezug in einer der beiden VERGLEICH-Funktionen jeweils angepasst werden.
Außerdem war es mit dieser Lösung noch nicht möglich, ein weiteres Selektionskriterium, nämlich die Unterscheidung nach Umsatz oder Gewinn, zu berücksichtigen.

Die ursprüngliche INDEX-Formel

Die ursprüngliche INDEX-Formel

Und genau mit diesen beiden Schwachpunkten werden wir mit der heutigen Variante aufräumen.

Die INDEX-Formel noch weiter aufgebohrt

Die ersten beiden Teile der verschachtelten INDEX-Formel aus dem letzten Artikel können wir unverändert übernehmen. Die zu durchsuchende Matrix passt und auch die erste VERGLEICH-Funktion, mit der die jeweils benötigte Zeile ermittelt wird, funktioniert einwandfrei. Der Knackpunkt ist die zweite VERGLEICH-Funktion, mit der die richtige Spalte in unserer Rohdatentabelle bestimmt werden soll.

Die zweite VERGLEICH-Funktion reicht noch nicht

Die zweite VERGLEICH-Funktion reicht noch nicht

Um die korrekte Spalte zu finden, muss die Tabelle nach drei Kriterien durchsucht werden:

  • Monat -> aus Zelle A5, A6, A7…
  • Umsatz / Gewinn -> aus Zelle B2
  • Ist / Vorjahr / Plan -> aus Zelle B4 / C4 / D4

Daher werden wir für das erste Argument einfach diese drei Kriterien mit Hilfe des kaufmännischen Und-Zeichens („Ampersand“) miteinander kombinieren. Für den ersten Wert in Zelle B5 sieht das so aus:
...VERGLEICH($A5&$B$2&B$4;...
Achte gleich von Anfang an darauf, die Dollarzeichen an den richtigen Stellen zu setzen, da wir später diese Formel ja in die restlichen Zeilen und Spalten kopieren wollen.

Logischerweise besteht der zu durchsuchende Teil in der VERGLEICH-Funktion ebenfalls aus drei Bereichen, nämlich den Zeilen 20, 21 und 22 aus der Rohdatentabelle. Falls du jetzt auf die Idee kommen solltest, diese ebenfalls miteinander zu kombinieren, bist du bereits auf dem richtigen Weg.
...VERGLEICH($A5&$B$2&B$4;$B$20:$BU$20&B$21:BU$21&B$22:BU$22;0)

Die angepasste VERGLEICH-Funktion

Die angepasste VERGLEICH-Funktion

Aber warum kommt jetzt dieser dämliche #WERT!-Fehler?

Weil die VERGLEICH-Funktion grundsätzlich als Suchmatrix nur eine Spalte oder eine Zeile durchsuchen kann. Um einen mehrzeiligen Bereich verwenden zu können, muss die Formel in eine Matrixformel umgewandelt werden. Also einfach nochmal die Formelzelle markieren, mit der Funktionstaste F2 in den Bearbeitungsmodus schalten und dann die Eingabe mit Strg+Umschalt+Enter abschließen (die Strg+Taste drücken und gedrückt halten, dann die Umschalt-Taste dazunehmen und ebenfalls gedrückt halten und zum Schluss noch die Enter- oder Eingabe-Taste drücken).

Und schon haben wir das gewünschte Ergebnis:

Die nochmal angepasste VERGLEICH-Funktion

Die nochmal angepasste VERGLEICH-Funktion

Dass es sich bei dem Konstrukt um eine Matrix-Formel handelt, erkennt man an den geschweiften Klammern, die durch die oben genannte Tastenkombination zustande kommen. Auf keinen Fall dürfen diese Klammern direkt über die Tastatur eingetippt werden!

Jetzt kannst du die Formel einfach in alle anderen Zellen kopieren (sofern du die Dollarzeichen wie oben beschrieben richtig gesetzt hast) und bekommst für jedes Feld die korrekten Werte. Spiele einfach mit den beiden Eingabezellen B1 und B2 herum und beobachte, wie sich die Werte verändern.

Die fertige Matrix

Die fertige Matrix

Auf diese Weise wäre es auch möglich, noch weitere Kriterien zu berücksichtigen. Die Grenzen setzt nur deine Fantasie (und die Rohdatentapete)!

THE END

Ach ja: Die fertige Beispieldatei kannst du dir hier herunterladen.

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.

24 Gedanken zu “Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2)

  • Michael Löffler

    Hallo Martin,

    danke für die tolle Erklärung der beiden Funktionen, die ich bisher noch nicht verwendet habe.
    Ich habe Deine Beispiele nachvollzogen und möchte sie demnächst mal einem komplexen eigenem Beispiel anwenden.

    Die von Dir bereitgestellte Datei im Teil 2 erhält zwei kleine Fehler, die sich jedoch nicht auf die Ergebnisse auswirken 🙂
    Prüfe bitte die Formel im Feld „Vorjahr“ und „Plan“.

    Liebe Grüße
    Michael

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      vielen Dank für den Hinweis. Da predige ich im Artikel, dass man die Dollarzeichen richtig setzen muss und übersehe es dann selbst. Zum Glück gibt es aufmerksame Leser wie Dich 🙂
      Ist jetzt in der Beispieldatei korrigiert.

      Schöne Grüße,
      Martin

    • Werner alias neopa oder auch neopa C

      Hallo Martin,

      mit exakt den gleichen Funktionen, wie bereits von Dir benutzt, gibt es auch eine alternative Lösungsformel, ohne (!) die Formel mit dem spez. Eingabeabschluss erstellen zu müssen.
      Es bedarf lediglich einer zusätzlichen INDEX()“-Klammerung“ innerhalb des VERGLEICH()-Formelteiles.

      =INDEX($B$23:$BU$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5&$B$2&B$4;INDEX($B$20:$BU$20&$B$21:$BU$21&$B$22:$BU$22;);0))

      Gruß Werner
      .. , – …

      • Martin Weiß Autor des Beitrags

        Hallo Werner,

        vielen Dank für die schöne Formel, auch eine sehr interessante Lösung!

        Schöne Grüße,
        Martin

      • marius36

        Hallo
        mich interessiert die Alternative OHNE die Matrix-Formel sehr. Ich benutze nämlich Numbers von Apple und dieses Programm kann soweit ich weiß keine Matrix-Formeln anwenden. Jetzt habe ich allerdings diese alternative Lösung nicht verstanden und sie hat auch in der Beispieldatei nicht funktioniert. Weiß irgendjemand wie das auch ohne eine Matrix-Formel funktioniert und kann mir das erklären?

        In meinem speziellen Fall habe ich eine große Tabelle. In den Spalten stehen die Monate von Jan – Dez. links neben der Tabelle stehen einerseits Himmelsrichtungen wie Süden oder Osten, andererseits aber auch zu jeder Himmelsrichtung Neigungen von 30° oder 60°. In der Tabelle selbst stehen dann die Parameter, die ich wissen will. Diese hängen somit von Himmelsrichtung und Neigung, als auch von dem Monat ab.

        Ich hoffe jemand kann mir da helfen. Ich habe nirgendwo sonst eine Lösung gefunden.

  • Bastian Burger

    Hallo Martin
    Vielen Dank für den Beitrag!
    Dank früherer Beiträge verwende ich nur noch Index- eine Hammerfunktion!
    Die Kombination mit Vergleich als Martix habe ich mal zufällig gefunden – wurde gleich zum Standardrepertoire. Hier ist sie aber viel besser erklärt! Den Link verteile ich gerne weiter!
    Auf die Anwendung von mehreren Kriterien auf Vergleich mit „&“ wäre ich nie gekommen – solche Tricks sind Gold wert!
    Gerade die „kleinen“ Sonderzeichen und Kniffe sind ja häufig der Schllüssel zum Erfolg – und somit doch mal einen Beitrag wert, oder 🙂

    Vielen Dank nochmals und beste Grüsse,
    Bastian

    • Martin Weiß Autor des Beitrags

      Hallo Bastian,

      sehr gern geschehen! Oft sind es wirklich die kleinen Tricks, die manchmal große Wirkung haben. Mal sehen, was da noch alles zu finden ist 🙂

      Schöne Grüße,
      Martin

  • Tim

    Habe jetzt etwas länger gebraucht um die Logik hinter deiner Nutzung des „&“ zu finden. Gefällt mir.
    Und jetzt habe ich wieder einen Grund mir noch einmal deine Matrix-Formel-Tipps anzusehen.
    Besten Dank und schöne Grüße
    -Tim

    • Martin Weiß Autor des Beitrags

      Hallo Tim,

      ja, das ist tatsächlich nicht immer auf Anhieb so leicht zu durchschauen. Und ich stoße ebenso immer wieder auf Formeln im Internet, bei denen es mir genauso geht. Aber der Aufwand lohnt sich meistens.

      Schöne Grüße,
      Martin

  • Tanja

    Hallo Martin,

    vorab mal generell vielen lieben Dank für deine ständige Hilfe! Ich liebe es mit Excel zu arbeiten und finde dank deiner Seite und deiner inspirierenden Beiträge, ständig neue Möglichkeiten meine Arbeitsabläufe zu verbessern und zu automatisieren. Ganz nebenbei macht es mir auch eine Menge Spaß etwas neues über Excel zu lernen!

    Auf den zweiten Teil der Vorstellung des INDEX+VERGLEICH-Teamworks habe ich mit Spannung gewartet, da es meine bisherige Arbeitsweise mit dem W- und SVERWEIS ablösen soll. Nach dem fiesen Cliffhanger aus dem ersten Teil, bin ich (wie erwartet) nicht enttäuscht worden! Die Erklärung ist wie immer super und sehr gut nachvollziehbar, so dass ich mein Excel-Repertoire jetzt mit ein wenig Zeit und Übung, um das INDEX+VERGLEICH-Teamwork erweitern kann.

    Danke! und Grüße
    Tanja

    • Martin Weiß Autor des Beitrags

      Hallo Tanja,

      vielen Dank für das schöne Feedback! Und es freut mich natürlich, wenn Du Deinen Excel-Werkzeugkoffer ein wenig erweitern konntest 🙂

      Schöne Grüße,
      Martin

  • Harald Huber

    Hallo Herr Weiß,
    danke für diesen sehr aufschlussreichen Beitrag. Gibt es auch irgendeine Möglichkeit zwei Kriterien mit „oder“ statt mit „&“ zu verknüpfen? Leider ist eine Umgehung per WENN-Funktion mit mehreren INDEX-Funktionen in meinem Fall auch nicht zielführend.
    Ich habe zwei Spalten, bei denen er mir die Zeile zurückgeben soll, wobei manchmal Spalte A den richtigen Treffer bietet, manchmal Spalte B, meist würde es auch mit „&“ passen, aber halt nicht immer. (wobei eben A oder B voll und ganz ausreicht)
    Beste Grüße
    Harald

    • Harald Huber

      Ich habe nun einen Weg mit lustiger WENN-Verschachtelung gefunden, der mich zum gewünschten Ergebnis führt. Im Prinzip hat sich das Ganze damit bereits erledigt. Nichtsdestotrotz interessehalber: gäbe es einen „Oder-Operator“, der „&“ ersetzen könnte?

      • Martin Weiß Autor des Beitrags

        Hallo Herr Huber,

        ich fürchte, in diesem Zusammenhang gibt es keinen Oder-Operator. Sie werden also vermutlich bei Ihrer WENN-Verschachtelung bleiben müssen.

        Schöne Grüße,
        Martin

  • Ide

    Vielen Dank für den tollen Blog! Hat mir schon oft weitergeholfen. Jetzt stehe ich vor einem Problem und hoffe Sie können weiterhelfen. Für eine Abfrage mit drei Bedingungen habe ich die Index Formel verwendet und habe es geschafft, dass die Formel mir die erste Zeile ausgibt, die gefunden wird. Da ich aber alle Zeilen sehen möchte, die meine Bedingungen erfüllen, habe ich mich an einer neuen Formel versucht und komme jetzt nicht weiter. Was übersehe ich hier:
    WENNFEHLER(INDEX(ZA!$A$3:$N$10;KGRÖSSTE((ZA!$C$3:$C$10=’AT12′!$B$1)*(ZA!$D$3:$D$10=’AT12′!B2)*(ZA!$E$3:$E$10=’AT12′!B3)*(ZEILE(ZA!$C$3:$C$10)-1);ZÄHLENWENNS(ZA!$C$3:$C$10;$B$1;ZA!$D$3:$D$10;$B$2;ZA!$E$3:$E$10;$B$3)+3-ZEILE(ZA!A3)));””)
    Bin dankbar für jeden Tipp!
    LG, Ide

  • Hanna

    Hallo Martin,
    vielen Dank für diese super Erklärung!!
    Eine Frage habe ich dazu noch:
    Wie gehe ich vor, wenn in meiner Rohdaten-Tapete einige Felder mit einer Null gefüllt sind? Ich erhalte dann glaube ich einen #NV-Fehler. Wie sage ich Excel in diesem Fall, dass es dann das Feld ignorieren soll, bzw. eine Null ausgeben soll?

    Viele Grüße,
    Hanna

    • Martin Weiß Autor des Beitrags

      Hallo Hanna,

      klingt merkwürdig, denn sowohl Nullwerte also auch leere Zellen führen bei mir zu keinen Formelfehlern. Nur wenn eine Zelle einen #NV-Fehler enthält, wird der auch übernommen.
      Das könnte man mit einer alles umschließenden WENNFEHLER-Funktion vermeiden.

      Schöne Grüße,
      Martin

  • Thomas Kopietz

    Hallo Martin,
    das ist wirklich eine tolle verständliche Erklärung. Selbst für mich, der wenig Ahnung von Excel hat. Ich habe die Formeln für meinen Bedarf angepasst, kann aber den nächsten Schritt, die „Tapete“ auf einem anderen Arbeitsblatt zu platzieren, nicht umsetzen. Was gibt es in der Formel zu beachten? Gerne kann ich meine Datei auch zur Verfügung stellen.
    Freundliche Grüße
    Thomas Kopietz

    • Martin Weiß Autor des Beitrags

      Hallo Thomas,

      wenn sich die Auswertung und die „Tapete“ auf unterschiedlichen Arbeitsblättern befinden, musst du in den Formeln die Bezüge zur Tapete um die Blattnamen erweitern. Angenommen, die Tapete liegt im Blatt „Tabelle2“, dann wird aus
      {=INDEX($B$23:$BU$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5&$B$2&B$4;$B$20:$BU$20&$B$21:$BU$21&$B$22:$BU$22;0))}
      die neue Formel
      {=INDEX(Tabelle2!$B$23:$BU$37;VERGLEICH($B$1;Tabelle2!$A$23:$A$37;0);VERGLEICH($A5&$B$2&B$4;Tabelle2!$B$20:$BU$20&Tabelle2!$B$21:$BU$21&Tabelle2!$B$22:$BU$22;0))}

      Falls der Blattname ein Leerzeichen enthält, musst du die Blattnamen noch in einfache Anführungszeichen setzen:
      {=INDEX(‚Tabelle 2′!$B$23:$BU$37;VERGLEICH($B$1;’Tabelle 2′!$A$23:$A$37;0);VERGLEICH($A5&$B$2&B$4;’Tabelle 2′!$B$20:$BU$20&’Tabelle 2′!$B$21:$BU$21&’Tabelle 2‘!$B$22:$BU$22;0))}

      (es muss immer das Apostroph-Zeichen verwendet werden, das über dem #-Zeichen liegt; wird hier leider nicht sauber dargestellt)

      Schöne Grüße,
      Martin

  • Jochen

    Hallo Martin,

    ich habe eine Tabelle mit ähnlichen Aufbau, komme aber nicht weiter. Der Benutzer soll bei mir jedoch drei Werte über Dropdown-Felder auswählen: Wie bei dir, die Spalten- und Zeilen-Beschriftung, wobei meine Zeilenbeschriftung für jeweils 10 Zeilen gilt. Zum besseren Verständniss in Anlehnung an deine Tabelle würde es so aussehen, dass Produkt 1 sich von Zeile 23 bis 33 erstreckt, Produkt 2 von Zeile 34-44 usw. die Zeilen der Matrix danach aber so bleiben wie deine.

    Als dritten Parameter soll der User nun den Wert der in der Matrix steht auswählen. Soll heißen, im dritten Dropdown-Feld sind die Werte in 10er-Schritten hinterlegt. Excel soll dann überprüfen, welche Zelle mit den drei Parametern übereinstimmt und wenn es den Wert des dritten Parameters nicht exakt gibt, das nächst höhere dafür nehmen. Ich werkel jetzt schon seit zwei Tagen daran, muss mir aber eingestehen ohne externe Hilfe hier nicht weiter zu kommen.

    Hoffe das ist soweit verständlich und würde mich um Hilfe/Antwort sehr freuen.

    Gruß,

    Jochen

  • Jenny

    Hallo Martin, ist es auch möglich deine Lösung mit der Summewenn bzw. Summewenns-Funktion zu verknüpfen, falls mehrere Werte auf diese Suchkritrien zutreffen und diese alle addiert werden sollen? Vielleicht hast du ja einen Tipp für mich.

    Vielen Dank im Voraus.

    Gruß
    Jenny

    • Martin Weiß Autor des Beitrags

      Hallo Jenny,

      ich fürchte, SUMMEWENN bzw. SUMMEWENNS funktioniert hier nicht, da sich die Kriterienbereiche sowohl in Zeilen (Produkte) als auch in Spalten (alles andere) befinden. Das lässt sich in einer SUMMEWENNS-Funktion nicht verarbeiten.
      Man könnte das vermutlich nur über umständliche WENN-Verschachtelungen lösen, die dann jedes Produkt einzeln prüfen, aber das wäre wenn überhaupt nur bei sehr wenigen Produkten eine Option. Bezogen auf meine Beispieldatei wäre das ein Monster dieser Art:
      =WENN($B$1=“Produkt 1″;SUMMEWENNS($B$23:$BU$23;$B$21:$BU$21;$B$2;$B$22:$BU$22;$B$4;$B$20:$BU$20;$A5);WENN($B$1=“Produkt 2″;SUMMEWENNS($B$24:$BU$24;$B$21:$BU$21;$B$2;$B$22:$BU$22;$B$4;$B$20:$BU$20;$A5)…..

      Kurz gesagt: Nicht optimal.

      Schöne Grüße,
      Martin

  • Ulrike

    Hallo Martin,

    vielen Dank für diese tolle Erklärung.
    Ich habe ein Tabelle bei der ich 2 Datumswerte abgreifen muss.
    Bsp.:
    Rohdatentabelle
    A B C D E
    01.01.2005 25.06.2008 ABCD ? Ja
    05.05.2004 01.07.2006 CDEF ? nein
    06.06.2004 02.02.2005 ABCD X vielleicht

    Meine gewünschten Ausgabewerte stehen in Spalte E.
    Ich möchte nun den Wert aus Spalte E, wenn Folgende Abfragewerte zutreffen: 02.01.2005, ABCD, X.
    Als Ergebnis sollte jetzt „Vielleicht“ rauskommen. An Welcher Stelle füge ich wie die Abfrage der Datumswerte ein?

    Vielen Dank schonmal für jeden Hinweis.

    Ulrike

    • Martin Weiß Autor des Beitrags

      Hallo Ulrike,

      dieses Problem wird sich mit der oben gezeigten INDEX-Funktion vermutlich nicht lösen lassen. Zunächst einmal muss geprüft werden, ob das Datum im Zeitraum zwischen den Werten in A und B liegt. Das würde ich in einer zusätzlichen Hilfsspalte F mit der MEDIAN-Funktion überprüfen (siehe Excel-Quickie 103), wodurch man in Spalte F WAHR oder FALSCH erhält.

      Danach würde ich über den Super-SVERWEIS (guckst du hier) alle gesuchten Kriterien abfragen. Also angenommen, du schreibst die Suchkriterien in die Zellen B1 (02.01.2005), B2 (ABCD) und B3 (X).
      Und die Rohtabelle liegt im Bereich A6:F9 (in Spalte F mit den Werten WAHR bzw. FALSCH aus der MEDIAN-Formel)
      Dann findest du den gesuchten Wert mit folgender Matrix-Formel:
      {=SVERWEIS(WAHR&B2&B3;WAHL({1.2};F6:F9&C6:C9&D6:D9;E6:E9);2;FALSCH)}

      Vielleicht hilft das ja weiter.

      Schöne Grüße,
      Martin