Matrix-Problem bei der Funktion VERGLEICH

Here you can find the English version of this blog post.

Die Excel Datei (mit allen Tabellen), die ich für diesen Blog Post verwendet habe, findet ihr hier.

---

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.

[bctt tweet=“Excel akzeptiert bei der Funktion #VERGLEICH grundsätzlich nur einspaltige oder einzeilige Bereiche!“ username=“hdcnews“]

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:

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.

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.

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.

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.

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