XVERWEIS erklärt: Werte zuverlässig nachschlagen, fehlende Einträge ehrlich behandeln und jedes Muster für ältere Excel-Versionen nach INDEX/VERGLEICH übersetzen

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was ist der Unterschied zwischen XVERWEIS und SVERWEIS und warum sollte ich XVERWEIS statt SVERWEIS verwenden?

A: Mit XVERWEIS kannst du einen Bereich durchsuchen und den passenden Wert aus einem anderen Bereich ausgeben. Im Gegensatz zu SVERWEIS kann er auch nach links suchen, er bricht nicht beim Einfügen einer Spalte und er sucht standardmäßig exakt statt näherungsweise. Außerdem hat er ein eigenes Argument für den Fall wenn_nicht_gefunden. Jedes Muster in diesem Artikel ist als XVERWEIS sicherer als als SVERWEIS.

Q: Welche sechs Argumente hat XVERWEIS?

A: Suchkriterium, Suchmatrix, Rückgabematrix, wenn_nicht_gefunden, Vergleichsmodus und Suchmodus. Die ersten drei sind erforderlich, die letzten drei optional. Die meisten Anwender kommen nie über das dritte hinaus und genau dort beginnt das nützliche Verhalten.

Q: Was mache ich, wenn eine Suche nichts findet?

A: Über das vierte Argument wenn_nicht_gefunden. Es ersetzt die übliche Gewohnheit, die ganze Formel in "WENNFEHLER" zu verpacken, und ist sicherer. Es fängt nur den Fall des fehlenden Werts ab und lässt jeden anderen Fehler sichtbar.

Q: Wie schlage ich einen Wert in einer Staffel nach? Beispielsweise in einer Steuer-, Rabatt- oder Kilometerstaffel.

A: Benutze den Vergleichsmodus -1 (genaues Ergebnis oder nächstkleinerer Wert) und suche in einer Tabelle mit den Untergrenzen der Stufen. Das ist der richtige Ersatz für SVERWEIS(…;WAHR), anders als dieser liefert XVERWEIS bei unsortierten Daten kein falsches Ergebnis.

Q: Kann XVERWEIS mehr als eine Zelle zurückgeben?

A: Ja. Wenn die Rückgabematrix zweidimensional ist, gibt XVERWEIS die gesamte Trefferzeile zurück und lässt sie überlaufen. Eine Formel ersetzt fünf. Er kann auch einen Bezug herstellen. Mit dieser Funktion kann man zwei XVERWEIS-Aufrufe machen. Damit kann man den Anfang und das Ende eines Bereichs bestimmen.

Q: Wie suche ich gleichzeitig nach Zeile und Spalte?

A: Kombiniere einen XVERWEIS mit einem anderen. Der innere Aufruf wählt die Spalte, der äußere die Zeile. Die alte Formel INDEX(Bereich; VERGLEICH(...); VERGLEICH(...)) wird ersetzt.

Q: Ich habe Excel 2016 oder 2019, dort gibt es keinen XVERWEIS. Was verwende ich?

A: Nutze INDEX zusammen mit VERGLEICH. Teil 7 zeigt die genaue Entsprechung für jedes Muster dieses Artikels, auch für die Staffelsuche und die zweidimensionale Suche.

Nahezu jede wichtige Arbeitsmappe enthält eine Nachschlagefunktion. Eine Preisliste beispielsweise in einer Auftragstabelle. Eine Personalnummer steht neben einem Stundensatz. Eine Kostenstelle an einer Abteilungsbezeichnung. Dies ist die häufigste Formelaufgabe in Excel und war rund fünfundzwanzig Jahre lang auch die häufigste Ursache für stillschweigend falsche Ergebnisse.

Den größten Anteil daran trägt SVERWEIS. Er zählt Spalten, statt sie zu benennen. Das Einfügen einer Spalte macht ihn ohne Warnung unbrauchbar. Sein viertes Argument steht standardmäßig auf näherungsweiser Suche, sodass das Weglassen dieses Arguments einen plausiblen Nachbarwert statt einer Fehlermeldung liefert. Er kann nicht links von der Suchspalte nachsehen. Und es gibt keine Möglichkeit, auszudrücken, was passieren soll, wenn nichts gefunden wird.

