Mit INDEX und VERGLEICH holen Sie einen Wert aus einer Tabelle, auch wenn er links vom Suchbegriff steht. Die Kombination aus beiden Funktionen kommt ohne feste Spaltennummer aus und läuft in jeder Excel-Version, auch dort, wo es XVERWEIS noch nicht gibt.
In diesem Beitrag lernen Sie beide Funktionen zuerst einzeln kennen. Danach setzen Sie sie zusammen und holen damit einen Wert, an den SVERWEIS nicht herankommt.
VERGLEICH: an welcher Stelle steht der Wert?
VERGLEICH sucht einen Wert in einer Liste und gibt zurück, an welcher Stelle er steht. Nicht den Wert selbst, sondern die Position als Zahl.
=VERGLEICH(G4;$A$4:$A$13;0)
Die Artikelnummer BB-4001 steht in Zeile 11 des Arbeitsblatts. VERGLEICH liefert trotzdem die 8, denn gezählt wird innerhalb des angegebenen Bereichs. A4 ist die Position 1, A11 ist die Position 8.
Das dritte Argument bestimmt die Art der Suche:
| Wert | Bedeutung |
0 | genaue Übereinstimmung, die Liste darf unsortiert sein |
1 | größter Wert, der kleiner oder gleich dem Suchkriterium ist, aufsteigend sortierte Liste nötig |
-1 | kleinster Wert, der größer oder gleich ist, absteigend sortierte Liste nötig |
Schreiben Sie die 0 immer ausdrücklich hin. Lassen Sie das Argument weg, arbeitet Excel mit 1 und liefert bei unsortierten Listen falsche Positionen, ohne zu warnen.
Kommt der gesuchte Wert mehrfach vor, gibt VERGLEICH mit 0 die Position des ersten Treffers von oben zurück. Weitere Treffer bleiben unberücksichtigt.
INDEX: welcher Wert steht an dieser Stelle?
INDEX macht das Gegenteil. Die Funktion bekommt eine Position und gibt den Wert zurück, der dort steht.
=INDEX($B$4:$B$13;8)
An der achten Stelle der Bezeichnungen steht der Toner HP 26A. Allein ist die Funktion wenig wert, denn die Position 8 müssten Sie von Hand heraussuchen.
Beide zusammen
Jetzt fügen Sie beide Teile zusammen. VERGLEICH ermittelt die Position, INDEX holt den Wert. Statt der festen 8 steht der VERGLEICH-Teil in der Formel.
=INDEX($B$4:$B$13;VERGLEICH(G4;$A$4:$A$13;0))
Lesen Sie die Formel von innen nach außen. Excel berechnet zuerst VERGLEICH(G4;$A$4:$A$13;0) und erhält 8. Daraus wird INDEX($B$4:$B$13;8), und das Ergebnis ist die Bezeichnung.
Tipp zur Fehlersuche: Markieren Sie in der Bearbeitungsleiste nur den VERGLEICH-Teil und drücken Sie F9. Excel zeigt das Zwischenergebnis an. Mit Esc verlassen Sie die Ansicht, ohne die Formel zu verändern.
Nach links suchen
Hier zeigt sich der eigentliche Vorteil. Gesucht ist die Artikelnummer zu einer bekannten Bezeichnung. Die Artikelnummer steht links davon, für SVERWEIS ist diese Aufgabe damit nicht lösbar.
=INDEX($A$4:$A$13;VERGLEICH(G4;$B$4:$B$13;0))
Der Aufbau bleibt gleich, nur die beiden Bereiche tauschen die Rollen. VERGLEICH sucht in den Bezeichnungen, INDEX holt aus den Artikelnummern. Für Excel spielt es keine Rolle, welcher Bereich weiter links liegt.
Der zweite Vorteil: eingefügte Spalten
SVERWEIS arbeitet mit einer festen Spaltennummer. Fügen Sie innerhalb der Matrix eine Spalte ein, zeigt diese Nummer auf die falsche Spalte. Excel meldet nichts, das Ergebnis ist ab sofort falsch.
INDEX arbeitet mit einem Bereich. Verschiebt sich dieser Bereich, passt Excel den Bezug in der Formel mit an. Aus $B$4:$B$13 wird dann $C$4:$C$13, und das Ergebnis stimmt weiterhin.
Bei großen Tabellen, an denen mehrere Personen arbeiten, ist das ein handfester Vorteil.
Werte aus einer Kreuztabelle holen
INDEX kann auch zweidimensional arbeiten. Dann geben Sie eine Zeile und eine Spalte an, und zwei VERGLEICH-Funktionen ermitteln beide Positionen:
=INDEX($B$4:$E$13;VERGLEICH($G$4;$A$4:$A$13;0);VERGLEICH($H$3;$B$3:$E$3;0))Der erste VERGLEICH sucht die Zeile, der zweite die Spaltenüberschrift. Damit holen Sie einen Wert aus einer Kreuztabelle, ohne die Spalten zu zählen. Ändert sich die Reihenfolge der Spalten, bleibt das Ergebnis richtig.
Wenn die Formel nicht rechnet
| Meldung | Ursache | Lösung |
#NV | VERGLEICH findet den Wert nicht | Schreibweise prüfen, Leerzeichen mit GLÄTTEN entfernen, Zahl gegen Text prüfen |
#BEZUG! | Die ermittelte Position liegt außerhalb des INDEX-Bereichs | Den INDEX-Bereich so weit nach unten ziehen, wie die Suchspalte reicht |
| falscher Wert ohne Meldung | Such- und Ergebnisbereich sind gegeneinander verschoben | Beide Bereiche müssen gleich viele Zeilen haben und Zeile für Zeile zusammenpassen |
| falscher Wert bei unsortierter Liste | Das dritte Argument von VERGLEICH fehlt, Excel rechnet mit 1 | 0 ergänzen |
#WERT! | Im dritten Argument von VERGLEICH steht Text | Dort gehört 0, 1 oder -1 hin |
Der dritte Fall bleibt am längsten unentdeckt, weil keine Meldung erscheint. Sucht VERGLEICH in $A$4:$A$13 und holt INDEX aus $B$5:$B$14, sind beide Bereiche um eine Zeile gegeneinander verschoben. Jede Zeile liefert dann den Nachbarwert. Entscheidend ist nicht die Zeilennummer im Blatt, sondern dass die achte Position im einen Bereich zur achten Position im anderen gehört. Am einfachsten stellen Sie das sicher, indem beide Bereiche gleich viele Zeilen umfassen und in derselben Zeile beginnen.
INDEX und VERGLEICH oder XVERWEIS
XVERWEIS kann dasselbe und ist kürzer zu schreiben. Die Kombination hat trotzdem ihre Berechtigung:
- Sie läuft in jeder Excel-Version, auch in 2016 und 2019.
- Sie steckt in unzähligen bestehenden Dateien. Wer sie liest, versteht auch fremde Tabellen.
- Für Kreuztabellen mit zwei Suchrichtungen ist sie nach wie vor der direkte Weg.
Für neue Dateien mit einer aktuellen Excel-Version ist XVERWEIS die einfachere Wahl.
Auf Englisch
In einer englischen Excel-Version heißen die beiden Funktionen INDEX und MATCH. In englischsprachigen Vorlagen steht zwischen den Argumenten meist ein Komma statt eines Semikolons. Das hängt nicht an der Sprache der Funktion, sondern am Listentrennzeichen der Regionseinstellungen.
Übungsdatei zum Herunterladen
Die Preisliste aus den Beispielen steckt in der Übungsmappe zum Thema Verweisfunktionen.
Was in der Übungsdatei steckt
- Bestellungen und Preisliste für die Grundlagen
- Zahlungen für den Abgleich zweier Listen
- Kundenpreise für die Suche mit zwei Kriterien
- Rabatte für die Umsatzstaffel
- Fehlersuche mit dem Klassiker Zahl gegen Text
Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.
Weitere Anleitungen zu Verweisfunktionen
- SVERWEIS Schritt für Schritt: SVERWEIS im Vergleich, mit seinen Grenzen.
- XVERWEIS: die kürzere Schreibweise für aktuelle Excel-Versionen.
- SVERWEIS mit zwei Suchkriterien: INDEX und VERGLEICH mit zwei Bedingungen.
- SVERWEIS funktioniert nicht: Fehlersuche bei Verweisformeln.