SVERWEIS, INDEX/VERGLEICH oder XVERWEIS: der Vergleich

Zu einer Artikelnummer die Bezeichnung finden, zu einer Kundennummer den Namen, zu einem Kürzel den Preis: Für solche Suchen gibt es in Excel drei Wege. SVERWEIS kennt fast jeder, INDEX/VERGLEICH ist flexibler, und XVERWEIS vereint die Vorteile beider in einer Funktion.

Die Kurzlösung

Steht die gesuchte Artikelnummer in H6, die Artikelnummern in C7:C16 und die Bezeichnungen in D7:D16:

Excel-VersionFormel
Microsoft 365, Excel 2021 und neuer=XVERWEIS(H6;C7:C16;D7:D16;"nicht gefunden")
alle Versionen=INDEX(D7:D16;VERGLEICH(H6;C7:C16;0))
alle Versionen, Ergebnis rechts der Suchspalte=SVERWEIS(H6;C7:E16;2;FALSCH)
Beispieldatei mit Artikelliste und den drei Suchfunktionen nebeneinander

SVERWEIS

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;FALSCH)

SVERWEIS sucht in der ersten Spalte der Matrix und gibt den Wert aus der Spalte zurück, deren Nummer Sie angeben. =SVERWEIS(H6;C7:E16;2;FALSCH) sucht die Artikelnummer in Spalte C und liefert die Bezeichnung aus der zweiten Spalte des Bereichs, also D.

Zwei Einschränkungen sind wichtig:

  • SVERWEIS kann nur nach rechts suchen. Das Lager in Spalte B, links der Artikelnummer, ist damit nicht erreichbar.
  • Das vierte Argument FALSCH (oder 0) ist fast immer nötig. Ohne es sucht SVERWEIS ungefähr und liefert bei einer nicht vorhandenen Nummer stillschweigend den nächstkleineren Eintrag.

INDEX/VERGLEICH

=INDEX(Ergebnisspalte;VERGLEICH(Suchkriterium;Suchspalte;0))

VERGLEICH ermittelt die Position des Suchwerts in der Suchspalte, INDEX holt den Wert an dieser Position aus der Ergebnisspalte. Da beide Spalten getrennt angegeben werden, ist die Richtung egal. Das Lager links der Artikelnummer liefert:

=INDEX(B7:B16;VERGLEICH(H6;C7:C16;0))

Die 0 am Ende von VERGLEICH steht für die genaue Suche.

XVERWEIS

=XVERWEIS(Suchkriterium;Suchspalte;Ergebnisspalte;[wenn_nicht_gefunden])

XVERWEIS arbeitet wie INDEX/VERGLEICH in einer einzigen Funktion. Es sucht ohne weitere Angabe genau, findet Werte in beide Richtungen und hat ein eigenes Argument für den Fall, dass nichts gefunden wird:

=XVERWEIS(H6;C7:C16;B7:B16;"nicht gefunden")

Geben Sie als Ergebnis mehrere Spalten an, etwa D7:E16, liefert XVERWEIS Bezeichnung und Preis nebeneinander.

Vergleich

SVERWEISINDEX/VERGLEICHXVERWEIS
Suche nach linksneinjaja
genaue Suche als Standardnein, FALSCH nötignein, 0 nötigja
Meldung bei fehlendem Wertnur mit WENNFEHLERnur mit WENNFEHLEReingebaut
robust beim Einfügen von Spaltenneinjaja
Excel-Versionallealle2021 und Microsoft 365

Für neue Dateien in Microsoft 365 empfehlen wir XVERWEIS. Müssen die Dateien auch mit Excel 2019 oder älter funktionieren, ist INDEX/VERGLEICH die sichere Wahl. Bei XVERWEIS zeigen ältere Versionen #NAME?.

Beispiel mit der Beispieldatei

Für die Artikelnummer 4718 liefern alle drei Wege die Bezeichnung „Winkel 40 mm“, INDEX/VERGLEICH und XVERWEIS zusätzlich das Lager A. Für die nicht vorhandene Nummer 9999 zeigen SVERWEIS und INDEX/VERGLEICH #NV, XVERWEIS den Text „nicht gefunden“.

Typische Fehler

  • Spalte eingefügt: Fügen Sie in die Liste eine Spalte ein, passt Excel bei SVERWEIS den Bereich an, nicht aber die feste Spaltennummer. =SVERWEIS(…;2;FALSCH) liefert danach die neue, leere Spalte (Ergebnis 0) statt der Bezeichnung. INDEX/VERGLEICH und XVERWEIS bleiben richtig.
  • Ungefähre Suche aus Versehen: =SVERWEIS(4713,5;B2:D9;2) ohne FALSCH liefert den Artikel zu 4713, obwohl es 4713,5 nicht gibt.
  • #NV trotz vorhandenem Wert: Meist steht die Nummer einmal als Zahl und einmal als Text in der Zelle, oder es hängt ein Leerzeichen daran. Prüfen Sie mit =ISTTEXT(C7) und bereinigen Sie die Daten.
  • Bezüge beim Kopieren: Wird die Formel nach unten kopiert, müssen Such- und Ergebnisbereich fest sein: $C$7:$C$16.

Beispieldatei

Die Datei enthält die Artikelliste und alle Varianten nebeneinander. Ändern Sie die gelbe Zelle, um verschiedene Nummern zu testen. Die Datei enthält keine Makros.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.