Die Arbeitsblattfunktion VERGLEICH in Excel, VBA & Google Sheets

Dieses Tutorial zeigt Ihnen, wie Sie die VERGLEICH-Funktion in Excel, VBA und Google Sheets verwenden können, um einen Wert zu finden.

Was ist die VERGLEICH-Funktion?

Die VERGLEICH-Funktion sucht nach einem Element in einer Liste und gibt eine Zahl zurück, die dessen Position in der Liste angibt.

Sie wird oft in Verbindung mit der INDEX-Funktion verwendet, um eine Nachschlageformel zu erstellen (ähnlich wie SVERWEIS oder XVERWEIS). In diesem Tutorial werden diese und weitere Anwendungsfälle behandelt.

VERGLEICH-Funktion – Exakte Übereinstimmung

Sie werden oft mit großen Datenlisten konfrontiert und müssen in der Lage sein, nach einem bestimmten Element zu suchen. Wir werden ein kleines Beispiel mit einigen Früchten verwenden. Zuerst suchen wir nach einer genauen Übereinstimmung. Hier ist das Layout unserer Daten. Wir wollen nach dem Wort in Zelle D1 suchen.

In D2 lautet unsere Formel:

=VERGLEICH(D1; A2:A5; 0)

Beachten Sie, dass wir als letztes Argument entweder 0 oder FALSCH angeben müssen, um eine genaue Übereinstimmung zu erreichen. Das Ergebnis dieser Funktion ist 2, da „Apfel“ das 2. Element in unserem Bereich ist.

Sortierte Liste

Schauen wir uns an, wie die VERGLEICH-Funktion mit einer ungefähren Übereinstimmung funktioniert. Hier haben wir eine Liste von Einträgen. HINWEIS: Die Einträge wurden in aufsteigender Reihenfolge sortiert.

In D1 haben wir gesagt, dass wir nach dem Wort „Orange“ suchen wollen. Die Formel in D2 lautet:

=VERGLEICH(D1; A2:A5; 1)

Unsere Formel ergibt das Ergebnis 2, obwohl „Orangensaft“ in der 3.  Zelle steht. Da wir nach einer möglichst großen Übereinstimmung gesucht haben, findet die Funktion entweder unser genaues Wort oder das nächstkleinere Element. Bei Textzeichenfolgen ist dies zufällig das Wort direkt vor „Orangensaft“, so dass wir das Ergebnis 2 erhalten.

Diese Fähigkeit, das nächstkleinere Element zu finden, lässt sich anhand einer Zahlensuche vielleicht leichter verstehen. Betrachten Sie dieses Layout, in dem wir die Elemente nur in 10er Schritten aufgelistet haben. Wenn wir mit unserer Formel nach einem Wert von 34 suchen, sehen Sie, dass das Ergebnis 3 lautet.

=VERGLEICH(D1; A2:A5; 1)

Dies kann gut funktionieren, wenn Sie mit „Buckets“ arbeiten und nur herausfinden wollen, zu welcher Gruppe ein Wert gehört.

Absteigende Liste

Was wäre, wenn Sie in unserem vorherigen Beispiel den Wert 34 in die größere Gruppe einordnen wollten? In unserem nächsten Beispiel hätten wir eine Liste von Versandbehältern unterschiedlicher Größe und wir müssten wissen, welchen wir verwenden sollen. Da wir sicherstellen müssen, dass wir genügend Platz haben, verwenden wir die VERGLEICH-Funktion, wobei das letzte Argument auf -1 gesetzt wird. Schauen wir uns dieses Beispiel an:

In diesem Fall müssen wir herausfinden, welcher Behälter zu unserer Größe von 495 passt. Die Formel in D2 lautet:

=VERGLEICH(D1; A1:A5; -1)

Das Ergebnis dieser Formel ist 2, was bedeutet, dass wir das zweite Element aus der Liste (den 500er) verwenden müssen, um unsere Anforderungen zu erfüllen.

VERGLEICH mit Platzhalter

Die VERGLEICH-Funktion unterstützt auch die Verwendung von Platzhaltern wie „*“ und „?“. Kehren wir zu unserer Liste mit den verschiedenen Lebensmitteln zurück. In diesem Fall haben wir unseren Suchbegriff in D1 in „Orange*“ geändert.

Unsere Formel in D2 lautet:

=VERGLEICH(D1; A1:A5; 0)

Beachten Sie, dass wir den Vergleichstyp wieder auf genaue Übereinstimmung umstellen müssen. Obwohl die Elemente durcheinander sind und unser Suchbegriff keine volle Textübereinstimmung aufwies, konnte unsere Formel das korrekte Ergebnis von 3 liefern.

Verwendung von VERGLEICH mit INDEX

Die Rückgabe der relativen Position eines Eintrags ist zwar gut, aber für den menschlichen Benutzer normalerweise nicht sehr hilfreich. Normalerweise wollen wir den entsprechenden Wert eines Elements wissen. An dieser Stelle kann INDEX ins Spiel kommen. Da INDEX eine numerische Position für Zeile und/oder Spalte benötigt, können wir die VERGLEICH-Funktion verwenden, um INDEX mitzuteilen, welches Element wir wollen. Betrachten wir das folgende Layout, in dem wir in der Lage sein müssen, die Preise für unsere Artikel nachzuschlagen.

Wir suchen nach „Apfel“. In E2 lautet unsere Formel:

=INDEX(A2:A5; VERGLEICH(E1; B2:B5; 0))

Die VERGLEICH-Funktion sucht in B2:B5 nach dem exakten Ausdruck „Apfel“. Sie wird dies im 2. Element finden und den Wert 2 zurückgeben. INDEX nimmt dies und gibt uns den 2. Wert aus dem Bereich A2:A5. Das 2. Element mit dem Wert $2.00 befindet sich in Zelle A3.

VERGLEICH in Google Sheets

Die VERGLEICH-Funktion funktioniert in Google Sheets genau so wie in Excel:

VERGLEICH-Beispiele in VBA

Sie können die VERGLEICH-Funktion auch in VBA verwenden. Tippen Sie:

Application.WorksheetFunction.Match(Suchkriterium,Suchmatrix,Vergleichstyp)

Die Funktionsargumente (Suchkriterium usw.) können Sie entweder direkt in die Funktion eingeben oder Variablen definieren, die stattdessen verwenden.

Angenommen, wir haben die folgenden Werte in unserem Excel-Blatt

Ausführen des folgenden Codes:

Dim Vergleich_Wert As Variant
Suchwert = Application.WorksheetFunction.Match("Test1", Range("A4:A13"), 1)
MsgBox ("Übereinstimmung wurde in Zeile " & Vergleich_Wert & " gefunden")

Gibt folgendes zurück:

Übereinstimmung wurde in Zeile 9 gefunden

da die Zeichenkette „Test1“ das neunte Element im zu durchsuchenden Wertebereich ist.

Wir können auch nach Inhalten in unserem Arbeitsblatt suchen, anstatt Werte direkt in den VBA-Code einzugeben: Die folgende Anweisung sucht in Spalte A nach dem Wert, der in Zelle C3 eingegeben wurde:

Vergleich_Wert = Application.Match(Cells(3, 3).Value, Columns(1), 0)