XVERWEIS in Excel: Syntax, Modi und Dienstplan-Beispiele (auch mit mehreren Kriterien)

Was ist XVERWEIS – und wo gibt es die Funktion?

Das „X“ steht dafür, dass die Funktion nicht auf eine Richtung festgelegt ist: Sie durchsucht eine Spalte oder eine Zeile und ersetzt damit sowohl den senkrechten SVERWEIS als auch den waagerechten WVERWEIS. Das englische Original heißt XLOOKUP. Anders als beim SVERWEIS gibt es keine Matrix mit Spaltennummer, sondern zwei getrennte Bereiche: einen, in dem gesucht wird, und einen, aus dem das Ergebnis kommt. Beide müssen nur gleich lang sein; wo sie im Blatt liegen, spielt keine Rolle.

Für eine Arbeitsmappe, die zwischen mehreren Rechnern wandert, ist die Verfügbarkeit die erste Frage. Microsoft führt die Funktion für die aktuellen Versionen und weist ausdrücklich darauf hin, dass sie in Excel 2016 und 2019 fehlt.

Arbeitet im Betrieb noch jemand mit Excel 2019 – etwa am Rechner im Stationszimmer oder im Lager –, gehört in gemeinsam genutzte Pläne weiterhin der SVERWEIS mit FALSCH oder die Kombination INDEX/VERGLEICH. Wie das sauber aufgebaut wird, steht im Beitrag SVERWEIS in Excel. Alles Weitere in diesem Artikel setzt eine Version mit XVERWEIS voraus.

Syntax: die sechs Argumente

Microsoft beschreibt drei Pflicht- und drei optionale Argumente. In deutschsprachigem Excel werden sie durch Semikolon getrennt; ein optionales Argument kann man überspringen, indem man zwei Semikolons hintereinander setzt.

Zwei Voreinstellungen unterscheiden XVERWEIS grundlegend vom SVERWEIS: Die genaue Suche ist Standard, und es gibt keine Spaltennummer, die nach dem Einfügen einer Spalte ins Leere zeigt. Ein vergessenes Argument führt deshalb nicht mehr stillschweigend zu falschen Stunden.

Grundbeispiel: Schichtlegende als Excel-Tabelle

Am übersichtlichsten arbeitet XVERWEIS mit einer formatierten Excel-Tabelle, weil sich Spalten dann über ihren Namen ansprechen lassen. Die Schichtlegende steht auf einem Blatt Stammdaten und wird mit Strg+T zur Tabelle tblSchichten:

Im Plan stehen die Kürzel in C5:AG14. Die Stunden je Zelle holt =XVERWEIS(C5;tblSchichten[Kürzel];tblSchichten[Netto];0). Die abschließende 0 ist das Argument wenn_nicht_gefunden: Leere Tage und unbekannte Kürzel ergeben 0 statt #NV, und Zeilensummen rechnen ohne zusätzliches WENNFEHLER weiter. Strukturierte Verweise wie tblSchichten[Netto] brauchen keine Dollarzeichen; sie bleiben beim Kopieren nach rechts und unten stehen, während C5 mitwandert. Kommt eine neue Schicht hinzu, wächst die Tabelle mit, ohne dass eine Formel angepasst werden muss.

Eine Einschränkung hat die bequeme 0: Sie unterscheidet nicht zwischen einem leeren Tag und einem Tippfehler. Wer das sehen will, setzt statt 0 einen Text wie "?" und fängt leere Zellen vorher ab: =WENN(C5="";0;XVERWEIS(C5;tblSchichten[Kürzel];tblSchichten[Netto];"?")). Ein Fragezeichen in der Auswertung lässt sich per bedingter Formatierung rot einfärben. Beachten sollte man außerdem, dass wenn_nicht_gefunden nur greift, wenn kein Treffer existiert – steht in der Rückgabespalte selbst ein Fehlerwert, kommt dieser unverändert zurück.

Vergleichsmodus: genau, Staffel oder Platzhalter

Das fünfte Argument entscheidet, was als Treffer gilt. Für Kürzel, Namen und Personalnummern bleibt es bei 0. Die übrigen Modi sind für Staffeln und Textmuster gedacht.

-1: die Pausenstaffel nach § 4 ArbZG