XVERWEIS behebt alle vier Punkte. Dieser Artikel behandelt die vollständige Syntax, die Argumente, zu denen die meisten nie vordringen, die Muster, für die sich das gründliche Lernen lohnt, sowie eine komplette Übersetzungstabelle zurück zu INDEX/VERGLEICH für all jene, die noch mit Excel 2016 oder 2019 arbeiten: Im deutschen Mittelstand ist dies nach wie vor ein erheblicher Anteil der Arbeitsplätze.

Teil 1: Die vier Probleme, die XVERWEIS löst

Betrachten wir eine Provisionstabelle auf einem Blatt Provision, formatiert als Excel-Tabelle mit den Spalten ID, Name, Abteilung und Provisonsatz.

Die klassische Formel:

=SVERWEIS(A2;Provision[#Alle];4;FALSCH)

Das funktioniert, bis einer dieser Fälle eintritt:

Das Problem mit dem Spaltenindex verdient besondere Betonung, weil es lautlos scheitert. Ein #BEZUG!-Fehler sagt dir, dass etwas kaputtgegangen ist. Eine Formel, die nun die Spalte Abteilung statt Provisionssatz liest, sagt dir gar nichts. Sie liefert Text, wo eine Zahl stehen sollte, oder schlimmer: eine andere Zahl, die legitim aussieht. Das ist dieselbe Fehlerklasse wie in Fehlerbehandlung in Excel-Formeln: Die gefährlichen Fehler sind die, die nie erscheinen.

Teil 2: Die vollständige Syntax

=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;[wenn_nicht_gefunden];[Vergleichsmodus];[Suchmodus])

Die Abbildung zeigt alle sechs Argumente und darunter dieselbe Suche an einer dreispaltigen Tabelle: Der Wert aus A2 wird in der Suchmatrix gefunden, und die dazu passende Zelle der Rückgabematrix kommt als Ergebnis zurück.

Die einfachste Form:

=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz])

Laut gelesen heißt das: Finde den Wert aus A2 in der Spalte ID und gib mir den passenden Wert aus der Spalte Provisionssatz. Keine Spaltennummer, keine Sortiervoraussetzung, kein viertes Argument, an das man denken muss. Fügt morgen jemand drei Spalten in die Mitte der Tabelle ein, funktioniert die Formel weiterhin, weil die strukturierten Verweise mit den Spalten wandern. Das ist einer von mehreren Gründen, jeden Datenbestand als Tabelle zu formatieren. Siehe Warum jeder Datensatz eine Excel-Tabelle sein sollte.

Die Suche nach links braucht keine Sonderbehandlung:

=XVERWEIS(A2;Provision[Provisionssatz];Provision[Name])

Die beiden Bereiche sind schlicht Argumente. Keiner muss links oder rechts vom anderen liegen. Sie müssen nur gleich lang sein, und sind sie es nicht, liefert XVERWEIS ein #WERT! statt zu raten.

Teil 3: wenn_nicht_gefunden, das Argument, das WENNFEHLER ersetzt

Die meisten Suchformeln in freier Wildbahn sehen so aus:

=WENNFEHLER(XVERWEIS(A2;Provision[ID];Provision[Provisionssatz]);0)

Das ist eine aus SVERWEIS-Zeiten übernommene Gewohnheit, die dort keine Alternative hatte. In XVERWEIS sollte sie nicht fortgeführt werden, denn das vierte Argument erledigt dieselbe Aufgabe wesentlich präziser:

=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];0)

Der Unterschied ist nicht kosmetisch. WENNFEHLER fängt jeden Fehler ab, den die Formel erzeugen kann. wenn_nicht_gefunden fängt genau eine Bedingung ab: Das Suchkriterium war in der Suchmatrix nicht vorhanden. Alles andere, ungleich lange Bereiche mit #WERT!, eine gelöschte Spalte mit #BEZUG!, ein Tippfehler mit #NAME?, bleibt als sichtbarer Fehler stehen, den Sie finden und beheben können.

