XVERWEIS statt SVERWEIS: Unterschiede, Umstieg, Fallstricke

SVERWEIS (VLOOKUP) ist seit Jahrzehnten die Nachschlagefunktion, die fast jeder Excel-Nutzer kennt – und deren Tücken ebenso bekannt sind: der Spaltenindex, der nach dem Einfügen einer Spalte plötzlich auf die falsche Spalte zeigt, das vergessene FALSCH am Ende, die Suchspalte, die ganz links stehen muss. XVERWEIS (XLOOKUP) räumt mit diesen Punkten auf. Microsoft selbst empfiehlt auf der SVERWEIS-Hilfeseite, die neue Funktion zu verwenden.

Ganz ohne Haken ist der Wechsel trotzdem nicht. Dieser Artikel zeigt die Unterschiede im Detail, übersetzt typische SVERWEIS-Formeln in XVERWEIS und nennt die Stellen, an denen der Umstieg schiefgehen kann.

Wer XVERWEIS nutzen kann

XVERWEIS gibt es laut Microsoft in Excel für Microsoft 365 (Windows und Mac), Excel 2021, Excel 2024 sowie in den Apps für iPad, iPhone und Android. In Excel 2016 und Excel 2019 ist die Funktion nicht verfügbar.

Das ist der wichtigste Punkt bei der Entscheidung. Wenn Sie eine Datei an einen Verein, eine Behörde oder einen Kunden schicken, der noch mit Excel 2016 oder 2019 arbeitet, können XVERWEIS-Formeln dort nicht neu berechnet werden. Spätestens wenn jemand eine solche Formel bearbeitet oder neu eingibt, erscheint #NAME?. Klären Sie vor dem Umbau einer gemeinsam genutzten Datei, welche Versionen im Spiel sind.

Die beiden Syntaxen nebeneinander

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])

Der grundlegende Unterschied: SVERWEIS bekommt einen ganzen Tabellenblock und eine Spaltennummer. XVERWEIS bekommt zwei getrennte Bereiche – wo gesucht wird und was zurückkommt. Daraus folgen fast alle weiteren Unterschiede.

Durchgerechnetes Beispiel: eine Bestellung bepreisen

Beispieldatei: Artikelliste und Bestellung eines fiktiven Bürobedarfshändlers. Die Artikelliste ist als Tabelle mit dem Namen Artikel angelegt, die Preise stehen in Euro. Die Bezeichnung steht absichtlich links von der Artikelnummer, weil das in vielen gewachsenen Listen so ist.

Auf einem zweiten Blatt steht die Bestellung: Artikelnummer in Spalte A, Menge in Spalte B. In Spalte C soll der Einzelpreis erscheinen, in Spalte D die Bezeichnung.

Für den Preis schreiben Sie in C2:

=XVERWEIS(A2;Artikel[ArtNr];Artikel[Preis];"fehlt")

Für die Bezeichnung, die links von der Artikelnummer steht, in D2:

=XVERWEIS(A2;Artikel[ArtNr];Artikel[Bezeichnung];"fehlt")

Mit SVERWEIS wäre die zweite Formel ohne Umbau der Tabelle nicht möglich, weil SVERWEIS laut Microsoft voraussetzt, dass das Suchkriterium in der ersten Spalte des Bereichs steht. Die übliche Notlösung war INDEX mit VERGLEICH.

Tippt jemand versehentlich A-1010 ein, liefert die Formel „fehlt“ statt #NV. Das ist lesbarer, hat aber eine Folge: Der Betrag in Spalte E (=B2*C2) ergibt dann #WERT!, weil Text nicht multipliziert werden kann. Wer lieber eine 0 hätte, schreibt 0 als viertes Argument – und riskiert, dass ein fehlender Artikel unbemerkt mit null Euro in der Summe landet. Nach unserer Einschätzung ist ein sichtbarer Hinweistext in Bestelllisten die bessere Wahl, weil er auffällt.

Ungefähre Suche: die Rabattstaffel

