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.
Domains günstig kaufen
Domains so einzigartig wie Ihre Ideen. Worauf warten Sie?
Registrieren Sie jetzt Ihre Wunsch-Domain mit IONOS!
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)
Tipp
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)
Hinweis
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.
Hinweis
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:
Mit dem weiteren Klick laden Sie das Video von YouTube. In diesem Fall kann YouTube Cookies setzen, auf welche wir keinen Einfluss haben.
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:
Die Zelle, in der wir die Funktion eingetragen haben, sollte nun Parallel Lines ausgeben.
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:
Die gleiche Funktion setzen wir in zwei weitere Zellen, ändern dabei aber den Spaltenindex jeweils auf 2 und 4.
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 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.
Fazit
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!
Mit einem deutschlandbasierten Rechenzentrum, der Cloud-Speicher für Ihre Daten: sicher und flexibel für alle Geräte!
Excel-Makros: So optimieren Sie Ihren Arbeitsprozess mit der beliebten Tabellenkalkulation
Excel, die Tabellenkalkulation aus dem Hause Microsoft, zählt seit Jahren zu den beliebtesten Lösungen, um der stetig wachsenden Flut an Daten zu begegnen. Mit einfachen Mitteln sind gut strukturierte Tabellen, aussagekräftige Diagramme oder komplette Haushaltspläne erstellt. Aus dem Büroalltag vieler Unternehmen ist die Office-Anwendung kaum wegzudenken. Je komplexer jedoch die Projekte sind,...
Excel-Makros: So optimieren Sie Ihren Arbeitsprozess mit der beliebten TabellenkalkulationZÄHLENWENN-Funktion in Excel
Was ist eigentlich ZÄHLENWENN? Mit Excel stellt Microsoft Ihnen ein hilfreiches Tabellenkalkulationsprogramm zur Verfügung. Die Anwendung vereint zahlreiche Funktionen, von denen aber viele Nutzer gar nichts wissen. Umso sinnvoller ist es, sich zu informieren und das Programm nicht nur zu benutzen, um per Hand Tabellen zu erstellen. Die ZÄHLENWENN-Funktion hilft Ihnen z. B. beim Erstellen von...
ZÄHLENWENN-Funktion in ExcelWenn-Dann in Excel: So funktioniert die WENN-Funktion
In Excel ist die Wenn-Dann-Funktion eine der hilfreichsten Formeln. In vielen Situationen können Sie hiermit einen logischen Vergleich starten: Wenn A wahr ist, dann gilt B und sonst C. Um das nützliche Werkzeug der Wenn-Dann-Abfrage aber auch gewinnbringend einsetzen zu können, muss man verstehen, wie es funktioniert und wie man damit umgeht. Welche Syntax hat die WENN-Funktion eigentlich und wie...
Wenn-Dann in Excel: So funktioniert die WENN-FunktionIn Excel den Druckbereich festlegen
Tabellen in Excel können mitunter enorme Ausmaße annehmen. Nicht alles davon muss aber für eine Präsentation interessant sein. Statt also seitenweise Papier zu verschwenden, können Sie in Excel den Druckbereich anpassen. So drucken Sie nur die Inhalte aus, die momentan wichtig für Sie sind. Wir erklären, wie Sie den Excel-Druckbereich festlegen und auch wieder aufheben.
In Excel den Druckbereich festlegenExcel-Dropdown: Erstellen einer Auswahlliste
Wer regelmäßig mit großen Datensammlungen in Tabellenkalkulationsprogrammen arbeitet, ist froh über jede Erleichterung im Umgang mit Datenwerten. In Excel sind Auswahllisten eine solche Hilfe. Durch die Dropdown-Liste können Sie passende Inhalte einfach per Mausklick einfügen und brauchen nicht immer wieder den gleichen Text eintippen. Wir erklären, wie Sie in Excel ein Dropdown erstellen können.
Excel-Dropdown: Erstellen einer Auswahlliste