SVerweis in Excel einfach erklärt: So funktioniert's

Microsoft Excel stellt eine Fülle von Funktionen zur Datenanalyse bereit. Eine der bekanntesten ist die Formel SVerweis, die dazu dient, Daten in Tabellen zu durchsuchen. Dieser Artikel erläutert den Einsatz von SVerweis in Excel und stellt darüber hinaus Alternativen vor.

Was ist die SVerweis-Funktion in Excel?

Der SVerweis, auch als VLookup bekannt, steht für “Vertikaler Verweis” (oder “Senkrechter Verweis”) und ist einer der häufigsten und nützlichsten Befehle in Excel für den Umgang mit großen Datenmengen. Dieser Befehl ermöglicht es Benutzern, bestimmte Werte in einer Tabelle oder Spalte zu lokalisieren und zu extrahieren, indem eine Bezugszelle und eine Spalte zum Suchen angegeben werden. Wenn man etwa eine Tabelle hat, die aus zwei Spalten besteht (Name und E-Mail-Adresse), kann man mit dem SVerweis-Befehl nach einem bestimmten Namen suchen und die zugehörige E-Mail-Adresse ausgeben lassen.

Aber Vorsicht! Der SVerweis sucht ausschließlich in einer Richtung: von links nach rechts.

Syntax und Funktion der Formel SVerweis

Die Syntax für die SVerweis-Funktion lautet:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex;[Bereich_Verweis])

Argumente

• Suchkriterium: Der Wert, der in der ersten Spalte der Matrix gesucht werden soll. Der Wert kann ein Text, ein logischer Wert oder eine Zahl sein.

• Matrix: Der Bereich, in dem die Suchkriterien und die zurückzugebenden Werte gespeichert sind. Der Bereich muss mindestens zwei Spalten enthalten.

• Spaltenindex: Die Spalte in der Matrix, aus der der Wert zurückgegeben werden soll. Die erste Spalte in der Matrix hat den Spaltenindex 1. Die Spalte rechts von der ersten Spalte hat die Spaltennummer 2 usw.

• Bereich_Verweis: Ein logischer Wert, der angibt, ob man eine genaue oder eine ungefähre Übereinstimmung wünscht. Bei WAHR oder 1 wird eine ungefähre Übereinstimmung durchgeführt. Bei FALSCH oder 0 wird eine exakte Suche durchgeführt. Dieses Kriterium ist optional. Wenn das Argument Bereich_Verweis weggelassen wird, wird die ungefähre Übereinstimmung durchgeführt.

Beispiele für SVerweis in Excel

Im Folgenden werden einige Beispiele für den Einsatz der SVerweis-Funktion in Excel dargestellt.

Einfache SVerweis-Funktion

Als Ausgangspunkt habe ich eine Tabelle mit Namen und E-Mail-Adressen.

Nun möchte ich die E-Mail-Adresse von “Cooper Schinacher” finden. Hierzu nutze ich die SVerweis-Funktion.

=SVERWEIS("Cooper Schinacher";A2:B6;2;0)

Ich kann auch den Bereich A1:B6 als Tabelle formatieren (STRG+T) und die Tabelle als Matrix verwenden.

=SVERWEIS("Cooper Schinacher";Tabelle1;2;0)

Dynamischer Spaltenindex

Nachfolgend habe ich eine Tabelle zu einem Lagerbestand, mit Produkt, Preis, Anzahl und Kategorie. Ich kann herausfinden, wie teuer ein Apfel ist, indem ich den SVerweis verwende.

=SVERWEIS("Apfel";A2:D6;2;0)

Was aber, wenn ich nicht genau weiß, in welcher Spalte der Preis steht? Ich kann den Spaltenindex dynamisch gestalten, indem ich die Funktion VERGLEICH verwende.