Nach § 4 Arbeitszeitgesetz ist die Arbeit bei mehr als sechs bis zu neun Stunden durch mindestens 30 Minuten, bei mehr als neun Stunden durch mindestens 45 Minuten Ruhepause zu unterbrechen. Als Nachschlagetabelle mit Untergrenzen in Minuten steht in H2:H4 0, 361, 541 und in I2:I4 0, 30, 45. Die Formel =XVERWEIS(D5;$H$2:$H$4;$I$2:$I$4;;-1) liefert für 480 Minuten Arbeitszeit den Wert 30, für 360 Minuten den Wert 0 und für 600 Minuten den Wert 45. Die Untergrenze 361 statt 360 bildet das „mehr als sechs Stunden“ des Gesetzes ab.

Der Unterschied zum SVERWEIS mit WAHR: Die Staffel muss nicht sortiert sein. Microsoft setzt eine sortierte Suchmatrix nur für die binäre Suche (Suchmodus 2 und -2) voraus; mit dem Standard-Suchmodus 1 prüft XVERWEIS alle Einträge. Wer die Staffel später um eine Zeile ergänzt, kann sie also unten anhängen. Wie die Pausenprüfung in eine vollständige Zeiterfassung eingebaut wird, zeigt Arbeitszeiterfassung in Excel.

1: Besetzung nach Obergrenzen

Umgekehrt arbeiten Staffeln, die mit Obergrenzen formuliert sind – etwa eine betriebliche Faustregel für den Service: bis 40 erwartete Gäste zwei Kräfte, bis 80 drei, bis 120 vier, bis 160 fünf. Mit den Obergrenzen in H8:H11 und der Kräftezahl in I8:I11 liefert =XVERWEIS(F2;$H$8:$H$11;$I$8:$I$11;"Sonderplanung";1) für 75 Gäste die nächstgrößere Stufe 80 und damit drei Kräfte. Liegt die Prognose über 160, gibt es kein größeres Element, und die Formel zeigt „Sonderplanung“. Die Zahlen sind Beispielwerte; wie sich der Bedarf systematisch herleiten lässt, beschreibt der Personalbedarf-Rechner.

2: Platzhalter

Mit Vergleichsmodus 2 steht * für beliebig viele Zeichen und ? für genau eines. =XVERWEIS("Nacht*";tblSchichten[Bezeichnung];tblSchichten[Kürzel];"–";2) findet das Kürzel N, auch wenn die Bezeichnung später in „Nachtdienst Station 2“ geändert wird. Soll ein Sternchen oder Fragezeichen wörtlich gesucht werden, steht davor eine Tilde: "~*". Im Modus 0 haben diese Zeichen dagegen keine Sonderbedeutung – das ist eine Abweichung zum SVERWEIS, bei dem Platzhalter in der genauen Suche wirken.

Suchmodus: vom letzten Eintrag aus suchen

Das sechste Argument legt die Richtung fest. Standard ist 1, also von oben nach unten; der erste Treffer gewinnt. Mit -1 beginnt die Suche beim letzten Element. Das ist immer dann richtig, wenn eine Liste chronologisch nach unten wächst und der jüngste Eintrag gefragt ist.

Ein typischer Fall ist die Ruhezeit. In einer Zeiterfassungstabelle tblZeiten mit den Spalten Datum, Name, Beginn und Ende hängt jeder Arbeitstag eine Zeile an. Das Ende des letzten erfassten Dienstes einer Person holt =XVERWEIS(B5;tblZeiten[Name];tblZeiten[Ende];"kein Eintrag";0;-1). Mit dem Datum aus derselben Zeile lässt sich dann prüfen, ob bis zum nächsten geplanten Beginn die elf Stunden Ruhezeit nach § 5 ArbZG liegen. Mit Suchmodus 1 bekäme man den ersten Dienst des Jahres.

Die binären Modi 2 und -2 setzen eine auf- beziehungsweise absteigend sortierte Suchmatrix voraus. Ist sie nicht sortiert, sind die Ergebnisse laut Microsoft ungültig – und zwar ohne Fehlermeldung. Bei Listen in der Größe eines Dienstplans bringt die binäre Suche keinen spürbaren Vorteil; sie sollte dort nicht verwendet werden.