Ein Beispiel macht es greifbar: Angenommen, die Spalte Provisionssatz enthält einen aus einem PDF eingefügten Wert, der als Text gespeichert ist. In der WENNFEHLER-Variante wird dieser Datensatz stillschweigend zu 0, und die Lohnsumme ist zu niedrig, ohne dass irgendetwas darauf hinweist. In der Variante mit wenn_nicht_gefunden gelangt das eigentliche Problem weiterhin an die Oberfläche.

Sinnvolle Werte für das vierte Argument:

=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];"")               ' leere Zelle im fertigen Bericht
=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];0)                ' Null, wenn Null wirklich korrekt ist
=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];"nicht gefunden") ' ausdrückliche Kennzeichnung zur Prüfung
=XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];NV())             ' #NV bewusst behalten, z. B. für Diagramme

Der letzte Fall lohnt sich zu kennen. Diagramme ignorieren #NV, zeichnen Nullen aber mit. Wenn du fehlende Punkte durch 0 ersetzt, fällt deine Linie auf die Achse. Lass einfach #NV stehen, dann sieht man, dass die Linie eine Lücke hat.

Eine Warnung: Eine leere Trefferzelle ist nicht dasselbe wie ein fehlender Treffer. Existiert die ID, ist deine Provisionszelle aber leer, liefert XVERWEIS eine 0 und nicht den Wert aus wenn_nicht_gefunden, denn der Datensatz wurde ja gefunden. Um beide Fälle zu unterscheiden, prüfe ausdrücklich:

=LET(
    prov; XVERWEIS(A2;Provision[ID];Provision[Provisionssatz];"kein Datensatz");
    WENN(prov=0;"Provisionssatz fehlt";prov)
)

LET verhindert, dass die Suche zweimal läuft. Siehe LET: übersichtlichere und schnellere Formeln schreiben.

Teil 4: Vergleichsmodus, Staffelsuche, aber sicher

Beim fünften Argument verdient sich XVERWEIS seinen Platz in ernsthaften Finanzmodellen.

Exakt (0, der Standard)

Nichts einzustellen. Das ist es, was du für IDs, Kontonummern, Artikelnummern und Namen brauchst.

Exakt oder nächstkleiner (-1), die Staffelsuche

Das ist das Muster für Steuerstufen, Rabattstaffeln, Mengenrabatte, Provisionsstufen, Gewichtstarife und Kilometersätze. Du legst die Untergrenze jeder Stufe fest und lässt Excel die Stufe finden, in die der Wert fällt.

Eine Rabatttabelle auf einem Blatt Stufen:

=XVERWEIS(B2;Stufen[Schwelle];Stufen[Rabatt];;-1)

Ein Auftragswert von 7.400 findet keinen exakten Treffer, also nimmt XVERWEIS die nächstkleinere Schwelle — 5.000 — und gibt 5 % zurück. Schau dir mal das vierte Argument an. Das ist ausgelassen, erkennbar an den zwei Semikola: wenn_nicht_gefunden bleibt leer, damit ein Wert unterhalb der niedrigsten Schwelle weiterhin ein sichtbares #NV erzeugt statt einer stillen Null.

Der entscheidende Unterschied zu SVERWEIS(...;WAHR): Dessen Näherungsmodus setzt voraus, dass die Tabelle aufsteigend sortiert ist, und liefert andernfalls Unsinn, ohne jeden Fehler. XVERWEIS mit Vergleichsmodus = -1 und dem Standard-Suchmodus = 1 durchläuft den Bereich ordentlich und benötigt keine sortierten Daten. Das ist der Unterschied zwischen einer Regel, die durchgesetzt wird, und einer, auf die man bloß hofft.

Exakt oder nächstgrößer (1)

Das Spiegelbild. Wenn du eine Stufe über deiner Obergrenze definierst, brauchst du eine Liefer-SLA, eine „bis einschließlich"-Preistabelle oder eine Kapazitätsstufe.

