=INDEX(C2:C6;VERGLEICH(F2;A2:A6;0)) findet die Zeile, in der F2 in A2:A6 vorkommt, und liefert den Wert aus derselben Zeile von C2:C6. VERGLEICH (englisch MATCH) findet die Position, INDEX holt den Wert an dieser Position. Das funktioniert in jeder Excel-Version und kann nach links suchen. Die Tabelle zeigt die englische Schreibweise, =INDEX(C2:C6,MATCH(F2,A2:A6,0)); du kannst die Formeln dort aber auch deutsch eingeben, mit Semikolons: =INDEX(C2:C6;VERGLEICH(F2;A2:A6;0)).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDEX(C2:C6;VERGLEICH(F2;A2:A6;0))Ändere F2 in Milk, und G2 liefert $1.10. Ändere C2:C6 in B2:B6, und die Formel liefert stattdessen die Kategorie.
Wie INDEX und VERGLEICH zusammenarbeiten
Die Formel besteht aus zwei Schritten in einer Zelle. Hier stehen sie in getrennten Zellen, damit du siehst, was jeder Teil liefert.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDEX(C2:C6;G2)VERGLEICH(G1;A2:A6;0) liefert 4, weil Bread das vierte Element von A2:A6 ist. INDEX(C2:C6;4) liefert das vierte Element von C2:C6, $2.40. Setz den VERGLEICH anstelle von G2 in den INDEX ein, und du hast die Formel für eine Zelle. Zwei Regeln sorgen dafür, dass es funktioniert:
- Beide Bereiche müssen in derselben Zeile beginnen und gleich hoch sein.
VERGLEICH(...;A2:A6;0)zählt ab Zeile 2, also muss INDEXC2:C6lesen, nichtC1:C6(das würde die Zeile darüber liefern). - Beende VERGLEICH mit 0. Ohne die 0 macht VERGLEICH eine ungefähre Übereinstimmung, die annimmt, dass Spalte A sortiert ist, und in einer Liste von Namen kann er die Position der falschen Zeile liefern. Die Seite VERGLEICH behandelt seine drei Vergleichstypen.
Steht der Wert nicht in der Liste, liefert VERGLEICH #NV (englisch #N/A) und damit auch die ganze Formel. =WENNNV(INDEX(C2:C6;VERGLEICH(F2;A2:A6;0));"Nicht gefunden") zeigt stattdessen Text.
Suche nach links
SVERWEIS liefert Spalten rechts von der durchsuchten Spalte. INDEX und VERGLEICH ist die Reihenfolge egal: Spalte D durchsuchen, Spalte A liefern.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDEX(A2:A6;VERGLEICH(F2;D2:D6;0))P-310 liefert Bread. Tippe P-205 in F2 für Carrot. Mit SVERWEIS müsstest du die Spalte Code erst an den Anfang der Tabelle verschieben.
Zweidimensionale Suche: INDEX mit zwei VERGLEICH
INDEX nimmt eine Zeilennummer und eine Spaltennummer. Gib ihm eine ganze Tabelle, und lass einen VERGLEICH die Zeile und einen anderen die Spalte finden.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
=INDEX(B2:D5;VERGLEICH(B6;A2:A5;0);VERGLEICH(B7;B1:D1;0))East ist Zeile 3 von A2:A5 und Mar ist Spalte 3 von B1:D1, also liefert INDEX Zeile 3, Spalte 3 von B2:D5: 5,600. Der VERGLEICH für die Zeile sucht in der ersten Spalte nach unten, der VERGLEICH für die Spalte sucht in der Überschriftenzeile nach rechts, und beide Bereiche sind an der Tabelle B2:D5 ausgerichtet.
Warum INDEX und VERGLEICH besser ist als SVERWEIS
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
In deutschem Excel: =SVERWEIS(F2;A2:D6;3;FALSCH) und =INDEX(C2:C6;VERGLEICH(F2;A2:A6;0)). Beide liefern den Preis. Der Unterschied zeigt sich, wenn sich das Blatt ändert:
- Eine Spalte einfügen. Füg zwischen Category und Price eine Spalte ein, und SVERWEIS fragt weiter nach Spalte 3, die jetzt die neue, leere Spalte ist. In der Version mit INDEX passt Excel
C2:C6zuD2:D6an, und sie funktioniert weiter. - Nach links suchen. Oben gezeigt: SVERWEIS kann es nicht, INDEX und VERGLEICH können es.
In Excel 2021 und Microsoft 365 erledigt XVERWEIS beides in einer Funktion mit einfacheren Argumenten. INDEX und VERGLEICH bleiben die Wahl für Dateien, die sich in Excel 2019 oder älter öffnen lassen müssen, und der INDEX-Teil ist auch für sich allein nützlich. Für Suchen nach zwei Bedingungen gleichzeitig siehe Suche mit mehreren Kriterien.
Übung: nach links suchen
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
Du bist dran: Die Produktnamen stehen in der letzten Spalte. Gib in G2 mit INDEX und VERGLEICH den Preis des Produkts in F2 zurück.
Übung: eine zweidimensionale Suche
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
Du bist dran: Gib in B9 das Ergebnis des Schülers in B7 für das Fach in B8 zurück.
Häufig gestellte Fragen
Wie funktioniert INDEX mit VERGLEICH?
VERGLEICH findet die Position eines Werts in einer Spalte, und INDEX liefert den Wert an dieser Position in einer anderen Spalte. In =INDEX(C2:C6;VERGLEICH("Pear";A2:A6;0)) liefert VERGLEICH 2, weil Pear das zweite Element von A2:A6 ist, und INDEX liefert das zweite Element von C2:C6.
Warum INDEX und VERGLEICH statt SVERWEIS?
Die Kombination kann eine Spalte links von der durchsuchten Spalte liefern, und sie geht nicht kaputt, wenn innerhalb der Tabelle eine Spalte eingefügt wird (es gibt keine Spaltennummer, die veralten kann). In Excel 2021 und Microsoft 365 bietet XVERWEIS dieselben Vorteile in einer einzigen Funktion.
Was bedeutet die 0 in VERGLEICH?
Sie verlangt eine genaue Übereinstimmung. Ohne sie verwendet VERGLEICH den Vergleichstyp 1, eine ungefähre Übereinstimmung, die eine aufsteigend sortierte Spalte erwartet, und in einer unsortierten Liste kann sie die Position der falschen Zeile liefern.
Wie mache ich eine zweidimensionale Suche mit INDEX und VERGLEICH?
Gib INDEX eine ganze Tabelle und zwei VERGLEICH, einen für die Zeile und einen für die Spalte: =INDEX(B2:D5;VERGLEICH("South";A2:A5;0);VERGLEICH("Feb";B1:D1;0)) liefert den Wert, wo sich die Zeile South und die Spalte Feb kreuzen.
