Der SVERWEIS ist die Funktion welche Excel Könner von Excel-Laien unterscheidet.

Wenn Du ein Excel Weiterbildungskurs besuchen wird der Referent Dich mit aller Wahrscheinlichkeit Fragen ob Du den SVERWEIS bereits beherrscht… selbst in «fortgeschrittenen» Excel Kursen!

Das ist auch kein Zufall, denn mit dem SVERWEIS kann man Excel automatisieren wie mit kaum einer zweiten Funktion!

Der SVERWEIS hat einiges auf Lager! Selbst wenn Du die Formel bereits benutzen, könnte eines oder mehrere der unten aufgeführten Beispiele Neuland sein!

Aber fangen wir doch am Anfang an…

Wenn Du die Beispiele sehen möchtest in einer Excel Datei, kannst Du Dir hier die Beispiel herunterladen: SVERWEIS Kompendium!

Einführung: Syntax der SVERWEIS Funktion

Der SVERWEIS

Der SVERWEIS wird benutzt, um nach einem Wert in einer Tabelle zu suchen, und anschliessend diesen Wert, oder einen Wert in der gleichen Zeile zurückzugeben. Sämtliche Berichte, welche erstellt werden müssen enthalten mindestens eine oder mehrere SVERWEIS Formeln. Wer eine Tabelle automatisieren will mit Formeln merkt sehr schnell: am SVERWEIS führt nichts vorbei!

Fangen wir nun also mit der Erklärung dieses Grundpfeilers der Automatisierung an.

Formel Syntax erklärt

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

Hier nun eine einfache Erklärung was es mit der Syntax genau auf sich hat:

=SVERWEIS(“Was man sucht”;”die Matrix in der man sucht”;”die Spalte in der das Resultat steht das ausgelesen wird”;”ob das genaue Suchkriterium gefunden werden soll”)

An dieser Stelle müssen wir noch erwähnen, dass die Matrix, die man ausliest in der ersten Spalte immer den Suchbegriff enthalten muss. Mit der SVERWEIS Formel wird nur diese erste Spalte senkrecht durchsucht bis den Suchbegriff gefunden wird.

Beispiel 1: Einen exakten Wert in einer Spalte finden

Hier das erste Beispiel in dem wir aus einer Tabelle automatisch die Grösse einer Person ermitteln.

Der Namen in Zelle C4 ist in diesem Fall unser Suchkriterium.

Wir suchen danach in der ersten Spalte A2:A5 senkrecht angeordnet, während die jeweilige Grösse der Person, die wir wissen wollen in der zweiten Spalte danebensteht B2:B5.

Sverweis Formel

Die Formel in diesem Beispiel wird also wie folgt lauten:

=SVERWEIS(C4;A2:B5;2;0)

und wenn wir das in Worte fassen, würde es sich etwa wie folgt lesen:

SVERWEIS(Suche den Namen “Marcel” in der ersten Spalte der Matrix A2:B5 und
dort wo es gefunden wurde, zeige an was in der 2. Spalte steht.)

Das Resultat wird dann von der Formel angezeigt (188).

Wenn das Suchkriterium und der Wert in der Matrix exakt übereinstimmen sollen, muss eine 0 (Null) im [Bereich Verweis] gesetzt werden. Wird eine 1 geschrieben oder auch gar nichts, wird der nächst kleinerem Wert angezeigt. Mit anderen Worten, Excel gibt den nächst kleinerem Wert zurück, den es findet. In einer unsortierten Liste kann es so schnell zu Fehler kommen.

Aufgepasst:

Ich empfehle immer eine 0 im optionalen [Bereich_Verweis] zu schreiben, es sei denn man will das bewusst nicht eine exakte Übereinstimmung für das Suchkriterium haben! Meistens wollen wir ein exaktes Ergebnis!

Was meine ich damit? Schauen wir uns ein zweites Beispiel an um ein wenig Licht in die Sache mit dem [Bereich_Verweis] zu bringen.

Beispiel 2: Den nächst höherem oder tieferem Wert finden
Anhand von einer Bestellmenge ermitteln wir den richtigen Rabatt.

In diesem Beispiel stellen wir den [Bereich Verweis] auf 1 und suchen nach dem korrekten Rabatt anhand der Bestellmenge. Wir machen das, weil wir nicht jede erdenkliche Bestellmenge angeben wollen, sondern einfach nur schrittweise den Rabatt erhöhen, wenn eine bestimmte Bestellmenge erreicht ist.

