sverweis excel

Der SVERWEIS in Excel zählt zu den Formeln, welche in der Praxis sehr häufig und auch für viele Bereiche eingesetzt werden können. In dem folgenden Tipp bekommen Sie eine Erklärung und Anleitung mit Beispiel, wie man den SVERWEIS sinnvoll einsetzen kann.


1. SVERWEIS für eine Matrix-Suche in Tabellen

Die Funktion SVERWEIS ist keine klassische Formel, mit deren Hilfe ein Ergebnis berechnet wird. Der Einsatzzweck von SVERWEIS besteht vielmehr darin, in einer Tabelle nach einem Wert zu suchen und dann in der Spalte, wo dieser Wert gefunden wurde, eine rechts angrenzende Zelle auszulesen und zu übernehmen. Als Beispiel nehmen wir eine Produktliste eines EDV-Dienstleisters. Die Produkte sind in einer Tabelle mit Produktnummer, Artikelbezeichnung, Warengruppe, EK- und VK-Preis organisiert.

Tabelle in Excel für die Übung SVERWEIS Excel

Tabelle in Excel für die Übung SVERWEIS Excel.

Möchten Sie nun für einen Kunden ein Angebot erstellen, können Sie die Angebotstabelle so organisieren, dass Sie nur die Produktnummer eingeben müssen. Über diese Produktnummer sucht der SVERWEIS in der Produktliste und gibt dann als Ergebnis eine rechts davon befindliche Zelle in der gleichen Spalte aus.

Tabelle mit SVERWEIS Excel in der Formel

Tabelle mit SVERWEIS Excel in der Formel

2. Syntax von SVERWEIS in Excel, Erklärung und Anleitung

Der SVERWEIS nutzt folgenden Syntax:

=SVERWEIS(Suchkriterium, Matrix, Spaltenindex, Bereich_Verweis)
Syntax von Excel SVERWEIS

Syntax von Excel SVERWEIS

  • Suchkriterium ist der Wert, nach dem in der Matrix gesucht werden soll. Der Wert kann dabei eine Zahl, Text oder der Inhalt einer anderen Zelle ein, beispielsweise ein Eingabefeld in einem Angebots- oder Rechnungsformular.
  • Matrix ist der Bereich, in welchem nach dem Suchkriterium gesucht werden soll. Der Suchbereich kann dabei im aktuellen Tabellenblatt oder einem anderen Tabellenblatt liegen, beispielsweise =SVERWEIS(G4;A2:E14;2;FALSCH) oder =SVERWEIS(G4;TabelleProdukte!A2:E14;2;FALSCH)
  • Spaltenindex bestimmt, welche Spalte ab der Fundstelle nach rechts als Wert übernommen wird. Gezählt wird inklusive der Spalte mit der Fundstelle. Der Wert „2“ bedeutet demnach, dass die rechts neben dem Suchwert gelegene Zelle ausgelesen wird.
  • Bereich_Verweis ist optional mit WAHR oder FALSCH. Bei FALSCH (oder einem Semikolon) wird der Wert absolut gesucht. Wird der Wert nicht gefunden, gibt die Formel einen Fehler aus. Bei WAHR (oder keine Angabe und auch kein Semikolon) sucht die Formel den nächstkleineren Wert und gibt diesen aus.
Syntax von Excel SVERWEIS für die Übung

Syntax von Excel SVERWEIS für die Übung

Auf unser Beispiel bezogen haben wir eine Angebotstabelle, welche nach Eingabe der Artikelnummer die Bezeichnung des Artikels über die Artikelnummer mit dem SVERWEIS sucht und in die Spalte Bezeichnung schreibt. Auch der Einzelpreis wird auf diesem Wege ermittelt, der Gesamtpreis dann über eine Multiplikation der Anzahl und Einzelpreis ermittelt.

Beispiel von SVERWEIS in einer Tabelle

Beispiel von SVERWEIS in einer Tabelle

Die Formel =SVERWEIS(G4;A2:E14;2;FALSCH) sucht nach dem Wert in G4 (Artikel) in der Matrix A2:E14 (unserer Produkttabelle) und liest bei einem Treffer die 2. Spalte (Artikelbezeichnung) aus.

Für den Einzelpreis verfahren wir in der Angebotstabelle analog mit der Formel =SVERWEIS(G4;A2:E14;5;FALSCH), lesen aber die 5. Spalte aus, in welcher sich der Verkaufspreis findet.

SVERWEIS Erklärung für das Beispiel

SVERWEIS Erklärung für das Beispiel.

3. Tipps zum Umfang mit dem SVERWEIS

Mit den folgenden Hinweisen können Sie die Funktion SVERWEIS, welche auch in dem nachfolgenden YouTube-Video nochmals genauer erklärt wird, noch optimaler einsetzen:

https://www.youtube.com/watch?v=GeleM8FbN70

3.1. Aufbau von Tabellen für optimale Nutzung vom SVERWEIS