Mehrere Spalten mit einer Formel zurückgeben

Die Rückgabematrix darf mehrere Spalten umfassen. Excel schreibt das Ergebnis dann in die Nachbarzellen – das Ergebnis „läuft über“. In der Personaltabelle tblPersonal mit Nr, Name, Bereich und Wochenstunden holt eine einzige Formel in B5 alle drei Angaben zu der Personalnummer in A5:

=XVERWEIS($A5;tblPersonal[Nr];tblPersonal[[Name]:[Wochenstunden]];"Nr. prüfen")

Name, Bereich und Wochenstunden erscheinen in B5, C5 und D5. Die Formel wird nur nach unten kopiert, nicht nach rechts. Zwei Bedingungen müssen dafür erfüllt sein, und beide sind häufige Fehlerquellen:

• Die Zielzellen müssen leer sein. Steht in C5 oder D5 noch ein Wert, zeigt B5 #ÜBERLAUF!. Das gilt auch für verbundene Zellen im Überlaufbereich.
• Die Formel darf nicht in einer Excel-Tabelle stehen. Microsoft unterstützt überlaufende Formeln innerhalb von Tabellen nicht. Der Plan selbst bleibt deshalb ein normaler Bereich; nur die Stammdaten sind als Tabelle formatiert.

Eine Personalnummer als Suchschlüssel ist robuster als ein Name. Zu beachten ist der Datentyp: Steht die Nummer im Plan als Zahl und in der Personalliste als Text, etwa nach einem Import, findet XVERWEIS nichts.

Zweidimensionale Suche: Soll-Besetzung aus einer Matrix

Viele Betriebe führen ihre Mindestbesetzung als Matrix: Schichten in den Zeilen, Wochentage in den Spalten. Ein verschachtelter XVERWEIS sucht in beide Richtungen. Microsoft zeigt diese Konstruktion als Ersatz für INDEX mit zwei VERGLEICH.

Mit dem Kürzel in K2 und dem Wochentag in K3 lautet die Formel =XVERWEIS(K2;$B$6:$B$8;XVERWEIS(K3;$C$5:$I$5;$C$6:$I$8)). Der innere XVERWEIS sucht den Wochentag in der Kopfzeile und gibt die ganze zugehörige Spalte zurück, für „Sa“ also H6:H8. Der äußere sucht darin die Zeile der Schicht – für F und Sa ergibt sich 4. Im Plan lässt sich so unter jedem Tag die Soll-Besetzung neben die gezählte Ist-Besetzung stellen; wie die Ist-Zahl entsteht, zeigt ZÄHLENWENN in Excel. Steht in der Kopfzeile des Plans ein echtes Datum, liefert TEXT(C$4;"TTT") das passende Kürzel „Mo“ bis „So“ als Suchkriterium.

XVERWEIS mit mehreren Kriterien

Für eine Suche nach zwei Bedingungen gleichzeitig – etwa „Wer hat am 14. November Nachtdienst?“ – kennt XVERWEIS kein eigenes Argument. Eine verbreitete Technik nutzt aus, dass Excel Vergleiche über ganze Bereiche als Arrays aus WAHR und FALSCH auswertet. In einer Schichtliste tblPlan mit den Spalten Datum, Schicht und Name lautet die Formel:

=XVERWEIS(1;(tblPlan[Datum]=K2)*(tblPlan[Schicht]="N");tblPlan[Name];"unbesetzt")

Die Multiplikation verwandelt WAHR in 1 und FALSCH in 0; nur in Zeilen, in denen beide Bedingungen zutreffen, entsteht eine 1. Genau diese 1 sucht XVERWEIS. Weitere Bedingungen werden mit einem weiteren *( … ) angehängt. Zwei Grenzen gelten: Die Formel liefert nur den ersten Treffer, und alle Bereiche müssen gleich lang sein. Sollen alle eingeteilten Personen erscheinen, ist die Funktion FILTER das passende Werkzeug. Für Summen nach mehreren Bedingungen – Stunden je Person und Monat – ist SUMMEWENNS übersichtlicher als jede Nachschlageformel.

Bereiche zurückgeben: Stunden zwischen zwei Daten summieren