In diesem Beispiel suchen wir den Rabatt für eine Bestellung von 11 Einheiten. Die genaue Anzahl 11 wird in der Tabelle nicht aufgeführt.

Wir haben aber definiert, dass die Formel uns den nächst kleineren Wert liefern soll, in dem wir den [Bereich_Verweis] eine 1 gesetzt haben, so liefert Excel uns nun die Antwort 5% welche gültig ist bei Bestellungen ab 10 Einheiten.

Die Formel lautet in Excel nun:

=SVERWEIS(C4;A2:B5;2;1)

Wer die SVERWEIS Formel kennt kann auch die Wverweis Formel einsetzten, denn der funktioniert gleich, aber halt eben Waagerecht!

Die Erläuterung zu der Funktion die Microsoft selber geschrieben hat könnt ihr hier
nachlesen. Der Bericht ist zwar maschinell übersetzt worden aus dem Englischen, ist aber trotzdem recht gut.

Beispiel 3: Der Waagerechte SVERWEIS (Horizontal Suchen)

Was wenn die Liste, in der man suchen muss nicht vertikal, sondern horizontal angelegt ist?

Keine Sorge! Excel bietet auch hierfür eine elegante Lösung.

Nun da Du den SVERWEIS kennst, kannst Du auch gleich den WVERWEIS benutzen!

Die Syntax ist genau gleich aufgebaut:

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

Wenn Du die Funktion im Detail anschauen möchtest, kannst Du Dir mein Beitrag anschauen in dem ich den WVERWEIS den «kleinen Bruder» vom SVERWEIS nenne, weil er weit weniger eingesetzt wird (da unsere Informationen meistens Vertikal ausgelegt sind), aber eigentlich gleich viel kann.

OK. Ich gebe es zu… Nr.3 war etwas geschummelt

Aber keine Angst, es geht weiter mit SVERWEIS Tricks!

Beispiel 4: Texte, die ein Muster haben finden (Wie man “Wildcard”-Zeichen benutzt)

Was sind «Wildcard» Zeichen? Es sind die Zeichen, welche man in Excel beim Suchen einsetzen kann um stellvertretend für andere Zeichen nach etwas zu suchen.

Spezialzeichen (Wildcard-Character)

=SVERWEIS(“”*Monroe””;$B$7:$C$11;2;0) à Antwort Position 1

=SVERWEIS(“”???””;$B$7:$C$11;2;0) à Antwort Position 4

=SVERWEIS(“”J*Adams””;$B$7:$C$11;2;0) à Antwort Position 3

Was wenn man die Wildcard Zeichen sucht?

Sucht man nach diesen speziellen Zeichen, so muss man davon eine Tilda (~) setzen, dann erkennt Excel, dass man nach diesem Zeichen sucht.

Beispiel 5: Der SVERWEIS nach Links

Da ich dem Thema Sverweis nach Links schon einen ganzen eigenen Blog Beitrag gewidmet habe, hier nur kurz die Zusammenfassung:

Die Lösungsvariante benutzt die WAHL Funktion. Das interessante an dieser Lösung ist, dass man damit jede Beliebige Spalte und in jeder beliebigen Reihenfolge zurückgeben kann!

Meine bevorzugte Methode einen Wert auf der Linken Seite der Suchspalte zurückzugeben ist in den meisten Fällen die Methode welche im Beispiel 13 beschrieben ist.

Beispiel 6: Wie man mehrere Kriterien mit dem SVERWEIS verbindet (Schlüssel)

Die wohl einfachste und unkomplizierteste Art und Weise eine Tabelle abzufragen in der man mehr als ein Abfrage Kriterium hat, ist dass man eine Schlüsselspalte erstellt.

Hier unser Beispiel:

Die Schlüsselspalte setzt sich im obigen Beispiel lediglich aus den zwei vorhergehenden Spalten zusammen, so dass man für den SVERWEIS danach ebenfalls diesen Schlüssel verwenden kann.

=SVERWEIS(“Mike”&”Süd”;$D$33:$P$46;6;0)

Wichtig hierbei ist, dass die Schlüssel, die erstellt werden, immer nur einmal vorkommen. Ansonsten laufen wir Gefahr, dass der falsche Wert zurückgegeben wird!

Beispiel 7: Mehrfachkonditionaler SVERWEIS

«Wofür benötigt man ein Mehrfachkonditionaler SVERWEIS?», fragst Du Dich vielleicht.

Eigentlich für das gleiche wie im Beispiel Nr. 6. Es gibt mehrere Kriterien auf der die Abfrage basiert und auf die eine es nur jeweils eine Antwort gibt.

