Matrix-Problem bei der Funktion VERGLEICH 5

Sicher kennen und schätzen viele die Funktion VERGLEICH von Excel.
Mit ihrer Hilfe lässt sich die Position eines Elements (nicht dessen Wert!) in einem Array zw. einer Matrix gemäß einem Suchkriterium bestimmen.

Leider hat aber diese sehr hilfreiche Funktion einen kleinen Haken, denn – obwohl Excel ansonsten eine “Matrix” meist als mehrspaltigen oder mehrzeiligen Bereich bezeichnet – wird bei VERGLEICH grundsätzlich nur ein einspaltiger oder einzeiliger Bereich akzeptiert.

[Tweet „Excel akzeptiert bei der Funktion #VERGLEICH grundsätzlich nur einspaltige oder einzeilige Bereiche!“]

Excel_Screenshot_VERGLEICH_Werteansicht

Screenshot #1 – Werte-Ansicht

Genau dieser Umstand wird häufig zu einem Problem, wenn man also eine mehrspaltige oder mehrzeilige Matrix auswählt (in unserem Beispiel die komplette Tabelle von A2:D6).

Sehen wir uns das Ganze in der Formelansicht genauer an:

Excel_Screenshot_VERGLEICH_Formelansicht
Screenshot #2 – Formel-Ansicht

In unserem Beispiel haben wir in Zelle C10 (aktuell grün markiert) die Matrix für die VERGLEICH-Funktion auf die komplette Daten-Tabelle (A2:D6, rot markiert) festgelegt.
Wie wir aber im oberen Screenshot #1 sehen, liefert uns dies den Fehler #NV.

In der Zelle darunter haben wir die Matrix für die VERGLEICH-Funktion auf einen einspaltigen Bereich festgelegt (hier: A3:A6).
Sehen wir uns das Ergebnis (siehe Screenshot #1) an, so stellen wir fest, dass dieses uns jetzt das korrekte Ergebnis liefert.
Gleiches gilt natürlich nicht auch für eine Matrix, die sich über diverse Zeilen zeiht – sie muss also auch immer einzeilig sein.

Leider ist genau dieser Aspekt bislang in Excel nicht vermerkt – weder im Funktions-Dialog noch in der Hilfe.
Excel_Screenshot_VERGLEICH_Dialog

Excel_Screenshot_VERGLEICH_Hilfe

Lediglich die Knowledgebase von Microsoft weist auf diesen besonderen Umstand hin:

Excel_Screenshot_VERGLEICH_Onlinehilfe

Also, solltet ihr zukünftig ein Problem mit der VERGLEICH-Funktion haben, prüft zunächst, ob Eure Matrix (bzw. Eurer Array) auch wirklich nur aus einer Spalte bzw. einer Zeile besteht.

Spezielle Verwendung von VERGLEICH

Wie kann man aber diese an sich geniale Funktion nun so verwenden, dass man beispielsweise ein Element in einer Tabelle findet, das von mehreren Bedingungen abhängt?

Als Beispiel wollen wir die folgende Tabelle nutzen, wobei unsere Aufgabe lautet:

Finde den Preis für das Produkt, das aus “Mehl” besteht, dessen Typ “550” ist und dessen Verpackung gleichzeitig noch “500g” wiegt.

Excel_Screenshot_VERGLEICH_Sample_Werte

Na, habt ihr die Lösung?
Kleiner Tipp:
Ohne eine zusätzliche Funktion kommen wir hierbei leider nicht aus.
SVERWEIS hilft uns hier auch nicht wirklich weiter, weil SVERWEIS nur die erste Spalte eines Bereichs durchsucht.

Zugegeben, man könnte ganz einfach einen Filter auf die Tabelle anwenden und dann entsprechend selektieren und das Teilergebnis der gefilterten Tabelle zur weiteren Berechnung nutzen.
Was aber, wenn ich die Tabelle komplett (aus welchem Grund auch immer) darüber sehen möchte?

Lösung

Wir verwenden die INDEX-Funktion von Microsoft Excel in Kombination mit der VERGLEICH-Funktion.

Zur Vereinfachung und besseren Lesbarkeit benennen wir den Wertebereich der Tabelle (A2:D10, hellblaue Markierung) als “Mehlpreise”.
Außerdem gaben wir in die Zelle A13 den Begriff “Mehl” ein, denn nach dem wollen wir später suchen.
In die Zelle B13 geben wir “550” ein – das ist der Mehl-Typ, den wir suchen wollen und in Zelle C3 geben wir “500” ein – unsere Gewichtsangabe, nach der wir selektieren wollen.

In der grünen Zelle (D13) geben wir dann folgende Funktion ein:
=INDEX(Mehlpreise;VERGLEICH(A13&B13&C13;INDEX(Mehlpreise;;1)&INDEX(Mehlpreise;;2)&INDEX(Mehlpreise;;3);0);4)

WICHTIG:
Wir benötigen die sog. Array-Funktion – nicht die übliche Standard-Funktion.
Um Excel mitzuteilen, dass es die Array-Funktion benutzen soll, schließen wir die Eingabe nicht (wie üblich) mit <Enter> ab, sondern müssen die Eingabe mit <Strg> + <Shift> + <Enter> abschließen.

Danach sieht unsere Formel so aus:
{=INDEX(Mehlpreise;VERGLEICH(A13&B13&C13;INDEX(Mehlpreise;;1)&INDEX(Mehlpreise;;2)&INDEX(Mehlpreise;;3);0);4)}

Die komplette Funktion besitzt also nun in der Zelle zusätzlich geschwungene Klammern (hier rot markiert).

Was haben wir gemacht und warum liefert Excel das korrekte Ergebnis?

Also, wir wissen, dass VERGLEICH nur in einer einzeiligen oder einspaltigen Matrix sucht – unsere Daten-Tabelle (hier: Mehlpreise) hat aber mehrere Spalten und Zeilen.
Die Matrix-Version von INDEX wiederum liefert uns ein Element in einer (mehrspaltigen und mehrzeiligen!) Matrix.
Problem dabei ist, dass sie uns aber nicht bei der Suche nach dem Element hilft, sondern nur den Wert des Elements zurückgibt, dessen Zeile und Spalte wir in der Matrix definiert haben.

Deshalb kombinieren wir beide einfach.

Excel_Screenshot_VERGLEICH_Solution

Wir setzen als Matrix unsere vorher definierten Wertebereich der Tabelle ein – hier also „Mehlpreise”.
Bevor wir auf das Zeilen-Argument eingehen (ist etwas länger), möchte ich erst das Spalten-Argument erläutern.
Als Spalte (drittes Argument) geben wir “4” ein, denn wir wollen ja den Preis des Produktes haben und dieser befindet sich in der 4. Spalte unseres Wertbereichs.

So, jetzt kommen wir zur Zeilen-Angabe für die INDEX-Funktion (also das 2. Feld) und hier kommt der eigentliche “Trick”:
Anstatt eine fixe Zeile anzugeben, halten wir diese variable und nutzen die VERGLEICH-Funktion.

Excel_Screenshot_VERGLEICH_Solution2

Als Suchkriterium für die VERGLEICH-Funktion gaben wir eine Kombination ein, wobei wir die einzelnen Suchkriterien mit einem kaufmännischen “UND” verbinden – besser bekannt als &.

Wir geben also A13&B13&C13 ein – dort stehen ja unsere Kriterien, nach denen Excel in der Wertetabelle suchen soll.
Soweit alles klar?

Jetzt müssen wir Excel nur noch sagen, wo es diese Werte suchen soll – also in welchen Spalten (das kann übrigens sehr wichtig sein, wenn wir eine Tabelle haben sollten, in der sich identische Einträge in verschiedenen Spalten befinden sollten).

Jetzt benötigen wir erneut die INDEX-Funktion:
Wir geben als Argument für die “Suchmatrix” der VERGLEICH-Funktion zunächst die Spalte an, in der für den Wert aus A13 gesucht werden soll.
Das ist die Spalte A – aber wir wollen ja nicht einfach nur den Bereich (also: A2:A10) markieren, sondern unseren schönen Namensbereich “Mehlpreise” wiederverwenden.
Deswegen nutzen wir die INDEX-Funktion und sagen dieser, sie soll aus dem Bereich “Mehlpreise” die erste Spalte nehmen – also

INDEX(Mehlpreise;;1)

Die Zeilenangabe in dieser INDEX-Funktion benötigen wir nicht, denn wir wollen ja die komplette Spalte zurückgeliefert bekommen, damit Excel mit VERGLEICH darin nach “Mehl” suchen kann.

Damit Excel aber auch gleichzeitig(!) nach unserem Eintrag der Zelle B13 sucht (also dem Mehl-Typ), hängen wir an die erste INDEX-Funktion eine zweite an, wobei wir – wie im Suchkriterium-Argument – das kaufmännische UND “&” verwenden.
Diesmal sagen wir Excel aber, es soll in Spalte 2 unserer Mehlpreise suchen:

INDEX(Mehlpreise;;1)&INDEX(Mehlpreise;;2)

Schließlich müssen wir noch die dritte Spalte festlegen, weil wir ja noch nach dem Gewicht (eingegeben als Kriterium in die Zelle C13) suchen wollen.

INDEX(Mehlpreise;;1)&INDEX(Mehlpreise;;2)&INDEX(Mehlpreise;;3)

Jetzt müssen wir nur noch für das Argument “Vergleichstyp” in der Funktion VERGLEICH (siehe letzter Screenshot) den Wert 0 eingeben (3. Argument/Feld), weil wir ja eine eindeutige Übereinstimmung haben wollen.

BINGO…. das war’s ;)

Viel Spaß Euch allen “da draußen” mit Excel und viele Grüße
Eric

5 thoughts on “Matrix-Problem bei der Funktion VERGLEICH

  1. Reply RH Okt 24,2015 1:55

    Sehr gute und anschauliche Darstellung.

    Eine Frage dazu:

    Ich möchte mit dem Ergebnis(bei mir ein Datum)als Bedingung in einer weiteren Formel weiterrechnen.

    Es bestehen bei mir naturgemäß mehrere Fundstellen. Excel nimmt aber die Oberste obwohl ich ausgerechnet die Unterste benötigte.

    Was nun?

    Vielen Dank

    RH

  2. Reply Eric Haas Okt 27,2015 1:57

    Hallo RH,

    vielen Dank für Deinen Kommentar.

    Lass‘ mich bitte vorab folgendes klarstellen:
    Mein Blog-Beitrag bezieht sich auf ein Phänomen mit Excel’s VERGLEICH-Funktion und wie man dies lösen bzw. hilfreich verwenden kann.

    In dem von dir genannten Fall würde ich nicht auf die VERGLEICH()-Funktion zurückgreifen, sondern die Array-Funktion von MAX() vorziehen.

    PRÄMISSE:
    Ich gehe davon aus, dass du als „Unterste“, den jeweils letzten (und damit auch wertmäßig größten) Datums-Eintrag in deiner Tabelle meinst, der den angegebenen (Such-)Kriterien entspricht, richtig?

    Damit du (und andere auch) das Beispiel nachvollziehen können, möchte ich es an meiner oben bereits vorhandenen Tabelle zeigen.
    Dazu habe ich drei weitere Zeilen unten angefügt, die eine Kopie der Zeilen 5-7 darstellen (damit wir mehrere Fundstellen besitzen).
    Wichtig ist natürlich auch, dass der Bereich „Mehlpreise“ in der erweiterten Tabelle auf diese drei neuen Zeilen erweitert wird (also auf den Bereich A2:D14).
    Danach ersetzen wir noch die Preis-Spalte durch eine Spalte mit Datumswerten.
    Jetzt hoffe ich, dass ich die Tabellenstruktur für dein Problem zumindest halbwegs nachvollzogen habe? ;)
    Tabelle (Problem-Stellung)

    LÖSUNG:
    Als Formel verwende ich NICHT die VERGLEICH()-Funktion, sondern die MAX()-Funktion:
    {=MAX(WENN(A19=INDEX(Mehlpreise;;1);WENN(B19=INDEX(Mehlpreise;;2);WENN(C19=INDEX(Mehlpreise;;3);INDEX(Mehlpreise;;4)))))}

    Bitte daran denken:
    Wir benötigen zwingend die Array-Funktion von MAX(), weswegen die Formel-Eingabe nicht (wie üblich) mit <Enter>, sondern mit <Strg> + <Shift> + <Enter> abgeschlossen werden muss.

    Ich hoffe, ich konnte dir helfen und wünsche viel Erfolg mit der Lösung.

    Viele Grüße
    Eric

  3. Reply San Feb 15,2016 11:06

    Guten Abend,

    ich habe diese Konstruktion in einem Fall verwendet, in dem der Vergleich auf Werte zugreift, die aus Formeln berechnet werden. Es entsteht dabei der Fehler #Bezug. Kopiere ich das Konstrukt komplett und füge es separat als Wert wieder ein, funktioniert die Funktion wieder einwandfrei. Kann man die Funktion auf Formel basierte Werte Felder nicht verwenden? Und wenn doch, wie?

    Grüße Sam

  4. Reply Simone Mrz 4,2016 2:05

    Hallo,

    bei mir funktioniert diese Formel nicht, sobald in Spalte D ein Wert doppelt vorkommt. Gibt es dafür eine Lösung?

  5. Reply Mario Apr 8,2016 1:12

    Tolle Hilfe, danke :)

Leave a Reply