Die Excel-Funktion VERGLEICH verwenden

Die Funktion VERGLEICH gehört zur Kategorie „Nachschlagen und Verweisen“.

Sie wird meistens nicht für sich allein genutzt, sondern in Kombination mit anderen Funktionen.

Im Beitrag zeige ich dir, was die Funktion ausmacht und wie du sie als Argument in anderen Funktionen verwenden kannst.

1. Beschreibung der Funktion

Mit der Funktion VERGLEICH durchsuchst du einen Bereich von Zellen, die Suchmatrix, nach einem vorbestimmten Element, dem Suchkriterium, und erhältst als Ergebnis die relative Position des Elementes im Bereich zurück.
VERGLEICH unterscheidet beim Abfragen der Suchmatrix nicht zwischen Groß- und Kleinschreibung.

2. Die Syntax

Die Syntax lautet

VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])

Dabei gilt:

Das Argument „Suchkriterium“ ist erforderlich. Es entspricht dem Wert, den du mit der Suchmatrix abgleichen möchtest.

Das Argument „Suchmatrix“ ist ebenfalls erforderlich. Sie entspricht dem Zellbereich, den du nach dem Suchkriterium durchsuchen willst.

Und schließlich das Argument „Vergleichstyp“. Es ist optional einsetzbar und gibt an, wie das Suchkriterium den Werten in der Suchmatrix entsprechen soll.

Als Vergleichstyp stehen dir zur Verfügung:

• Mit 1 suchst du nach dem größten Wert, der kleiner oder gleich dem Suchkriterium ist. Die Funktion arbeitet standardmäßig mit „1“, auch wenn du den Vergleichstyp nicht angibst.
•  Mit 0 suchst du nach dem ersten Wert, der mit dem Wert Suchkriterium genau übereinstimmt. Ist der Vergleichstyp 0 und das Suchkriterium eine Zeichenfolge, kannst du im Argument Suchkriterium die Platzhalterzeichen ? (Fragezeichen) oder * (Sternchen) verwenden. Dazu solltest du wissen, dass ein Fragezeichen ein Zeichen und ein Sternchen eine beliebige Zeichenfolge ersetzt.
• Mit -1 suchst du nach kleinstem Wert, der größer als oder gleich dem Suchkriterium ist. Findet VERGLEICH keinen übereinstimmenden Wert, wird der Fehlerwert #NV zurückgegeben.

3. Beispiele Funktion VERGLEICH

3.1 Beispiel 1-Der Vergleichstyp „0“

Erstelle dir einen unsortierten Wertebereich.

Mit dem Vergleichstyp „0“ suchst du nach genauer Übereinstimmung, um die Position des Suchkriteriums in der Suchmatrix zu bestimmen. Der gesuchte Wert muss demnach in der Matrix vorhanden sein. Ist dem nicht so, wird VERGLEICH den Fehlerwert #NV zurückgeben.

Für die Suche nach der genauen Position des Wertes „22“ schreibst du diese Formel:

=VERGLEICH(22;B2:B18;0)

Als Ergebnis erhältst du 11, das heißt, der gesuchte Wert „22“ steht an elfter Position im Bereich. Dadurch, dass das Ergebnis nicht „#NV“ ist erhältst du weiterhin die Bestätigung, dass der gesuchte Wert in der Suchmatrix vorhanden ist.

3.2 Beispiel 2-Der Vergleichstyp „1“

Verwende wieder die Suchmatrix aus Beispiel 1. Suche nach dem Wert „18“ und gib nun als Vergleichstyp die „1“ ein.

=VERGLEICH(18;B2:B18;1)

Das Ergebnis ist nun #NV. Warum?

Mit dem Vergleichstyp „1“, so die Erläuterung oben, wird nach dem größten Wert gesucht, der kleiner oder gleich dem Suchkriterium ist. Das ist der Wert „17“, der an 10. Stelle in der Matrix steht.

Beachte: Für den Einsatz der „1“ muss die Suchmatrix aufsteigend sortiert sein.

Das Ergebnis mit obiger Formel lautet nun „1“. Der gesuchte Wert steht an 1. Stelle in der aufsteigend sortierten Suchmatrix.

3.3 Beispiel 3-Der Vergleichstyp „-1“

Verwende wieder die Suchmatrix aus Beispiel 1. Suche nach dem Wert „25“ und gib nun als Vergleichstyp die „-1“ ein.

=VERGLEICH(25;B2:B18;-1)

Als Ergebnis erhältst du bei der aufsteigend sortierten Liste „#NV“, bei der unsortierten Liste ebenfalls.
Sortiere also die Suchmatrix in absteigender Reihenfolge.

Als Ergebnis erhältst du nun mit der Formel

=VERGLEICH(25;B2:B18;-1)

die Position 9 und das ist der Wert 30. Die „30“ ist der kleinste Wert in der Suchmatrix, der größer oder gleich dem gesuchten Wert „25“ ist.

4. Verwendung der Funktion als Argument in anderen Funktionen

Mit der Funktion VERGLEICH wird die Position eines gesuchten Wertes zurückgegeben, nicht der Wert selbst.

Den Wert selbst kannst du u.a. mit den Funktionen INDEX oder BEREICH.VERSCHIEBEN abfragen.

Die nachfolgende Tabelle zeigt den Umsatz von 3 Obst- und Gemüsehändlern über 3 Jahre.

Angenommen, du willst jetzt den Umsatz von Fruchtbombe im Jahr 2019 abfragen, machst du das mit der INDEX-Funktion so:

Fruchtbombe befindet sich in der 2. Zeile, 2019 in der 3. Spalte der Matrix C4:E6.

=INDEX(C4:E6;2;3)

Ergebnis: 27.990

Mit VERGLEICH kannst du die Position von „Fruchtbombe“ in der Liste B4:B6 und die Position von „2019“ in der Liste C3:E3 bestimmen.

Setze VERGLEICH mit dem Vergleichstyp „0“ nun als Argument Zeile und als Argument Spalte in die INDEX-Funktion ein:

=INDEX(C4:E6;VERGLEICH(„Fruchtbombe“;B4:B6;0);VERGLEICH(2019;C3:E3;0))

Das Ergebnis ist auch hier 27.990. Die INDEX-Funktion wird dadurch allerdings dynamisch.

Die Abfrage kannst du ebenso mit der Funktion BEREICH.VERSCHIEBEN starten, als Bezug (Startpunkt) nimmst du die Zelle B3.

=BEREICH.VERSCHIEBEN(B3;2;3)

Ergebnis: 27.990

Setze nun wieder VERGLEICH mit dem Vergleichstyp „0“ als Zeilen- bzw. Spaltenargument ein:

=BEREICH.VERSCHIEBEN(B3;VERGLEICH(„Fruchtbombe“;B4:B6;0);
VERGLEICH(2019;C3:E3;0))

Das Ergebnis ist wieder 27.990.

Auf die Möglichkeit, die Suchkriterien mittels nebengelagerten Drop-Down-Listen auszuwählen und in den Formeln einen Bezug darauf herzustellen, geht der Beitrag nicht weiter ein, weist aber darauf hin.