Start › Excel-Funktionen › INDEX/VERGLEICH
INDEX und VERGLEICH in Excel: beide Funktionen erklärt
Zusammen bilden INDEX und VERGLEICH einen Verweis, der alles kann, was SVERWEIS kann, und mehr: nach links suchen, eingefügte Spalten überstehen und in Zeilen und Spalten gleichzeitig suchen. Er funktioniert in allen Excel-Versionen. Im englischen Excel heißen die Funktionen INDEX und MATCH.
INDEX/VERGLEICH · In dieser Anleitung
So funktionieren INDEX und VERGLEICH
Die Kombination versteht man am besten als zwei getrennte Funktionen mit je einer Aufgabe.
VERGLEICH findet die Position
=VERGLEICH(Suchkriterium; Suchmatrix; [Vergleichstyp])VERGLEICH gibt die Position eines Werts in einer Liste zurück, nicht den Wert selbst. Verwenden Sie 0 als letztes Argument für eine genaue Übereinstimmung.
INDEX gibt den Wert an einer Position zurück
=INDEX(Matrix; Zeile; [Spalte])INDEX gibt zurück, was an einer bestimmten Position eines Bereichs steht. =INDEX(D2:D6; 4) gibt den 4. Wert aus D2:D6 zurück.
Zusammen
=INDEX(Ergebnisbereich; VERGLEICH(Suchkriterium; Suchbereich; 0))VERGLEICH findet heraus, in welcher Zeile der Wert steht, und INDEX holt mit dieser Zeilennummer das Ergebnis aus einer anderen Spalte.
Beispiel Schritt für Schritt
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Produkt | Bestand | Preis |
| 2 | P-101 | Apfel | 120 | 0,35 € |
| 3 | P-102 | Banane | 85 | 0,25 € |
| 4 | P-103 | Kirsche | 300 | 0,10 € |
| 5 | P-104 | Mango | 40 | 1,20 € |
| 6 | P-105 | Birne | 60 | 0,30 € |
=VERGLEICH("Mango"; B2:B6; 0)ergibt 4: Mango ist das 4. Element in B2:B6.=INDEX(D2:D6; 4)ergibt 1,20 €: das 4. Element in D2:D6.- Kombiniert:
=INDEX(D2:D6; VERGLEICH("Mango"; B2:B6; 0))ergibt 1,20 €.
Verwenden Sie zwei gleich große Bereiche, die in derselben Zeile beginnen. Beginnt der Suchbereich in Zeile 2, muss auch der Ergebnisbereich dort beginnen. Sonst zeigt die Position von VERGLEICH auf die falsche Zeile.
INDEX/VERGLEICH üben: selbst ausprobieren
Lesen ist das eine, selbst tippen das andere. Bauen Sie die Formel unten Schritt für Schritt auf: Die Tipps sagen Ihnen, was Sie eingeben müssen, und Sie können auf Zellen klicken oder darüber ziehen, um sie hinzuzufügen, genau wie in Excel.
Aufgabe: Finden Sie in F2 die Abteilung von Diana: =INDEX(B2:B7;VERGLEICH(E2;A2:A7;0))
Einen Wert links davon suchen
Da Suchbereich und Ergebnisbereich getrennt sind, kann die Ergebnisspalte überall liegen. Für den Code der Mango in Spalte A:
=INDEX(A2:A6; VERGLEICH("Mango"; B2:B6; 0))Diese Formel ergibt P-104, was SVERWEIS nicht kann.
Zweidimensionale Suche: Zeile und Spalte
Geben Sie INDEX einen Bereich mit mehreren Spalten und ein zweites VERGLEICH für die Spalte. Diese Formel findet den Wert, wo sich die Zeile „Mango“ und die Spalte „Preis“ kreuzen:
=INDEX(C2:D6; VERGLEICH("Mango"; B2:B6; 0); VERGLEICH("Preis"; C1:D1; 0))Das erste VERGLEICH ergibt Zeile 4 und das zweite Spalte 2: INDEX gibt also 1,20 € zurück. Ersetzen Sie „Preis“ durch „Bestand“, erhalten Sie 40. Ideal für Tabellen wie Umsatz nach Monat und Region.
Warum INDEX/VERGLEICH statt SVERWEIS?
- Er kann nach links suchen. Die Ergebnisspalte muss nicht rechts der Suchspalte stehen.
- Er bricht nicht, wenn Sie Spalten einfügen. Der feste Spaltenindex von SVERWEIS (etwa 3) zeigt nach dem Einfügen auf eine andere Spalte; die Bezüge von INDEX/VERGLEICH wandern mit den Daten mit.
- Er kann in zwei Richtungen suchen mit einer zweidimensionalen Suche.
- Er funktioniert überall, auch in Excel 2019 und älter, wo es XVERWEIS nicht gibt.
In Microsoft 365 oder Excel 2021 und neuer macht XVERWEIS dasselbe mit einer einzigen Funktion und ist leichter zu lesen. INDEX/VERGLEICH steckt aber in unzähligen Arbeitsmappen, es lohnt sich also, ihn zu kennen.
Häufige Fehler
#NV
VERGLEICH hat den Wert nicht gefunden. Prüfen Sie die Schreibweise, überflüssige Leerzeichen und als Text gespeicherte Zahlen. Packen Sie die Formel in WENNFEHLER, um eine Meldung anzuzeigen.
Falsches Ergebnis, ohne Fehler
Wahrscheinlich fehlt die 0 in VERGLEICH. Ohne sie sucht VERGLEICH eine ungefähre Übereinstimmung, die nur bei sortierten Daten funktioniert. Oder die beiden Bereiche beginnen nicht in derselben Zeile.
#BEZUG!
Die Zeilen- oder Spaltennummer ist größer als der Bereich von INDEX, meist weil der INDEX-Bereich kleiner ist als der von VERGLEICH.
Häufige Fragen
Ist INDEX VERGLEICH schneller als SVERWEIS?
Bei sehr großen Blättern kann er etwas schneller sein, weil er nur die beiden benötigten Spalten liest. In den meisten Alltagsmappen merken Sie keinen Unterschied.
Was bedeutet die 0 in VERGLEICH?
Sie verlangt eine genaue Übereinstimmung. Verwenden Sie 1 (oder lassen Sie es weg) für „der größte Wert kleiner oder gleich“ bei aufsteigend sortierten Daten, und -1 für „der kleinste Wert größer oder gleich“ bei absteigend sortierten Daten.
Soll ich INDEX VERGLEICH oder XVERWEIS verwenden?
Verwenden Sie XVERWEIS, wenn alle Microsoft 365, Excel 2021 oder neuer haben. Verwenden Sie INDEX VERGLEICH, wenn die Datei in älteren Versionen funktionieren muss.
Kann INDEX VERGLEICH mehrere Kriterien verwenden?
Ja. In Microsoft 365 können Sie =INDEX(D2:D6; VERGLEICH(1; (B2:B6="Mango")*(C2:C6>20); 0)) schreiben. In älteren Versionen bestätigen Sie sie als Matrixformel mit Strg+Umschalt+Enter.