SVERWEIS in Excel: Anleitung mit Dienstplan-Beispiel (und wann XVERWEIS besser ist)

Was macht der SVERWEIS?

Das „S“ steht für senkrecht: Die Funktion durchsucht eine Spalte von oben nach unten, bis sie den gesuchten Wert findet, und geht dann in derselben Zeile nach rechts bis zur angegebenen Spalte. Das englische Original heißt VLOOKUP, das waagerechte Gegenstück WVERWEIS.

Im Dienstplan ist das die Brücke zwischen zwei Welten. In der Planfläche stehen kurze Kürzel wie F, S und N, weil sie sich schnell eintragen und per Dropdown-Liste absichern lassen. Für Stundensummen, Besetzungszählung und Zuschläge braucht die Auswertung aber Zahlen. Statt Uhrzeiten und Stunden in jeder Zelle zu wiederholen, stehen sie genau einmal in einer Legende – und der SVERWEIS holt sie dort ab. Ändert sich eine Schichtzeit, wird sie an einer Stelle korrigiert, und alle Summen rechnen neu.

Syntax: die vier Argumente

Microsoft beschreibt die Funktion mit vier Argumenten, von denen das letzte optional ist. In deutschsprachigem Excel werden sie mit Semikolon getrennt.

Zwei Eigenschaften folgen direkt aus dieser Konstruktion. Erstens muss der Suchwert in der linken Spalte der Matrix stehen – Microsoft formuliert es als Bedingung, nicht als Empfehlung. Zweitens ist der Spaltenindex eine feste Zahl. Fügt jemand in der Legende eine Spalte ein, zeigt die 5 auf etwas anderes, ohne dass Excel einen Fehler meldet. Beide Punkte tauchen weiter unten bei den Fehlerbildern wieder auf.

Bereich_Verweis: WAHR oder FALSCH?

Das vierte Argument ist die häufigste Fehlerquelle, weil seine Voreinstellung für die meisten Anwendungen die falsche ist. Mit FALSCH sucht Excel eine genaue Übereinstimmung; gibt es keine, erscheint #NV. Mit WAHR – oder wenn das Argument fehlt – sucht Excel den größten Wert, der kleiner oder gleich dem Suchkriterium ist. Das funktioniert nur, wenn die erste Spalte aufsteigend sortiert ist; andernfalls kann laut Microsoft ein unerwartetes Ergebnis herauskommen, und zwar ohne Fehlermeldung.

Für Schichtkürzel ist die ungefähre Suche gefährlich: Ein unbekanntes Kürzel „M“ würde je nach Sortierung stillschweigend die Werte einer anderen Schicht erhalten. Deshalb gilt als Faustregel: Bei Texten, Kürzeln und Nummern immer FALSCH. Die ungefähre Suche hat ihren Platz bei Staffeln – also überall dort, wo ein Zahlenwert in eine Stufe einsortiert wird.

Beispiel für WAHR: die Pausenstaffel nach § 4 ArbZG

Nach § 4 Arbeitszeitgesetz ist die Arbeit bei mehr als sechs bis zu neun Stunden durch mindestens 30 Minuten Ruhepause zu unterbrechen, bei mehr als neun Stunden durch mindestens 45 Minuten. Diese Staffel lässt sich als kleine Tabelle mit Untergrenzen in Minuten anlegen:

Die Formel =SVERWEIS(D5;Stammdaten!$H$2:$I$4;2;WAHR) liefert für eine Arbeitszeit von 480 Minuten in D5 den Wert 30, für 360 Minuten den Wert 0. Entscheidend sind die Untergrenzen 361 und 541 statt 360 und 540: Weil die ungefähre Suche „kleiner oder gleich“ trifft, würde eine Grenze von 360 schon bei genau sechs Stunden eine Pause fordern – das Gesetz verlangt sie aber erst bei mehr als sechs Stunden. Wer mit Minuten in ganzen Zahlen statt mit Uhrzeiten rechnet, umgeht zugleich Rundungsprobleme bei Zeitwerten. Wie eine vollständige Prüfspalte für Pausen und Ruhezeiten aussieht, zeigt der Beitrag Arbeitszeiterfassung in Excel.

