=VERGLEICH(E2;A2:A6;0) liefert die Position des Werts aus E2 in A2:A6: 1 für die erste Zelle, 2 für die zweite und so weiter. Die 0 verlangt eine genaue Übereinstimmung. VERGLEICH (englisch MATCH) liefert eine Zahl, nicht den Wert selbst, und wird deshalb meist mit INDEX kombiniert. Die Tabelle zeigt die englische Schreibweise, =MATCH(E2,A2:A6,0); du kannst die Formeln dort aber auch deutsch eingeben, mit Semikolons.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=VERGLEICH(E2;A2:A6;0)Bread ist die vierte Zelle von A2:A6, also zeigt F2 4. Die Zählung beginnt bei der ersten Zelle des Bereichs, nicht bei Zeile 1 des Blatts: Ändere den Bereich in A1:A6, und die Antwort wird 5. Tippe Kiwi in E2, und VERGLEICH liefert #NV (englisch #N/A, so zeigt es die Tabelle).
Syntax von VERGLEICH und Vergleichstypen
=MATCH(lookup_value, lookup_array, [match_type])
| match_type (Vergleichstyp) | Findet | Die Liste muss |
|---|---|---|
0 | Den ersten Wert, der genau gleich lookup_value (Suchkriterium) ist. Bei Text sind Platzhalter erlaubt. | in beliebiger Reihenfolge sein |
1 oder weggelassen | Den größten Wert, der kleiner oder gleich lookup_value ist. | aufsteigend sortiert sein |
-1 | Den kleinsten Wert, der größer oder gleich lookup_value ist. | absteigend sortiert sein |
Der Standard ist 1, nicht 0. Ein VERGLEICH ohne drittes Argument auf einer unsortierten Namensliste beschwert sich nicht: Er liefert eine Position, die zu einer anderen Zeile gehören kann. Schreib die 0 jedes Mal, wenn du Text, Codes oder IDs suchst.
Ungefähre Übereinstimmung: In welche Stufe fällt ein Wert?
Mit Vergleichstyp 1 beantwortet VERGLEICH die Frage "welche Stufe": die Position der letzten Schwelle, die der Wert erreicht hat. Hier hat eine Punktzahl von 75 die 0, die 60 und die 70 überschritten, also liegt sie in Stufe 3, Note C.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
=VERGLEICH(D2;$A$2:$A$6;1)59 liegt noch unter 60, also ist es Stufe 1 (F). 90 trifft die letzte Schwelle genau, und 100 liegt darüber, also landen beide in Stufe 5 (A). Die Schwellen müssen aufsteigend sortiert bleiben: Nur so hält VERGLEICH an der richtigen Stelle an.
Vergleichstyp -1 ist das Spiegelbild für eine Liste, die vom größten zum kleinsten Wert sortiert ist. Er liefert die Position des kleinsten Werts, der noch größer oder gleich dem Suchkriterium ist: Mit den Größen {50;25;12;5} in einer Spalte liefert =VERGLEICH(18;D2:D5;-1) die 2, die Größe 25, die kleinste, in die 18 passt.
Platzhalter und Groß- und Kleinschreibung
VERGLEICH ignoriert die Groß- und Kleinschreibung, und mit Vergleichstyp 0 akzeptiert er die Platzhalter * (beliebige Zeichen) und ? (genau ein Zeichen). Für einen Vergleich mit Groß- und Kleinschreibung vergleichst du die Liste mit IDENTISCH (englisch EXACT) und suchst das erste WAHR.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
=VERGLEICH("C*";A2:A6;0)"C*" passt auf Carrot, Position 3. "milk" findet Milk an Position 5, weil die Schreibweise ignoriert wird. Die Version mit IDENTISCH vergleicht jeden Namen Zeichen für Zeichen mit "milk", findet kein WAHR und liefert #NV. Ändere A6 in milk, und sie liefert 5. In Excel 2019 und älter bestätigst du die Formel mit IDENTISCH mit Strg+Umschalt+Eingabe (Cmd+Umschalt+Return auf dem Mac); in Excel 2021 und Microsoft 365 reicht die Eingabetaste. Eine Tilde macht einen Platzhalter zum normalen Zeichen: "~*" sucht ein echtes Sternchen.
Prüfen, ob ein Wert in einer Liste steht
VERGLEICH liefert eine Zahl, wenn er den Wert findet, und #NV, wenn nicht. ISTZAHL (englisch ISNUMBER) macht daraus WAHR oder FALSCH, ein schneller Test für "steht das in der Liste".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
=WENN(ISTZAHL(VERGLEICH(A2;$D$2:$D$5;0));"Yes";"No")Pear, Milk und Bread stehen in D2:D5, also zeigen sie Yes. Kiwi und Plum zeigen No. In deutschem Excel lautet die Formel in B2 =WENN(ISTZAHL(VERGLEICH(A2;$D$2:$D$5;0));"Yes";"No"). ZÄHLENWENN($D$2:$D$5;A2)>0 liefert dieselbe Antwort; VERGLEICH hält beim ersten Treffer an, ZÄHLENWENN (englisch COUNTIF) zählt jeden.
VERGLEICH, XVERGLEICH und INDEX
VERGLEICH ist die eine Hälfte der klassischen Suche =INDEX(C2:C6;VERGLEICH(E2;A2:A6;0)), die auf der Seite INDEX und VERGLEICH erklärt wird. Excel 2021 und Microsoft 365 bringen XVERGLEICH (englisch XMATCH) mit, das standardmäßig genau sucht, vom letzten Element aus suchen kann und "nächstgrößer" ohne absteigende Sortierung beherrscht. VERGLEICH funktioniert weiterhin überall, auch in Dateien, die sich in Excel 2019 öffnen lassen müssen.
Übung: Position eines Mitarbeiters
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
Du bist dran: Gib in F2 die Position des Namens aus E2 in der Liste in Spalte A zurück.
Häufig gestellte Fragen
Was liefert VERGLEICH in Excel?
Eine Position, keinen Wert: =VERGLEICH("Bread";A2:A6;0) liefert 4, wenn Bread die vierte Zelle von A2:A6 ist. Kombiniere es mit INDEX, um den Wert an dieser Position aus einer anderen Spalte zu holen.
Was ist der Unterschied zwischen Vergleichstyp 0, 1 und -1?
0 findet eine genaue Übereinstimmung in beliebiger Reihenfolge. 1 (der Standard) findet den größten Wert, der kleiner oder gleich dem Suchkriterium ist, und braucht eine aufsteigend sortierte Liste. -1 findet den kleinsten Wert, der größer oder gleich ist, und braucht eine absteigend sortierte Liste.
Unterscheidet VERGLEICH Groß- und Kleinschreibung?
Nein. =VERGLEICH("milk";A2:A6;0) findet Milk. Für einen Vergleich mit Groß- und Kleinschreibung nimmst du =VERGLEICH(WAHR;IDENTISCH(A2:A6;"milk");0), das in Excel 2019 und älter Strg+Umschalt+Eingabe braucht (Cmd+Umschalt+Return auf dem Mac).
Wie prüfe ich mit VERGLEICH, ob ein Wert in einer Liste steht?
VERGLEICH liefert #NV, wenn der Wert fehlt, also prüfst du, ob eine Zahl herauskam: =ISTZAHL(VERGLEICH(A2;$D$2:$D$5;0)) ergibt WAHR oder FALSCH. Umschließe das mit WENN für deinen eigenen Text.