Aber anstelle, dass man eine Hilfsspalte einbaut, umgeht man den Schritt ganz, in dem man das bilden eines Schlüssels direkt in die SVERWEIS Abfrage integriert!

In der Formel stellt es sich dann wie folgt dar:

Die Formel zu verstehen bedarf einer etwas längerer Erklärung. Diese habe ich aber zum glück schon einmal geschrieben in dem sehr ausführlichen Beitrag: Mehrfachkonditionaler-SVERWEIS

Beispiel 8: Kalkulierte Werte aus mehreren Spalten zurückgeben mit SVERWEIS

Was nun aber wenn Du anstelle mehrerer Kriterien in der Abfrage den Wunsch hast mehrerer Resultate anzuzeigen? Hier ein paar Beispielhafter Fragen:

• Verkäufe von Lars im 1. Quartal
• Höchster Verkaufswert von Carmen während dem Jahr
• Monatsdurchschnitt der Verkäufe von Johan

…und die Daten werden wie gewohnt in der Monatssicht präsentiert…

Diese Fragen lassen sich ebenfalls mit dem SVERWEIS beantworten. Diesmal in Kombination mit anderen Funktionen welche dann zum Schluss alle als Matrix Formeln (STRG+SHIFT+ENTER) eingegeben werden müsse!

Ich werde hier lediglich die erste Formel erläutern, denn anhand dieses Beispiels, werden die anderen beiden schnell auch verständlich.

Die 3 wichtigsten Teile, die es bei dieser Formelkombination zu verstehen gilt sind hervorgehoben.

1. Im Bereich in dem wir normalerweise eine Antwortspalte definieren benutzen wir geschweifte Klammern ( { } ) und der Backslash ( \ ) um Excel mitzuteilen dass wir eine Matrix in die Formel einbauen!
   Hinweis: ob Ihr ein Backslash oder ein anderes Zeichen benutzen müsst damit die Formel für Euch funktioniert hängt von den «Regional Settings» des PC ab! Was es bei Euch ist könnt Ihr am einfachsten erfahren in dem Ihr die Beispiel Datei herunterlädt und die Formel dort direkt anschaut… Excel konvertiert es für Euch!
2. Die normale SVERWEIS Formel, wird jetzt in eine SUMME Formel gepackt
3. Letztendlich muss das Ganze dann als Matrix Formel eingegeben werden… ansonsten funktioniert Sie nicht (STRG+ALT+ENTER)!

Schön und gut… aber was macht die Formel denn nun eigentlich?

In Worte gefasst könnte man es so sagen: wir suchen «Lars» wie immer in der ersten Spalte.

Wenn wir Ihn dann gefunden haben, so gibt uns die Formel anstatt nur einer, gleich drei Zellen zurück!

Weil Excel aber immer nur einen Wert in einer Zelle anzeigen kann, Summieren wir die drei Werte.

Damit Excel diesen Trick zulässt müssen wir die Formel als Matrix Formel eingeben!

Beispiel 9: SVERWEIS mit Tabellen benutzen

Der grösste Fluch mit dem SVERWEIS ist, dass man den Bereich anpassen muss, weil die Anzahl Datensätze grösser geworden sind!