Schritt für Schritt: Schichtkürzel in Uhrzeiten und Stunden übersetzen

Ausgangspunkt ist eine Arbeitsmappe mit einem Blatt Stammdaten und einem Blatt Plan. Im Plan stehen die Mitarbeitenden in den Zeilen 5 bis 14 und die Tage des Monats in den Spalten C bis AG.

1. Schichtlegende anlegen. Auf dem Blatt Stammdaten in A1:E1 die Überschriften Kürzel, Bezeichnung, Beginn, Ende und Nettostunden eintragen, darunter je Schicht eine Zeile. Das Kürzel muss in Spalte A stehen, weil der SVERWEIS nur dort sucht.
2. Kürzel eindeutig halten. Jedes Kürzel darf nur einmal vorkommen. Bei Dubletten liefert der SVERWEIS stets den ersten Treffer, und eine geänderte zweite Zeile bliebe wirkungslos.
3. Formel für die Stunden schreiben. In einem Auswertungsbereich – etwa auf einem eigenen Blatt oder in einem Spiegelbereich unter dem Plan – in die erste Zelle =WENNFEHLER(SVERWEIS(Plan!C5;Stammdaten!$A$2:$E$7;5;FALSCH);0) schreiben.
4. Bezug fixieren. Die Matrix trägt Dollarzeichen, das Suchkriterium nicht. So wandert C5 beim Kopieren nach rechts und unten mit, die Legende bleibt fest. Unter Windows schaltet F4 im markierten Bezug zwischen relativ und absolut um.
5. Kopieren und summieren. Die Formel über alle Tage und Personen ziehen; eine Zeilensumme liefert die Monatsstunden je Person.

Die Zeiten sind Beispielwerte; jeder Betrieb trägt seine eigenen Schichtzeiten und Pausen ein. Mit derselben Matrix und anderem Spaltenindex holt =SVERWEIS(C5;Stammdaten!$A$2:$E$7;3;FALSCH) den Beginn und …;4;FALSCH) das Ende. Die Ergebniszelle braucht dann das Zahlenformat hh:mm – ohne Format zeigt Excel den internen Zeitwert, für 06:00 Uhr also 0,25.

Dass die Nettostunden als fertige Zahl in der Legende stehen, ist kein Zufall. Wer stattdessen Ende minus Beginn rechnet, bekommt beim Nachtdienst einen negativen Wert, weil 06:00 kleiner ist als 22:00. Die Rechnung über Mitternacht mit REST und das Format [h]:mm erklärt Arbeitsstunden in Excel korrekt summieren. Für Urlaub und Arbeitsunfähigkeit steht hier 0; ob ein Urlaubstag im Arbeitszeitkonto mit Sollstunden angerechnet wird, regelt der Betrieb – das zeigt Arbeitszeitkonto in Excel führen.

Tipp: Legende als Excel-Tabelle

Wird die Legende mit Strg+T in eine Excel-Tabelle namens tblSchichten umgewandelt, kann die Matrix als tblSchichten angegeben werden: =SVERWEIS(C5;tblSchichten;5;FALSCH). Der Bereich wächst dann automatisch, wenn unten eine neue Schicht dazukommt – ein fester Bereich wie $A$2:$E$7 würde die siebte Schicht in Zeile 8 nicht mehr finden. Dieselbe Tabelle kann zugleich die Quelle des Dropdowns sein, wie im Beitrag Excel Dropdown erstellen beschrieben.

Zweites Beispiel: Mitarbeiter-Stammdaten nachschlagen

Der zweite typische Einsatz im Dienstplan sind Personaldaten. Auf einem Blatt Personal steht je Person eine Zeile mit Personalnummer, Name, Bereich und vertraglichen Wochenstunden. Im Plan wird nur die Personalnummer in Spalte A eingetragen; Name und Sollstunden kommen per Formel:

