Stell dir vor, du suchst in einer Getränkekarte den Preis für ein bestimmtes Getränk.
Du wanderst mit deinen Augen in der Spalte ganz links bei den Getränkenamen in die Zeile, wo du das gewünschte Getränk findest. Dann gehst du nach rechts in die dritte Spalte, wo die Preise stehen und liest die gesuchte Information.
Genau so arbeitet auch die SVERWEIS-Funktion. Sie sucht in der ganz links stehenden Spalte nach Übereinstimmung mit einem Suchbegriff (dem Getränkenamen) und liest aus einer der Spalten rechts davon die gewünschte Information (den Preis).
Dazu übergibt man der SVERWEIS-Funktion den Suchbegriff (1. Parameter), den Zellbereich, in dem gesucht und gefunden wird, sozusagen die Getränkekarte (2. Parameter) und die Nummer der Spalte, aus der die Information gelesen werden soll (3. Parameter). Der vierte Parameter wird weiter unten genauer erklärt.
Beispiel 1
In der Zelle G6 steht die Formel:
=SVERWEIS(G4; $B$4:$D$20; 2; FALSCH)
| Suchkriterium: | G4 |
| Matrix: | $B$4:$D$20 |
| Spaltenindex: | 2 |
| Bereich_Verweis: | FALSCH |
In der Zelle G7 steht dieselbe Formel, nur der Spaltenindex wurde in "3" geändert.
In diesem Fall suchen wir kein Getränk, sondern den Namen eines Mitarbeiters (hier den Namen 'Neumeier'). An Stelle des Preises wollen wir wissen, zu welcher Abteilung der Mitarbeiter Neumeier gehört (Spalte 2) und wieviele Überstunden er hat (Spalte 3).
Zum Ausprobieren:
Allgemeine Hinweise
Parameter 'Suchkriterium':
Der Parameter 'Suchkriterium' enthält den Begriff, nach dem gesucht wird. Hier kann eine Zahl, ein Text in Anführungszeichen, ein Zellbezug (z. B. wie hier G4) oder ein komplizierterer Ausdruck stehen.
Parameter 'Matrix':
Im Parameter 'Matrix' gibt man einen (mehrspaltigen) Bereich an, der die Suchspalte und die Informationsspalten enthält. Zu beachten ist, dass die erste (ganz linke) Spalte immer die Suchspalte ist.
Parameter 'Spaltenindex':
Der Parameter 'Spaltenindex' gibt die Nummer derjenigen Spalte an, die die gesuchte Information liefert (z. B. die Abteilung oder die Überstunden). Gezählt wird ab der Suchspalte nach rechts. Die Suchspalte hat die Spaltennummer 1.
Parameter 'Bereich_Verweis':
Der Parameter 'Bereich_Verweis' kann die beiden Werte WAHR oder FALSCH annehmen. Schreibfaule können hier auch 1 (= WAHR) oder 0 (= FALSCH) eintragen. Er ist optional. Wird er weggelassen, so hat er automatisch den Wert 'WAHR'.
FALSCH bedeutet: Es wird nach exakter Übereinstimmung gesucht. Wird nichts gefunden, trägt die Funktion den Errorcode '#NV' in die Zelle ein. Gibt es mehrere Übereinstimmungen (z. B. mehrmals den Namen 'Neumeier' in Spalte B), wird der erste Treffer verwendet.
WAHR bedeutet: Die Funktion setzt voraus, dass die Suchspalte aufsteigend sortiert ist. Sie sucht den letzten (d. h. größten) Wert, der noch kleiner oder gleich dem Suchkriterium ist.
Auf diese Weise wird bei der Suche nicht auf einen einzelnen exakt übereinstimmenden Wert verwiesen, sondern auf einen bestimmten Bereich von Werten (Bereich_Verweis = WAHR). Hört sich schwer verständlich an, aber das folgende Beispiel 2 zeigt einen solchen Fall und ist nicht schwer zu verstehen.
Das 'S' im Namen 'SVERWEIS' steht für 'senkrecht'. Die Funktion sucht senkrecht nach unten und 'verweist' auf die Zelle, in der die gesuchte Information steht. Für eine waagerechte Suche gibt es noch die Funktion 'WVERWEIS'.
Beispiel 2
Formel in Zelle E5:
=SVERWEIS(D5; $G$5:$H$7; 2)
| Suchkriterium: | D5 |
| Matrix: | $G$5:$H$7 |
| Spaltenindex: | 2 |
| Bereich_Verweis: | Fehlt - also Defaultwert WAHR |
Die Formel in Zelle E5 wurde nach unten in die Zellen E6:E12 kopiert.
Zum Ausprobieren:
Das Suchkriterium ist der Einzelpreis 144,50. Durchsucht wird die erste Spalte der roten Tabelle rechts. Dort stehen die Werte 0, 50, 100, 200 und 500. Der Wert 144,50 wird dort nicht gefunden. Da der Parameter 'Bereich_Verweis' jedoch auf WAHR gesetzt ist, wird keine exakte Übereinstimmung gesucht, sondern der Bereich, zu dem der Preis 144,50 gehört.
Man kann sich das auch so vorstellen: Wenn die gesuchte Zahl (hier 144,50) nicht gefunden wird, wird in der Liste der möglichen Zahlen (0, 50, 100, 200, 500) die nächstkleinere (100) genommen. Die Zahl 100 entspricht der Kategorie C.
Wenn man überlegt, welche Zahlen alle in die Kategorie C fallen würden, dann ist das die Zahl 100 selbst und alle größeren (z.B. 101, 133, 180, 199), die noch kleiner sind als 200. Dieser ganze Bereich von Zahlen verweist auf die Kategorie C. Deshalb spricht man hier von einem "Bereichsverweis". Der Parameter 'Bereich_Verweis' ist WAHR.
In komplizierter Sprechart: Von den Werten 0, 50, 100, 200 und 500 wird der größte gesucht, der gerade noch kleiner oder gleich ist als das Suchkriterium 144,50. Und das ist der Wert 100! Somit findet die Funktion in der Spalte 2 der Matrix die Kategorie "C".
Bei realen Beispielen befindet sich die rote Tabelle mit den Kategorien sicherlich auf einem anderen Excel-Blatt oder sogar in einer anderen Arbeitsmappe.
Neue Funktion XVERWEIS
Die Funktion SVERWEIS wird ab Excel 2021 und in Excel 365 durch die neue Funktion XVERWEIS ersetzt. Wer Informationen über die Funktion XVERWEIS sowie Beispiele sucht und außerdem wissen möchte, wie man die Funktion XVERWEIS auch in älteren Excel-Versionen einsetzen kann, wird auf dieser Seite fündig:
▸Funktion XVERWEIS