Der Händler gewährt Mengenrabatt nach Bestellwert: ab 0 € kein Rabatt, ab 100 € 3 %, ab 250 € 5 %, ab 500 € 8 %. Die Staffel steht in einer Tabelle Rabatt mit den Spalten Ab und Satz. Gesucht ist die höchste Stufe, die der Bestellwert erreicht oder überschreitet – also exakte Übereinstimmung oder das nächstkleinere Element, Vergleichsmodus −1.

Der Bestellwert steht in E6 (=SUMME(E2:E5)). Die Formel für den Rabattsatz:

=XVERWEIS(E6;Rabatt[Ab];Rabatt[Satz];0;-1)

Für den Bestellwert von 335,20 € liefert die Formel 5 %. Der Rabatt beträgt 16,76 €, der Nettobetrag 318,44 €. Ein Bestellwert von genau 250,00 € bekommt ebenfalls 5 %, einer von 249,99 € nur 3 %.

Der Unterschied zu SVERWEIS liegt im Detail: SVERWEIS mit WAHR verlangt laut Microsoft eine aufsteigend sortierte erste Spalte, sonst kann ein unerwarteter Wert herauskommen. XVERWEIS mit Vergleichsmodus −1 und dem Standard-Suchmodus 1 setzt keine Sortierung voraus. Nur die Binärsuche (Suchmodus 2 oder −2) verlangt sortierte Daten; Microsoft warnt, dass sonst ungültige Ergebnisse zurückgegeben werden.

Umstieg Schritt für Schritt

Eine bestehende SVERWEIS-Formel übersetzen Sie in vier Schritten. Ausgangspunkt sei:

=SVERWEIS(A2;Artikel!$B$2:$D$200;3;FALSCH)

1. Suchspalte bestimmen. Die erste Spalte des SVERWEIS-Bereichs ist die Suchspalte: Artikel!$B$2:$B$200.
2. Rückgabespalte bestimmen. Spaltenindex 3 bedeutet die dritte Spalte ab B, also D: Artikel!$D$2:$D$200. Achten Sie darauf, dass beide Bereiche dieselben Zeilen abdecken.
3. Das vierte Argument übersetzen. FALSCH (genaue Übereinstimmung) entspricht dem Standard von XVERWEIS und entfällt. Stand dort WAHR oder gar nichts, brauchen Sie Vergleichsmodus −1.
4. Fehlerbehandlung verlagern. Ein umschließendes WENNFEHLER(…;"") ersetzen Sie durch das Argument wenn_nicht_gefunden. Das ist genauer, weil WENNFEHLER (IFERROR) auch echte Fehler wie falsche Bezüge verschluckt.

Ergebnis:

=XVERWEIS(A2;Artikel!$B$2:$B$200;Artikel!$D$2:$D$200;"")

Noch robuster wird es, wenn Sie die Artikelliste mit Strg+T als Tabelle formatieren und strukturierte Verweise wie Artikel[Preis] verwenden. Neue Zeilen werden dann automatisch erfasst, und die Formel bleibt lesbar.

Fallstricke beim Wechsel

Such- und Rückgabebereich unterschiedlich groß