=XVERWEIS(B2;Grenzen[BisZu];Grenzen[Preis];;1)

Platzhaltersuche (2)

Aktiviert * (beliebig viele Zeichen), ? (ein beliebiges Zeichen) und ~ (maskiert das folgende Zeichen).

=XVERWEIS("*GmbH";Kunden[Name];Kunden[ID];"kein Treffer";2)

Nützlich für unsaubere importierte Namensspalten, aber eher ein Diagnosewerkzeug als eine Produktivformel: Platzhalter liefern den ersten Treffer, und „der erste“ ist in einer Kundenliste selten eine sinnvolle Größe. Wenn du dich darauf stützt, liegt die eigentliche Lösung meistens weiter oben. Siehe Wie man schmutzige Datenbestände bereinigt.

Teil 5: Suchmodus, Richtung und Geschwindigkeit

Das sechste Argument steuert, wie der Bereich durchlaufen wird.

Der Modus -1 ist der, den man sich merken sollte. In einem nach Datum sortierten Vorgangsprotokoll mit mehreren Zeilen je Kunde ist die letzte Trefferzeile in der Regel die aktuelle:

=XVERWEIS(A2;Protokoll[Kunde];Protokoll[Status];"keine Historie";0;-1)

Das liefert den aktuellen Status des Kunden, nicht den ältesten. Vor XVERWEIS brauchte man dafür entweder eine sortierte Hilfsspalte oder eine Matrixformel, die die meisten Kolleginnen und Kollegen nicht pflegen konnten.

Die Binärmodi sind eine Leistungsoption, keine Korrektheitsoption. Sie setzen tatsächlich sortierte Daten voraus und scheitern lautlos, wenn diese es nicht sind. Damit kehrt genau das Risiko von SVERWEIS(...;WAHR) zurück, das XVERWEIS beseitigen sollte. Nutze sie am besten nur, wenn du in einem Bereich mit vielen tausend Zeilen die Sortierung kontrollieren willst. Und dokumentiere die Voraussetzung im Blatt.

Teil 6: Ganze Zeilen zurückgeben und Bezüge zurückgeben

Eine Formel statt fünf

Ist die Rückgabematrix zweidimensional, gibt XVERWEIS die gesamte Trefferzeile zurück und lässt sie in die Nachbarzellen überlaufen:

=XVERWEIS(A2;Provision[ID];Provision[[Name]:[Provisionsatz]])

Das liefert Name, Abteilung und Provisionsatz in einem Zug als Überlaufbereich. Statt drei oder fünf getrennter Suchformeln, die jeweils erneut die ID-Spalte durchlaufen, hast du eine einzige. Sie rechnet schneller, und es gibt nur eine Formel zu pflegen. Das Verhalten folgt denselben Überlaufregeln wie in Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ beschrieben.

Wenn du die Spalten anders ordnen möchtest oder nur einige davon, kannst du die Spaltenauswahl anpassen. Dafür gibt's die Funktion SPALTENWAHL.

=SPALTENWAHL(XVERWEIS(A2;Provision[ID];Provision[[Name]:[Provisionsatz]]);3;1)

XVERWEIS gibt einen Bezug zurück, nicht nur einen Wert

Das ist die am wenigsten bekannte und wirkungsvollste Eigenschaft der Funktion. Weil XVERWEIS einen echten Zellbezug zurückgibt, können zwei Aufrufe die beiden Enden eines Bereichs bestimmen:

=SUMME(XVERWEIS("März";Monate;Beträge):XVERWEIS("August";Monate;Beträge))

Das summiert alles von der März-Zelle bis einschließlich der August-Zelle, ein wirklich dynamischer Bereich, definiert durch zwei Beschriftungen statt durch zwei fest verdrahtete Adressen. Wenn du die Monatsnamen in den Steuerzellen änderst, wandert auch der summierte Bereich mit.