Übersieht man das, so passieren Fehler: die Formel gibt entweder keinen Wert zurück (#N/V) oder einen falschen Wert!

Um das zu vermeiden benutzen wir Strukturierte Tabellen (wird über Einfügen>Tabelle erstellt)!

Benutzt man danach eine Formel, die auf diese Tabelle verweist, so muss man sich keine Sorgen mehr machen das neue Datensätze die unten hinzukommen verloren gehen! Die Tabelle nimmt die neuen Datensätze automatisch in ihren Bereich auf!

Hier ein Beispiel wie ein SVERWEIS aussieht der auf einer solche Tabelle aufsetzt:

=SVERWEIS(“Carmen”;tblBeispiel;3;0)

Beispiel 10: Wie man die Spaltensuche auch automatisiert

Sehr praktisch ist die SVERWEIS Formel auch wenn man es mit sehr grossen Tabellen zu tun hat.

Gerade hier benötigt man dann aber meistens ein wenig Hilfe damit man die richtige Spalte als Resultat zurückgibt.

Das ist nicht leicht, wenn die Tabelle 100 oder mehr Spalten hat!

Natürlich gibt es den einten oder anderen Trick gibt um die Anzahl Spalten die man nach rechts gehen muss zu berechnen (die Formel SPALTE() kommt in Sinn), aber noch einfacher geht es wenn man einfach die Spaltenüberschrift angibt, und Excel dann selber berechnet wo diese ist!

Das macht man mit der VERGLEICH() Formel.

Die Syntax der VERGLEICH() Formel ist ähnlich wie die des SVERWEISES..

=VERGLEICH(Suchkriterium;Suchvektor;[Vergleichstyp])

Mit der VERGLEICH Formel (1)

1. finden wir die richtige Spalte, welche die wir in der SVERWEIS Formel einbetten.
2. Damit auch diese Formel dynamisch bleibt, benutzen wir auch hier wieder eine Strukturierte Tabellen Referenz!
3. Diesmal können wir aber zusätzlich noch spezifizieren, dass wir nur die Kopfzeile wollen für die VERGLEICH Formel!

Beispiel 11: Den zweiten Eintrag mit SVERWEIS zurückgeben

Es gibt die Situation, dass wir einen Wert in einer Liste suchen, dieser aber nicht nur einmal, sondern mehrmals vorhanden ist in der Liste… und zwar gewollt (kommt oft vor bei Berichte, die aus Systemen generiert werden, die aber weiter verarbeitet werden müssen)!

Wie sucht man nun mit dem SVERWEIS nach dem zweiten Eintrag in einer Liste?

Stellen wir uns vor, dass wir den zweiten Eintrag für Thomas in dieser Liste suchen:

Damit wir den korrekten Wert zurückgeben können benötigen wir wiederum eine Hilfsspalte. In dieser führen wir eine Formel ein welche die Anzahl mal die eine Begriff / Name vorkommt berechnet und jeweils einen eindeutigen Schlüssel bildet.

In der Schlüsselspalte steht folgende Formel in Zelle B2 (welche runter kopiert werden kann):

=A2&ZÄHLENWENNS($A$2:A2;A2)

Beachten muss man hier dass bei ZÄHLENWENNS in der ersten Referenz der Bereich festgeschrieben wird (Absolute Zellen Referenz) und der zweite Teil des Bereiches aber dynamisch bleibt! So zählt die Formel beim runter kopieren die fortlaufende Anzahl des zu zählenden Begriffes!

Somit können wir nun die SVERWEIS Formel wieder wie gewohnt benutzen:

=SVERWEIS(B5;$B$2:$E$7;1;0)

Beispiel 12: Der Sverweis in PowerPivot

In vielen Fällen wird der SVERWEIS zum Anreichern von Daten benutzt, die dann nachher in einer Pivot Tabelle ausgewertet werden. Wer riesige Tabellen mit dem SVERWEIS anreichert wird schnell merken, dass sein Excel Workbook rasant mehr Speicherplatz benötigt, aber viel schlimmer als das, dass Excel richtig lahm wird!

Diese Problematik kann man mit verschieden Mittel beheben, aber einen guten Weg das ganze zu umgehen bietet PowerPivot, indem man ein Datenmodell erstellt so wie in einer Datenbank (zB MS Access).

So sieht dann das Pivot Tabellen Menü aus, welches auf einer solchen Beziehung beruht.

PowerPivot ist in Excel 365, Excel 2019, 2016, und 2013 standardmässig vorhanden und muss lediglich unter COM Add-Ins aktiviert werden (im Ribbon unter Entwicklertools Menü). Nur in der Excel 2010er Version muss man es noch separat heruntergeladen werden von der Microsoft Website als separates gratis Add-In.

Eine Ausführliche Beschreibung wie man das Thema angeht habe ich ebenfalls schon einmal gemacht in diesem Beitrag: Daten in einer Tabelle mit dem SVERWEIS und PowerPivot anreichern!

Beispiel 13: Index() Vergleich() die Sverweis Kombination für fortgeschrittene

Der SVERWEIS, so großartig er auch ist, hat seine Grenzen. Wie bereits in verschiedenen Punkten oben erwähnt, kann man den SVERWEIS oft auch mit anderen Formeln aus der Verweis Family verbinden, um effizienter zu arbeiten!

Eine Kombination, welche den SVERWEIS überhaupt nicht braucht, aber trotzdem oft von Profis als Ersatz bei komplizierteren Abfragen eingesetzt wird ist die INDEX/VERGLEICH Kombination.

Im Gast Beitrag EXCEL INDEX/VERGLEICH-FUNKTIONEN von Dr. Klaus Kis, ist ausführlich beschrieben wie man die INDEX/VERGLEICH Kombination einsetzen kann!