=SVERWEIS("Apfel";A2:D6;VERGLEICH("Preis (EUR)";A1:D1;0);0)
bzw.
=SVERWEIS("Apfel";Tabelle2;VERGLEICH("Preis (EUR)";Tabelle2[#Kopfzeilen];0);0)

Nicht vorhandener Suchbegriff

Wenn der Suchbegriff im Suchbereich nicht vorhanden ist, gibt die SVerweis-Funktion die Fehlermeldung #NV zurück.

Dies kann ich mit der Funktion WENNNV abfangen.

=WENNNV(SVERWEIS("Birne";A2:D6;2;0);"Nicht vorhanden")

Unpraktisch ist allerdings, dass jetzt nicht erkennbar ist, ob das Produkt oder der Spaltenname nicht existiert. Dies kann ich jedoch mit den Funktionen WENN und ISTNV abfangen.

=WENNNV(SVERWEIS($F$2;Tabelle2;VERGLEICH($F$3;Tabelle2[#Kopfzeilen];0);0);WENN(ISTNV(VERGLEICH($F$3;Tabelle2[#Kopfzeilen];0));"Spalte nicht vorhanden";"Produkt nicht vorhanden"))

Noch einfacher ist es, wenn Teile der Funktion mit LET definiert werden.

=LET(Suchbegriff;$F$2;Tabelle;Tabelle2;Kopfzeilen;Tabelle2[#Kopfzeilen];Spaltenname;$F$3;Spaltenindex;VERGLEICH(Spaltenname;Kopfzeilen;0);
WENNNV(SVERWEIS(Suchbegriff;Tabelle;Spaltenindex;0);WENN(ISTNV(Spaltenindex);"Spalte nicht vorhanden";"Produkt nicht vorhanden")))

Übrigens sollte man auch darauf achten, dass die Typen der Suchkriterien und der Matrix übereinstimmen. Wenn ich beispielsweise nach “1” (Text) suche, wird SVerweis nur eine “1” (als Text) und nicht 1 (als Zahl) finden. Dementsprechend ist es bei Zahlen eine gute Idee, die Funktion WERT zu verwenden, um sicherzustellen, dass die Typen übereinstimmen. Prüfen lässt sich das mit der Funktion TYP.

Suche über mehrere Arbeitsblätter

Angenommen, ich habe eine Excel-Datei mit mehreren Arbeitsblättern. Auf jedem Arbeitsblatt habe ich eine Tabelle mit Produkten und Preisen.

Ich möchte nun die Preise für Januar und Februar in einer Tabelle zusammenfassen. Hierzu verwende ich die Funktion SVerweis zusammen mit der Funktion INDIREKT.

=SVERWEIS($A2;INDIREKT("'"&B$1&"'!$A$2:$B$6");2;0)

Auf diese Weise wechsle ich bei der SVerweis-Funktion dynamisch das Arbeitsblatt.

Ungefähre Übereinstimmung

In manchen Fällen benötigt man auch eine ungefähre Übereinstimmung.

Ich habe eine Tabelle mit Produkten und Preisen (tbl_Produkte) und indessen auch eine zweite Tabelle mit einem Mengenrabatt (tbl_Mengenrabatt).

=LET(
Suchbegriff;$F$2;
Tabelle;tbl_Produkte;
Menge;$F$3;
WENNNV(SVERWEIS(Suchbegriff;Tabelle;2;0)*Menge;"Produkt nicht vorhanden")
)

Wenn die Menge jedoch größer als 10 ist, soll der Preis aber mit den Daten aus der Tabelle tbl_Mengenrabatt rabattiert werden.

Hier benötige ich die ungefähre Übereinstimmung, weil die Menge nicht nur 10, sondern auch beispielsweise 11 sein könnte.

=LET(
Suchbegriff;$F$2;
Menge;$F$3;
Rabatt;1-SVERWEIS(Menge;tbl_Mengenrabatt;2;1);
WENNNV(SVERWEIS(Suchbegriff;tbl_Produkte;2;0)*Menge*Rabatt;"Produkt nicht vorhanden")
)

SVerweis mit mehreren Kriterien

Wenn ich nach mehreren Kriterien suchen möchte, kann ich die Funktion SVerweis mit der Funktion WAHL kombinieren.

Beispiel: Ich habe eine Tabelle mit Daten aus Bilanzen verschiedener Gesellschaften.

Ich möchte nun den Saldo für die Forderungen der Gesellschaft 2 im Jahr 2020 ermitteln.

=SVERWEIS("Gesellschaft 2" & "-" & "2020" & "-" & "Forderungen"; WAHL({1.2};A2:A9 & "-" & B2:B9 & "-" & C2:C9;D2:D9);2;0)

Was passiert hier? Die Funktion WAHL erzeugt eine neue Matrix, in der die einzelnen Suchkriterien miteinander verknüpft werden. In der ersten Spalte befinden sich alle Suchkriterien, die duch einen Bindestrich verknüpft sind, in der zweiten Spalte der Saldo, den wir suchen. Die Funktion SVerweis sucht dann in dieser Matrix nach dem Suchkriterium und gibt den Saldo (Spalte 2) zurück.

Alternativen zu SVerweis

Suchrichtung nach unten

Die Funktion SVerweis sucht immer nach rechts. Wenn ich waagerecht (nach unten) suchen möchte, kann ich die Funktion WVERWEIS verwenden.

=WVERWEIS(Suchkriterium; Matrix; Zeilenindex;[Bereich_Verweis])

Index und Vergleich (Index & Match)

Um mehr Flexibilität bezüglich der Suchrichtung und der Spaltenauswahl zu haben, kann ich die Funktionen INDEX und VERGLEICH verwenden.

=INDEX(Matrix;VERGLEICH(Suchkriterium;Matrix;[Vergleichstyp]);[Spaltenindex])

XVerweis

Seit Excel 2019 gibt es die Funktion XVerweis mit Suchfunktion in alle Richtungen und integrierter Fehlerbehandlung. Diese Funktion kann SVerweis, WVerweis, Index und Vergleich ersetzen. Allerdings sollte man Kompatibilitätsprobleme mit älteren Excel-Versionen und die Geschwindigkeit bei größeren Dateimengen beachten.

=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;wenn_nicht_gefunden;Vergleichsmodus;Suchmodus)

Langfristig wird die Funktion SVerweis vermutlich durch XVerweis abgelöst werden, da sie mehr Möglichkeiten bietet und einfacher zu handhaben ist.

SummeWenns

Ein mögliches Problem bei SVerweis ist, dass es nur den ersten Treffer zurückgibt. Wenn ich etwa die Summe der Obstartikel im Lager berechnen möchte, kann SUMMEWENNS alle Treffer berücksichtigen und summieren.

=SUMMEWENNS(tbl_Produkte[Anzahl auf Lager];tbl_Produkte[Kategorie];$F$2)

In Kombination mit der Funktion ZÄHLENWENNS kann ich auch die Anzahl der Treffer ermitteln.

=TEXTVERKETTEN(": ";0;ZÄHLENWENNS(tbl_Produkte[Kategorie];$F2);SUMMEWENNS(tbl_Produkte[Anzahl auf Lager];tbl_Produkte[Kategorie];$F2))

Filter

Möchte ich alle Treffer sehen, kann ich auch die neue Funktion FILTER verwenden. In meinem Fall kombiniere ich sie mit der MTRANS Funktion, um die Ergebnisse in einer Reihe anzuzeigen.

=MTRANS(FILTER(tbl_Produkte[Produktname];tbl_Produkte[Kategorie]=$F2))

Fazit

Obwohl SVerweis eine sehr populäre Funktion in Excel ist, gibt es viele alternative Methoden, die je nach Situation besser geeignet sein können. Die Wahl der richtigen Methode hängt von der Art der Daten und den speziellen Anforderungen der Auswertung ab. Es lohnt sich daher, mit verschiedenen Ansätzen zu experimentieren, um herauszufinden, welche für die eigenen Bedürfnisse am besten geeignet ist.