Das ist der ehrliche Ersatz für die BEREICH.VERSCHIEBEN- und INDIREKT-Konstruktionen älterer Arbeitsmappen. Beide Funktionen sind volatil: Sie erzwingen bei jeder Änderung irgendwo in der Mappe eine Neuberechnung von allem, was auf ihnen aufbaut. XVERWEIS ist nicht volatil. In einem Modell nennenswerter Größe ist das Ersetzen volatiler Bereichsbildung durch dieses Muster einer der größten Geschwindigkeitsgewinne überhaupt. Siehe Volatile Funktionen: was sie sind und wann man sie vermeiden sollte.

Kombiniert mit Zellbezügen für die Endpunkte:

=SUMME(XVERWEIS($B$1;Monate;Beträge):XVERWEIS($B$2;Monate;Beträge))

Sind B1 und B2 Dropdowns, entsteht daraus eine Zeitraumauswahl ohne VBA und ohne Hilfsspalten. Der Aufbau der Dropdowns ist in Datenüberprüfung, Dropdown-Listen und abhängige Listen beschrieben.

Teil 7: Zweidimensionale Suche, Zeile und Spalte zugleich

Wenn du eine Matrix mit Monatswerten hast, Produkte in der linken Spalte und Monate in der Kopfzeile, brauchst du oft die Zelle, in der sich ein gewähltes Produkt und ein gewählter Monat treffen.

=XVERWEIS(B1;Produktnamen;XVERWEIS(B2;Kopfzeilen;Datenmatrix))

Lies es von der inneren zur äußeren Funktion. Der innere XVERWEIS checkt die Kopfzeile und liefert die passende Spalte aus der Matrix. Der äußere XVERWEIS durchsucht dann die Produktnamen und gibt die passende Zeile dieser Spalte zurück, also eine einzelne Zelle.

Das ist lesbarer als die traditionelle Konstruktion INDEX(Matrix;VERGLEICH(...);VERGLEICH(...)), weil jeder Schritt seine eigenen Bereiche benennt, statt nackte Positionsnummern zu erzeugen. Soll es auch in sechs Monaten für eine Kollegin nachvollziehbar sein, verpacke es in eine LET Funktion:

=LET(
    produkt; B1;
    monat;   B2;
    spalte;  XVERWEIS(monat;Kopfzeilen;Datenmatrix;"unbekannter Monat");
    XVERWEIS(produkt;Produktnamen;spalte;"unbekanntes Produkt")
)

Jeder Fehlerfall hat jetzt seine eigene Meldung, sodass ein falsches Ergebnis dir sagt, welche der beiden Auswahlen ungültig war.

Teil 8: Die INDEX/VERGLEICH-Alternative für Excel 2016 und 2019

XVERWEIS setzt Excel 365 oder Excel 2021 voraus. In Excel 2016 und 2019 existiert er nicht, und eine Arbeitsmappe mit dieser Funktion zeigt dort _xlfn.XLOOKUP und einen #NAME?-Fehler. Wenn deine Datei von jemandem mit Dauerlizenz geöffnet wird, brauchst du die ältere Konstruktion. Das ist im deutschen Mittelstand und in Behörden nach wie vor üblich.

Das Grundmuster:

=INDEX(Rückgabebereich;VERGLEICH(Suchkriterium;Suchbereich;0))

VERGLEICH ermittelt die Position des Wertes, INDEX gibt das Element an dieser Position zurück. Gemeinsam leisten sie alles, was SVERWEIS kann, in beide Richtungen und ohne Spaltennummern.

Vollständige Übersetzungstabelle:

Zwei Zeilen dieser Tabelle tragen eine Warnung, und sie sind der Grund, zu migrieren, sobald es geht. Die Staffelsuchen mit VERGLEICH-Typ 1 und -1 setzen sortierte Daten voraus und liefern bei gebrochener Sortierung lautlos ein falsches Ergebnis. XVERWEIS hat diese Falle beseitigt; INDEX/VERGLEICH nicht.

Und schau dir auch die zweite Zeile an. Mit INDEX und VERGLEICH wird die Fehlerbehandlung wieder zu einer äußeren Hülle. Verwende dort WENNNV und nicht WENNFEHLER, wie im Fehlerbehandlungsartikel erklärt.

