SVERWEIS-Funktion in Excel erklärt
Der SVERWEIS macht Ihnen die Arbeit in Excel erheblich einfacher. Statt große Datensammlungen selbst nach einem Wert zu durchsuchen, übernimmt das die SVERWEIS-Funktion und gibt ihnen das Ergebnis sogar aufbereitet in einer anderen Zelle wieder. Damit auch Sie von der Nützlichkeit der Excel-Funktion profitieren können, erklären wir Ihnen den SVERWEIS Schritt für Schritt an einem Beispiel und geben Ihnen praktische Ideen, wobei man die Funktion einsetzen kann.
Domain Bundle Aktion
Nur für kurze Zeit: So erhalten Sie zwei Gratis-Domains!
Kaufen Sie eine .de-Domain und erhalten eine .com und .info Domain gratis dazu.
Was ist ein SVERWEIS?
Der SVERWEIS ist eine der nützlichsten Funktionen in Excel und gehört zu den Nachschlage- und Verweisfunktionen. Nehmen wir an, Sie haben eine Tabelle, bestehend aus mehreren Spalten, in denen Sie Daten erfasst haben. Nun benötigen Sie zu einem bestimmten Suchkriterium den passenden Wert. Beispielsweise könnte dies ein Mitgliedsverzeichnis sein: Sie wissen den Namen der Person und möchten hierzu die passende Telefonnummer erfahren. Mit der SVERWEIS-Funktion brauchen Sie nicht das komplette Verzeichnis nach dem Namen zu durchsuchen – dies erledigt die Funktion für Sie und liefert die entsprechende Telefonnummer automatisch.
Der Name der Funktion setzt sich aus dem „S“ für senkrecht und dem Verweis zusammen. Das gibt den praktischen Umfang der Funktion allerdings nur bedingt wieder. Dennoch lassen sich aus der Bezeichnung zwei wichtige Umstände ablesen: Zum einen gibt das „S“ die Richtung für die Suche an. Die Funktion beginnt in der obersten Zeile und geht von da aus senkrecht nach unten vor. Analog zum SVERWEIS gibt es auch den WVERWEIS, der waagerecht Spalten durchsucht. Die Verweiskomponente kommt durch den Referenzcharakter zustande: Die Funktion gibt nur den Wert einer anderen Zelle wieder, verweist also auf diese. Somit ist SVERWEIS auch mit der weniger umfangreichen VERWEIS-Funktion verwandt.
Excel mit Microsoft 365 und IONOS!
Organisieren Sie Ihre Daten in Excel und erstellen Sie im Handumdrehen Tabellen. In allen Office-Paketen enthalten!
SVERWEIS: Erklärung der Syntax
Damit Sie SVERWEIS richtig einsetzen können, müssen Sie wissen, wie Sie die Funktion korrekt einzugeben haben. Jede Funktion in Excel hat eine bestimmte Syntax, von der man nicht abweichen darf, ansonsten liefert der Befehl entweder nicht das richtige Ergebnis oder gibt sogar eine Fehlermeldung aus. Für SVERWEIS gilt folgende Syntax:
=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis)
Auch in der Tabellenkalkulation von Google gibt es die Funktion. Der VLOOKUP in Google Sheets funktioniert ähnlich wie die Variante bei Excel.
Die Parameter genauer erklärt:
- Suchkriterium: Dieser Parameter enthält den Wert oder die Zeichenfolge, nach der Sie suchen. Sie können das Kriterium entweder direkt in die Formel eintragen, wobei Sie zu suchende Wörter in Anführungszeichen setzen oder eine Zelle angeben, die den Inhalt enthält.
- Matrix: An dieser Stelle geben Sie den Bereich an, in dem die Daten liegen. In Excel-Funktionen wird als Bis-Zeichen immer ein Doppelpunkt eingesetzt.
- Spaltenindex: Der Spaltenindex benennt die Stelle der Spalte des Rückgabewerts innerhalb der festgelegten Matrix. Demnach muss Spalte D nicht automatisch den Index 4 haben. Wenn Sie den Datenbereich erst ab Spalte B anfangen lassen, würden Sie 3 als Spaltenindex eintragen.
- Übereinstimmung: Soll das Ergebnis exakt oder nur ungefähr übereinstimmen? Mit 0 oder FALSCH wird nach genau dem angegebenen Wert gesucht. 1 oder WAHR lässt auch andere Eingaben zu und liefert dann den nächstkleineren Wert. Die Eingabe dieses Parameters ist optional: Wenn Sie die Stelle frei lassen, nimmt die Funktion 1 als Standard an.
In der Praxis würde also eine SVERWEIS-Funktion so aussehen:
=SVERWEIS("Schraube";B1:D50;2;FALSCH)
Oder:
=SVERWEIS(F2;B1:D50;2;0)
Wenn Sie eine Funktion per Hand in Excel eingeben wollen, gehen Sie dafür in die Bearbeitungsleiste über dem Tabellenblatt und beginnen mit einem Gleichheitszeichen. Nur so weiß Excel, dass eine Formel folgt und nicht ein fester Wert.
Wenn Sie die Funktion nicht per Hand in die Bearbeitungsleiste eintragen möchten, können Sie auch das hilfreiche Werkzeug „Funktion einfügen“ verwenden. Hier wählen Sie die Funktion aus einer Liste aus oder suchen nach ihr und erhalten dann eine Maske, in der Sie die Parameter eintragen können. Das Werkzeug erstellt daraufhin die korrekte Syntax selbst.
Das Suchkriterium muss immer in der am weitesten links stehenden Spalte sein. Die Suche wird dann in den Spalten rechts davon durchgeführt. Bauen Sie also Ihre Tabelle so auf, dass keine relevanten Spalten links von der Tabelle auftauchen.
Innerhalb der Funktion können Sie auch mit Platzhaltern arbeiten: Mit einem Asterisken (*) ersetzen Sie komplette Zeichenfolgen. Das Fragezeichen (?) hingegen steht immer für genau ein Zeichen. Diese Wildcards funktionieren im Übrigen auch in den meisten anderen Excel-Funktionen.
Folgende Fehlermeldungen können bei der Verwendung des SVERWEIS in Excel auftreten:
- #BEZUG!: Beim Spaltenindex wurde eine Spalte angegeben, die sich außerhalb des Suchbereichs befindet, z. B. Bei einem Suchbereich mit drei Spalten, haben Sie im Spaltenindex die Zahl 4 eingetragen.
- #WERT!: Da der SVERWEIS immer nur nach rechts schaut, darf im Spaltenindex weder 0 noch eine negative Zahl eingetragen werden - es ist nicht möglich links neben der ersten Spalte zu suchen.
- #NAME?: Dieser Fehler tritt in Excel auf, wenn eine Formel falsch geschrieben wurde, im Fall des SVERWEIS zum Beispiel so: "=seerweis"
Wie die Excel-Funktion SVERWEIS funktioniert, finden Sie zusätzlich zu diesem Artikel auch schnell und einfach in diesem Video-Tutorial erklärt:
SVERWEIS an einem Beispiel erklärt
Wie funktioniert der SVERWEIS genau? Die Funktion spielt ihre Nützlichkeit dann aus, wenn es darum geht, große Datenansammlungen zu durchsuchen und ein bestimmtes Ergebnis zu liefern. Nehmen wir also als Beispiel an, dass wir eine Musiksammlung haben und dafür ein Verzeichnis in Excel anlegen. Jedes Album erhält einen Eintrag, wobei diese nicht nach Namen sortiert sind, sondern einfach chronologisch nach der Aufnahme in die Sammlung.
Nun möchten wir herausfinden, welches Album sich hinter der Inventarnummer 10 befindet. Dafür können wir die SVERWEIS-Funktion verwenden:
=SVERWEIS(10;A2:D17;3;0)
Nun möchte man aber sicherlich nicht nur einmal diese Suche durchführen. Deshalb bietet es sich an, SVERWEIS in eine Art von Formular einzubinden, das uns außerdem noch Interpreten und Medium liefert. Dafür geben wir das das Suchkriterium – in unserem Fall die Inventarnummer – nicht direkt in die Funktion ein, sondern machen in dieser nur eine Referenz zu einer weiteren Zelle:
=SVERWEIS(G2;A2:D17;3;0)
Nun ist das Vorgehen aber in den meisten Fällen genau umgekehrt: Man weiß, nach welchem Album man sucht, und hätte dazu gern die Inventarnummer oder den Interpreten. Man möchte also mit dem SVERWEIS nach links suchen. Eine Möglichkeit: Sie bauen Ihre Tabelle jeweils um und verschieben die Spalten untereinander. So können Sie zu jedem Suchkriterium ein eigenes Tabellenblatt anlegen.
Stattdessen lässt sich der SVERWEIS aber auch mithilfe einer weiteren Funktion in die gewünschte Form biegen: WAHL. Damit können wir SVERWEIS vorspielen, dass Spalten sich an anderer Stelle befinden, als sie wirklich sind. Man verändert damit also die eigentliche Matrix. In der WAHL-Funktion geben Sie zum einen bestimmte Bereiche an und zum anderen die Reihenfolge, in der diese interpretiert werden sollen:
=WAHL(Index;Wert1;Wert2;…)
Für die Werte setzen wir die Spalten unserer Matrix ein und legen dann im Index eine umgekehrte Reihenfolge fest, in der die Funktion die Spalten interpretiert. Die Angabe des Index geschieht übrigens in geschweiften Klammern und die einzelnen Ziffern werden mit Punkten voneinander getrennt. WAHL ersetzt also in unserem Beispiel den Matrix-Parameter:
=SVERWEIS(F2;WAHL({2.1};A2:A17;C2:C17);2;0)
So gibt uns die Funktion zu einem Albumtitel die passende Inventarnummer. Es ist auch möglich, die Werte in WAHL zu erweitern und so den kompletten Datenbereich aufzunehmen. Das ist aber zumindest in unserem Beispiel gar nicht nötig: Über die Inventarnummer können wir die anderen Werte wieder in einfachen SVERWEIS-Funktionen ermitteln.
Diese Formel funktioniert allerdings nur dann, wenn Sie als Suchkriterium ein Album eingeben; für Interpreten oder Inventarnummern müsste die Formel wieder angepasst werden. Mit ein wenig zusätzlichem Aufwand können Sie sich aber auch ein Drop-down-Menü bauen, in dem Sie das Suchkriterium auswählen. Nutzen Sie dann die WENN-Funktion, um abhängig von der Auswahl die Matrix in der SVERWEIS-Funktion anzupassen:
=WENN(E2="Interpret";SVERWEIS(F2;WAHL({2.1};A2:A17;B2:B17);2;0);WENN(E2="Album";SVERWEIS(F2;WAHL({2.1};A2:A17;C2:C17);2;0)))
Bisher haben wir immer mit einer genauen Übereinstimmung gearbeitet, den Parameter Bereich_Verweis also immer auf FALSCH gesetzt, und das mit gutem Grund: Das Beispiel arbeitet hauptsächlich mit Text. Die ungenaue Übereinstimmung funktioniert aber in erster Linie mit Werten in einer sortierten Liste. Ist der Wert selbst nicht vertreten, verwendet die Funktion den nächstkleineren. Eine solche sortierte Liste mit Werten haben wir in Spalte A. Sollte man also mit einem einfachen SVERWEIS nach den Angaben zu einer bestimmten Inventarnummer suchen, die nicht vergeben ist, greift die Funktion auf die nächstkleinere zurück – in unserem Fall die Nummer 16.
=SVERWEIS(17;A2:D17;3;1)
SVERWEIS ist auch insofern etwas beschränkt, als dass es nur ein Ergebnis – nämlich das erste – liefert. Würden wir in unserem Beispiel nicht nach einem bestimmten Album, sondern nach dem Interpreten suchen und dabei The Beatles eingeben, würden wir nur ein Ergebnis erhalten (Help!). Mit SVERWEIS lässt sich das Problem leider nicht lösen. Dafür müssen wir eine sehr komplexe Formel anwenden:
=INDEX($C$2:$C$17;KGRÖSSTE(($B$2:$B$17=$F$2)*(ZEILE($B$2:$B$17)-1);ZÄHLENWENN($B$2:$B$17;$F$2)+1-ZEILE(C1)))
Mehr Informationen zu der Formel finden Sie z. B. auf der Excel-Seite von Martin Weiß. Achten Sie darauf, dass es sich bei der Formel um eine sogenannte Array-Formel handelt. Eine solche aktivieren Sie nicht einfach mit der Eingabetaste, sondern durch die Kombination von [Strg] + [Shift] + [Enter]. Dies erzeugt auch die für Array-Formeln typischen geschweiften Klammern um die Eingabe.
Das Verwenden von SVERWEIS ist immer dann sinnvoll, wenn man mit großen Mengen an Daten arbeitet. Beliebte Einsatzgebiete sind deshalb z. B. Preislisten oder andere Verzeichnisse. Dabei ist die Einrichtung einer Suchmaske nur eine Möglichkeit von vielen: Immer dann, wenn Sie mit abhängigen Werten aus einer Tabelle hantieren, kann die SVERWEIS-Funktion Ihnen die Arbeit erleichtern.
HiDrive Cloud Speicher von IONOS!
Ihr sicherer Online-Speicher mit Serverstandort in Deutschland: zentral speichern, teilen und bearbeiten!