Der SVERWEIS kann immer nur die Werte rechts vom Suchergebnis ausgeben. Der richtige Aufbau einer Tabelle ist daher essenziell, um die Funktion in der Praxis sinnvoll zu nutzen. Setzen Sie den Suchwert daher im Idealfall immer in die linke Spalte einer Tabelle. Im Internet finden Sie viele Übungen, welche auf diesem Prinzip basieren.

Richtiger Aufbau einer Tabelle für SVERWEIS in Excel

Richtiger Aufbau einer Tabelle für SVERWEIS in Excel

3.2. Absolute Zellangaben, um Formeln zu kopieren

Excel bietet die Möglichkeit, Zellen durch Ziehen nach unten oder nach rechts bzw. links zu ziehen und passt die Formeln dann entsprechend an. Für unsere Angebotstabelle würde sich beim Ziehen nach unten die abgefragte Spalte „G“ mit der Artikelnummer ändern, also von G4 auf G5. Da wir aber die Matrix auch mit relativen Zellangaben versehen haben, würde Excel die Formel von

=SVERWEIS(G4;A2:E14;2;FALSCH) auf =SVERWEIS(G5;A3:E15;2;FALSCH)

ändern und damit auch den Matrix-Bereich. Um das zu verhindern, müssen Sie die Zellbezüge absolut angeben. Dies können Sie erreichen, indem Sie vor die Spalten- und Zeilenangaben jeweils ein „Dollar-Zeichen“ setzen.

absolute Zellbzüge in einer Formel

Absolute Zellbezüge in einer Formel sind praktisch, wenn Sie die Formel durch Ziehen kopieren.

Tastenkombination: Mit der Excel-Tastenkombination F4 setzt Excel automatisch absolute Zellbezüge.

Mit dem absoluten Zellbezug können Sie die Formel nun ziehen, ohne dass sich die Matrix der Formel ändert.

absoluter Zellbezug zum Kopieren einer Formel

Ein absoluter Zellbezug erleichtert das Kopieren einer Formel.

3.3. Benannte Bereiche statt kryptische Zellangaben

Statt der absoluten Bezeichnung von Zellen können Sie auch die Matrix im Vorfeld mit einem Namen definieren und diesen Bereichsnamen verwenden.

Benannter Bereich in Excel

Einen benannten Bereich definieren Sie in Excel über das Kontextmenü "Namen definieren".

Name für den Bereich definieren

Name für den Bereich definieren.

Mit dem Bereichsnamen als Definition in der Formel können Sie wesentlich einfacher die Formeln erstellen und verwalten.

definierter Name in der Formel

Ein definierter Name in der Formel erleichtert die Formelerstellung.

3.4 Fehlermeldung #NV

Wenn die Formel #NV ausgibt, wurde das Suchkriterium nicht gefunden. In unserem Beispiel verwenden wir die Eingabe in der Spalte „Artikel“ als Suchwert. Wurde keine Artikelnummer eingegeben, kann auch kein Wert gefunden werden.

Fehlermeldung #nv in excel

Die Fehlermeldung #nv in Excel besagt, dass kein Wert gefunden werden konnte.

Um die Ausgabe von #nv zu unterbinden, prüfen Sie den Index und Abfragewert. Sie können aber auch die Formel anpassen und damit #nv unterdrücken, indem Sie die Formel für SVERWEIS wie folgt erweitern:

=WENNFEHLER(SVERWEIS(G6;$A$2:$E$14;2;FALSCH);"")
#nv in Excel durch eine Formel unterbinden

#nv können Sie in Excel durch eine Formel unterbinden.

Die Formel WENNFEHLER schreibt nichts in die Zelle (""), wenn der SVERWEIS #NV ausgeben möchte. Statt keiner Angabe mithilfe doppelter Hochzeichen, können Sie auch eine Zahl oder Text ausgeben lassen.

4. XVERWEIS als Alternative

Microsoft bietet mit der neuen XVERWEIS-Funktion eine verbesserte Alternative zum SVERWEIS, welche nicht diese Limitierungen besitzt. Der neue Befehl steht aber nur in Office 365 / Microsoft 365 zur Verfügung und ist daher mit älteren Excel-Versionen nicht kompatibel. Daher ergibt es derzeit wenig Sinn, diese Formel zu lernen oder in VBA zu nutzen.

5. INDEX VERGLEICH als großer Bruder von SVERWEIS

SVERWEIS besitzt mehrere Limitierungen. Die Formel kann nur in einem Matrix-Bereich suchen und auch nur einen Wert in einer rechts liegenden Spalte ausgeben.

Für komplexere Aufgaben bietet es sich daher an, die Matrix mit Index (Vergleich) zu durchsuchen. Index-Vergleich durchsucht auch von oben nach unten und ist nicht nur auf links nach rechts limitiert. In Kombination mit VERGLEICH ist dieses Gespann in der Lage, Werte einzelner Zellen überall in einem definierten Bereich zu ermitteln.

40 Bewertungen

1 Stern2 Sterne3 Sterne4 Sterne5 Sterne Ø 3,73
Was kann die SVERWEIS-Funktion in Excel? – Eine Erklärung mit Anleitung und Beispielen
Loading...