XVERGLEICH sei hier ebenfalls erwähnt. Er ist das modernisierte VERGLEICH, dieselbe Verfügbarkeit ab 365/2021, mit den verbesserten Argumenten Vergleichsmodus und Suchmodus, und gibt eine Position statt eines Wertes zurück. Du kannst ihn verwenden, wenn du die Position selbst brauchst, zum Beispiel um INDEX über mehrere parallele Bereiche zu speisen oder um zu prüfen, ob ein Wert überhaupt existiert.

Teil 9: Häufige Fehlerbilder

Die erste Zeile macht den weit überwiegenden Teil echter Supportanfragen aus. Ein Suchkriterium "4711 " und ein Tabelleneintrag "4711" sind unterschiedliche Zeichenketten, und Excel hat recht, wenn es sagt, es habe nichts gefunden. Ebenso passt die Zahl 4711 nicht zum Text "4711". Beide Probleme gehören in die Datenbereinigungsschicht und nicht in eine immer defensivere Formel. Der Bearbeitungsansatz ist in Die vier Ebenen einer gut gebauten Arbeitsmappe beschrieben.

Teil 10: Versionsverträglichkeit

Wenn du Dateien erstellst, die du auf einem anderen Rechner speicherst, lege die Zielversion fest und schreibe die gesamte Arbeitsmappe dafür. Eine gemischte Datei, in der manche Blätter modern sind und andere alt, ist am schwersten zu pflegen. Die Unterschiede über den gesamten Funktionsumfang behandelt Excel 365 im Vergleich zu älteren Excel-Versionen.

Zusammenfassung

XVERWEIS ist nicht bloß ein aufgeräumter SVERWEIS. Die ersten drei Argumente ersetzen ihn; die letzten drei können Dinge, die SVERWEIS nie konnte.

• Nutze die exakte Standardsuche für IDs, Nummern und Namen. Und denke nicht weiter darüber nach.
• Nutze das vierte Argument wenn_nicht_gefunden statt die Formel in WENNFEHLER zu verpacken. Fehlende Daten werden behandelt, kaputte Formeln bleiben sichtbar.
• Nutze Vergleichsmodus = -1 für jede Steuerstufe, Rabattstaffel und Tarifstufe. Es ist der sichere Ersatz für SVERWEIS(...;WAHR).
• Nutze Suchmodus = -1, um den jüngsten Datensatz aus einem Protokoll zu holen.
• Gebe ganze Zeilen zurück, statt vier parallele Suchformeln zu schreiben.
• Nutze zwei XVERWEIS-Aufrufe als Bereichsenden, um volatile BEREICH.VERSCHIEBEN- und INDIREKT-Konstruktionen zu ersetzen.
• Unter Excel 2016 oder 2019 nehme INDEX/VERGLEICH und denken daran, dass dessen Staffelmodi sortierte Daten voraussetzen und lautlos scheitern, wenn diese Annahme bricht.

Das zugrunde liegende Prinzip ist dasselbe, das sich durch alle Artikel dieser Reihe zieht. Eine Formel sollte laut scheitern, wenn etwas nicht stimmt, ohne dass ein Kommentar nötig ist, der erklärt, was sie tut. XVERWEIS ist in dieser Hinsicht einfacher als jede Nachschlagefunktion zuvor.

Siehe auch: Fehlerbehandlung in Excel-Formeln — WENNFEHLER, WENNNV und die IST-Familie für die Fehlerschicht um jede Suche, Warum jeder Datensatz eine Excel-Tabelle sein sollte für die strukturierten Verweise, die Suchformeln stabil halten, Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ für das in Teil 6 genutzte Überlaufverhalten und Volatile Funktionen — was sie sind und wann man sie vermeiden sollte für den Grund, warum der Bezugs-Trick in großen Modellen zählt.

Dieser Artikel ist Teil der Reihe helpme.safeoffice.de mit praxisnahen Anleitungen zu Excel-Funktionen, Arbeitsmappen-Modellierung und Datenlösungen. Die Reihe richtet sich an Unternehmen, die wirksame und wartbare Werkzeuge wollen, die alle verstehen.