Das Dollarzeichen vor dem A in $A5 hält die Suchspalte fest, wenn die Formel nach rechts kopiert wird. Die Personalnummer ist als Suchschlüssel besser geeignet als der Name: Namen ändern sich, kommen doppelt vor oder werden unterschiedlich geschrieben. Eine Falle gibt es dabei: Steht die Nummer im Plan als Zahl und in der Personalliste als Text – oft nach einem Import –, findet der SVERWEIS nichts. Microsoft nennt diese Abweichung der Datentypen ausdrücklich als Ursache für #NV.

Typische Fehler und ihre Ursachen

Die Suchspalte steht nicht links

Soll im Plan zu einem Namen die Personalnummer ermittelt werden, steht der Suchwert (Spalte B der Personalliste) rechts vom Ergebnis (Spalte A). Der SVERWEIS kann das nicht. Die klassische Lösung kombiniert zwei Funktionen: =INDEX(Personal!$A$2:$A$40;VERGLEICH(B5;Personal!$B$2:$B$40;0)). VERGLEICH ermittelt die Zeilennummer des Namens, INDEX holt aus der Nummernspalte den Wert dieser Zeile. Die 0 steht für die genaue Suche. Die Kombination läuft in jeder Excel-Version.

Der Spaltenindex verrutscht

Die starre Spaltennummer lässt sich durch VERGLEICH auf die Überschrift ersetzen: =SVERWEIS(C5;Stammdaten!$A$1:$E$7;VERGLEICH("Nettostunden";Stammdaten!$A$1:$E$1;0);FALSCH). Wird eine Spalte eingefügt, findet die Formel die Überschrift an ihrer neuen Position. Die Matrix beginnt dafür in Zeile 1, damit Überschriften und Index zueinander passen.

WENNFEHLER und WENNNV: Fehler gezielt abfangen

Im Dienstplan ist #NV oft kein echter Fehler, sondern ein leerer Tag. Ohne Abfangen wird aber jede Summe über diesen Zellen ebenfalls zu #NV. Zwei Funktionen helfen:

• WENNFEHLER(Wert;Wert_falls_Fehler) fängt laut Microsoft alle Fehlertypen ab – #NV, #WERT!, #BEZUG!, #DIV/0!, #ZAHL!, #NAME? und #NULL!.
• WENNNV(Wert;Wert_bei_NV) fängt nur #NV ab und lässt alle anderen Fehler stehen.

Für den Plan ist WENNNV oft die sauberere Wahl. Ein #BEZUG! nach einer gelöschten Spalte ist ein Konstruktionsfehler, der sichtbar bleiben sollte; WENNFEHLER(…;0) würde ihn zu einer unauffälligen Null machen, und die Monatsstunden wären einfach zu niedrig. Noch besser ist es, leere Zellen und unbekannte Kürzel zu unterscheiden: =WENN(C5="";0;WENNNV(SVERWEIS(C5;Stammdaten!$A$2:$E$7;5;FALSCH);"?")) gibt bei leerem Tag 0 aus, bei einem Kürzel, das nicht in der Legende steht, aber ein Fragezeichen. Wer zusätzlich eine bedingte Formatierung auf „?“ legt, sieht Tippfehler sofort. Weitere Konstruktionsfehler dieser Art sammelt der Beitrag Die häufigsten Fehler in Excel-Dienstplänen.

SVERWEIS oder XVERWEIS?

Microsoft bezeichnet XVERWEIS als verbesserte Version des SVERWEIS. Die Syntax trennt Such- und Rückgabebereich: =XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;[wenn_nicht_gefunden];[Vergleichsmodus];[Suchmodus]). Die Stundenformel aus dem Beispiel lautet damit =XVERWEIS(C5;Stammdaten!$A$2:$A$7;Stammdaten!$E$2:$E$7;0) – ohne Spaltennummer, ohne viertes Argument für die genaue Suche und mit eingebautem Ersatzwert 0. Alle sechs Argumente, Staffeln ohne Sortierung, die Rückgabe mehrerer Spalten und die Suche nach mehreren Kriterien erklärt der eigene Beitrag XVERWEIS in Excel.