Wenn die Suchmatrix B2:B200 umfasst und die Rückgabematrix D2:D199, stimmen die Zeilen nicht überein. XVERWEIS meldet dann einen Fehler (#WERT!). Gefährlicher ist der Fall B2:B200 gegen D3:D201: gleich groß, aber um eine Zeile versetzt. Das liefert kein Fehlerzeichen, sondern stillschweigend den Preis des nächsten Artikels. Strukturierte Verweise auf Tabellenspalten schließen diesen Fehler praktisch aus.

Alte Formeln ohne viertes Argument

Viele SVERWEIS-Formeln wurden ohne viertes Argument geschrieben. Das bedeutet laut Microsoft „ungefähre Übereinstimmung“. Wer solche Formeln eins zu eins übersetzt und das Argument weglässt, bekommt mit XVERWEIS eine exakte Suche – und plötzlich #NV, wo vorher ein (womöglich falscher) Wert stand. Prüfen Sie bei jeder Formel, ob die ungefähre Suche gewollt war, etwa bei einer Staffel, oder ein Versehen.

Überlauf blockiert

Gibt XVERWEIS mehrere Spalten zurück, etwa =XVERWEIS(A2;Artikel[ArtNr];Artikel[[Bezeichnung]:[Preis]]), läuft das Ergebnis in die Nachbarzellen. Stehen dort schon Daten, erscheint #ÜBERLAUF!. Innerhalb einer formatierten Tabelle funktionieren solche überlaufenden Formeln nicht; dort schreiben Sie eine Formel pro Spalte.

Zahlen als Text

Dieses Problem hat XVERWEIS von SVERWEIS geerbt. Steht die Artikelnummer 1001 in der Liste als Zahl und in der Bestellung als Text (etwa nach einem CSV-Import), findet keine der beiden Funktionen einen Treffer. Microsoft rät, Zahlen und Datumswerte nicht als Text zu speichern. Wie Sie solche Spalten beim Import sauber übernehmen, beschreibt der Artikel CSV-Dateien importieren ohne Zahlensalat. Für bestehende Daten hilft =XVERWEIS(TEXT(A2;"0");…) oder umgekehrt =XVERWEIS(WERT(A2);…), je nachdem, auf welcher Seite der Text steht.

Doppelte Schlüssel

Beide Funktionen liefern bei mehreren Treffern standardmäßig den ersten. XVERWEIS kann mit Suchmodus −1 den letzten Treffer liefern, was bei Preislisten mit Historie nützlich ist („letzter eingetragener Preis“). Ob Duplikate gewollt sind, sollte aber vorher geklärt sein; Methoden dafür zeigt der Artikel Duplikate finden und entfernen.

Platzhalter

SVERWEIS mit FALSCH wertet * und ? im Suchtext als Platzhalter aus. XVERWEIS tut das nur mit Vergleichsmodus 2. Eine Formel wie =SVERWEIS("Ordner*";…;FALSCH) braucht nach dem Umstieg also ausdrücklich …;"";2) – sonst sucht XVERWEIS nach einem Eintrag, der wörtlich „Ordner*“ heißt.

Welche Variante wann passt

Für neue Dateien, die nur in Microsoft 365 oder Excel 2021 und neuer geöffnet werden, spricht nach unserer Einschätzung wenig für SVERWEIS. XVERWEIS ist kürzer, sucht standardmäßig exakt und übersteht eingefügte Spalten. Allein das Standardverhalten verhindert einen der häufigsten Fehler in alten Listen: die versehentlich ungefähre Suche, die bei unsortierten Daten falsche Werte liefert, ohne dass eine Fehlermeldung erscheint.

SVERWEIS bleibt sinnvoll, wenn Empfänger mit Excel 2016 oder 2019 arbeiten oder wenn eine Datei in Abläufe eingebunden ist, die Sie nicht vollständig überblicken. Bestehende, funktionierende SVERWEIS-Formeln müssen nicht flächendeckend umgeschrieben werden. Ein guter Zeitpunkt für den Umstieg ist der Moment, in dem Sie eine Formel ohnehin anfassen.

Wer in gemischten Umgebungen arbeitet, kann für Suchen nach links weiterhin die Kombination aus INDEX und VERGLEICH (INDEX/MATCH) verwenden. Sie funktioniert in allen Versionen und ist ebenfalls unempfindlich gegen eingefügte Spalten, verlangt aber mehr Schreibarbeit.

Vor dem Weitergeben einer umgestellten Datei lohnt ein kurzer Abgleich: Decken Such- und Rückgabebereich exakt dieselben Zeilen ab? Ist bei jeder ehemals ungefähren Suche Vergleichsmodus −1 gesetzt? Ist der Hinweistext für „nicht gefunden“ so gewählt, dass nachfolgende Rechnungen nicht stillschweigend mit null weiterlaufen? Und arbeiten alle Empfänger mit einer Excel-Version, die XVERWEIS kennt?