XVERWEIS gibt nicht nur Werte, sondern Zellbezüge zurück. Zwei XVERWEIS-Formeln lassen sich deshalb mit dem Bereichsoperator : verbinden. Steht in Zeile 4 des Plans das Datum je Tag und in Zeile 10 die Tagesstunden einer Person, summiert =SUMME(XVERWEIS(K2;$C$4:$AG$4;$C10:$AG10):XVERWEIS(K3;$C$4:$AG$4;$C10:$AG10)) die Stunden vom Datum in K2 bis zum Datum in K3 – etwa für eine Abrechnung zur Monatsmitte. Microsoft führt diese Konstruktion als eigenes Beispiel. Liegen die Stunden ohnehin als Liste mit Datumsspalte vor, ist SUMMEWENNS mit zwei Datumsgrenzen die gängigere Lösung.

XVERGLEICH: die Position statt des Werts

Zusammen mit XVERWEIS hat Microsoft XVERGLEICH eingeführt. Die Syntax lautet =XVERGLEICH(Suchkriterium;Suchmatrix;[Vergleichsmodus];[Suchmodus]) mit denselben Modi; zurück kommt die relative Position des Treffers. Im Dienstplan ist das nützlich, wenn eine Spaltennummer gebraucht wird: =XVERGLEICH(HEUTE();$C$4:$AG$4) liefert, in der wievielten Spalte des Monatsplans der heutige Tag steht, und lässt sich mit INDEX für eine „Heute im Dienst“-Übersicht kombinieren. Für das reine Nachschlagen eines Werts ist XVERWEIS kürzer.

Typische Fehler und ihre Ursachen

Bei #NAME? in einer älteren Version hilft es nicht, die Formel neu einzutippen: Die Funktion ist dort schlicht nicht vorhanden. Für Pläne, die im ganzen Team identisch rechnen müssen, ist ein einheitlicher Softwarestand deshalb wichtiger als die eleganteste Formel. Weitere Konstruktionsfehler dieser Art sammelt der Beitrag Die häufigsten Fehler in Excel-Dienstplänen.

XVERWEIS in Google Tabellen und LibreOffice

Google Tabellen führt die Funktion in der deutschen Hilfe unter dem englischen Namen XLOOKUP mit den Argumenten Suchschlüssel, Suchbereich, Ergebnisbereich, fehlender_Wert, Abgleichmodus und Suchmodus. Die Modi entsprechen denen in Excel, Standard sind ebenfalls 0 und 1. Wird eine Excel-Datei mit XVERWEIS importiert, lohnt eine Kontrolle der strukturierten Verweise wie tblSchichten[Netto], weil sie nicht in jedem Fall unverändert übernommen werden.

LibreOffice Calc hat XLOOKUP und XMATCH mit Version 24.8 im August 2024 erhalten. Für ältere Calc-Installationen gilt dasselbe wie für Excel 2019: Dort bleibt der SVERWEIS die kompatible Wahl.

Wo die Formel an ihre Grenze kommt

XVERWEIS macht Nachschlagelogik robuster, aber nicht klüger. Die Formel findet die Soll-Besetzung für Samstag und das letzte Dienstende einer Person; ob der Plan als Ganzes Ruhezeiten, Pausen und Besetzung einhält, ergibt sich erst aus vielen solcher Formeln, die zusammenpassen müssen. Jede davon kann beim Kopieren eines Monatsblatts, beim Öffnen in einer älteren Version oder durch einen überschriebenen Überlaufbereich brechen. Für einen kleinen, stabilen Plan ist das gut beherrschbar. Woran man erkennt, dass die Pflege der Formeln selbst zur Hauptaufgabe wird, beschreibt Wann sich der Umstieg auf eine App lohnt.

Weiterlesen

Die Grundlagen der Nachschlagefunktionen, die Pausenstaffel mit WAHR und die Kombination INDEX/VERGLEICH für ältere Versionen erklärt SVERWEIS in Excel. Wie Legende, Dropdown und Stundenformeln zu einem vollständigen Plan zusammenkommen, zeigt Dienstplan in Excel erstellen. Wer mit Kürzeln rechnet und dabei Bedingungen prüfen will, findet die Formeln in WENN-Funktion in Excel. Fertige Arbeitsmappen mit hinterlegter Legende gibt es in der Vorlagenübersicht.