Der Suchmodus -1 ist im Dienstplan nützlicher, als er klingt: In einer Änderungsliste, in der jede Schichtzeit-Anpassung mit Datum angehängt wird, findet =XVERWEIS("N";tblAenderungen[Kürzel];tblAenderungen[Beginn];;0;-1) den jüngsten Eintrag für den Nachtdienst. Mit dem SVERWEIS bekäme man immer den ältesten.

Wann bleibt der SVERWEIS die richtige Wahl? Immer dann, wenn eine Datei auch in Excel 2016 oder 2019 rechnen muss. Microsoft weist darauf hin, dass XVERWEIS dort nicht verfügbar ist; eine Arbeitsmappe mit XVERWEIS lässt sich dort zwar öffnen, die Funktion selbst steht in diesen Versionen aber nicht zur Verfügung. In Betrieben mit gemischtem Softwarestand – etwa einem älteren Rechner im Stationszimmer – ist der SVERWEIS mit FALSCH und $-Bezügen oder die Kombination INDEX/VERGLEICH deshalb die robustere Lösung. Wird die Datei ausschließlich in Microsoft 365 bearbeitet, etwa gemeinsam über OneDrive wie in Excel-Dienstplan im Team teilen beschrieben, spricht wenig gegen XVERWEIS.

SVERWEIS in Google Sheets

Google Sheets kennt den SVERWEIS mit derselben Logik und eigenen Argumentnamen: SVERWEIS(Suchschlüssel;Bereich;Index;ist_sortiert). Auch hier gilt ohne viertes Argument die ungefähre Suche; die Google-Hilfe empfiehlt ausdrücklich, FALSCH anzugeben, und nennt für das Abfangen von #NV die Funktion WENNNV. Platzhalter wie * und ? funktionieren nur bei genauer Suche.

Das Gegenstück zum XVERWEIS führt die Google-Hilfe unter dem Namen XLOOKUP mit den Argumenten Suchschlüssel, Suchbereich, Ergebnisbereich, fehlender Wert, Abgleichmodus und Suchmodus; auch dort ist die genaue Suche voreingestellt. Wird eine Excel-Datei mit Nachschlageformeln in Google Sheets importiert, lohnt eine Kontrolle der Bezüge auf andere Tabellenblätter und auf strukturierte Tabellenverweise wie tblSchichten, weil diese nicht in jedem Fall unverändert übernommen werden.

Wo die Nachschlagelogik an ihre Grenze kommt

Der SVERWEIS macht aus Kürzeln Zahlen, aber er prüft nicht, ob die Kombination der Kürzel sinnvoll ist. Ein Frühdienst am Morgen nach einem Nachtdienst ergibt korrekte Stunden – und trotzdem kann die elfstündige Ruhezeit nach § 5 ArbZG nicht eingehalten sein. Solche Prüfungen lassen sich mit Formeln über benachbarte Zellen und bedingter Formatierung nachbauen, ebenso Abweichungen zwischen Soll- und Ist-Stunden. Jede weitere Prüfung ist jedoch eine weitere Formel, die beim Kopieren eines Monatsblatts, beim Einfügen einer Spalte oder beim Import in ein anderes Programm brechen kann. Für einen kleinen, stabilen Plan ist das gut beherrschbar; mit wachsendem Team und häufigen Änderungen wird die Pflege der Formeln selbst zur Aufgabe. Woran sich dieser Punkt festmachen lässt, beschreibt Wann sich der Umstieg auf eine App lohnt.

Weiterlesen

Wie Legende, Dropdown und Stundenformeln zu einem vollständigen Plan zusammenkommen, beschreibt Dienstplan in Excel erstellen. Die Eingabeseite – Kürzel per Datenüberprüfung, mitwachsende Listen und Schichtfarben – steht in Excel Dropdown erstellen. Wer Zuschläge aus den nachgeschlagenen Schichtzeiten ableiten will, findet die Formeln in Nacht-, Sonntags- und Feiertagszuschläge in Excel berechnen. Fertige Arbeitsmappen mit hinterlegter Legende gibt es in der Vorlagenübersicht.