Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2) 8

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.

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.

8 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

  • 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