XVERWEIS statt SVERWEIS: Daten in Excel suchen und finden

XVERWEIS steht in Microsoft 365, in Excel 2021 und neueren Einzelversionen sowie in Excel im Browser zur Verfügung. In Excel 2019 und älteren Versionen fehlt die Funktion. Die Formeln unten sind mit Semikolon als Trennzeichen geschrieben, wie es die deutsche Excel-Version verlangt.

Was ein Nachschlagen leistet

Du hast eine Liste mit Artikelnummern, Bezeichnungen und Preisen und eine zweite Tabelle mit Bestellungen, in der nur die Artikelnummer steht. Statt den Preis jedes Mal von Hand zu suchen, lässt du Excel die Nummer in der Liste finden und den Preis daneben übernehmen. Diese Aufgabe heißt Nachschlagen. Früher war dafür SVERWEIS zuständig, heute ist XVERWEIS die bequemere Lösung.

Den Einstieg in die Grundlagen der Formeln findest du in Excel-Formeln für Einsteiger. Dort wird auch erklärt, warum absolute Zellbezüge mit Dollarzeichen beim Nachschlagen wichtig sind.

Aufbau der Funktion XVERWEIS

Die vollständige Schreibweise lautet:

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

Nur die ersten drei Argumente sind Pflicht. Lässt du die übrigen weg, sucht Excel nach einer genauen Übereinstimmung von oben nach unten.

Erstes Beispiel: Preis zur Artikelnummer

In Blatt „Liste“ stehen die Artikelnummern in Spalte A (A2 bis A500) und die Preise in Spalte C (C2 bis C500). In Blatt „Bestellung“ steht die Nummer in Zelle A2, der Preis soll in B2 erscheinen. Dort trägst du ein:

=XVERWEIS(A2; Liste!A2:A500; Liste!C2:C500)

Excel sucht die Nummer aus A2 in der Spalte A der Liste und gibt den Preis aus derselben Zeile in Spalte C zurück. Ziehe die Formel nach unten, und jede Bestellzeile bekommt ihren Preis. Wenn du die Daten als Tabelle formatiert hast, kannst du statt der Zellbereiche die Tabellennamen verwenden, zum Beispiel Artikel[Nummer] und Artikel[Preis]. Das wächst automatisch mit, wenn neue Zeilen hinzukommen.

Nach links suchen und in Zeilen suchen

SVERWEIS kann nur Werte rechts vom Suchbegriff liefern. XVERWEIS kennt diese Beschränkung nicht, denn Such- und Rückgabespalte sind getrennte Angaben. Steht die Bezeichnung in Spalte B und die Artikelnummer in Spalte A, findest du die Nummer zu einer Bezeichnung mit:

=XVERWEIS(E2; Liste!B2:B500; Liste!A2:A500)

Auch waagerechte Listen sind kein Problem. Gib als Such- und Rückgabematrix je eine Zeile an, etwa Liste!B1:M1 und Liste!B2:M2. Damit ersetzt XVERWEIS zugleich den älteren WVERWEIS.

Wenn nichts gefunden wird

Findet Excel keinen Treffer, zeigt es den Fehler #NV. Beim XVERWEIS fängst du das direkt im vierten Argument ab, ohne die Formel in WENNNV zu verpacken:

=XVERWEIS(A2; Liste!A2:A500; Liste!C2:C500; "nicht vorhanden")

So steht statt der Fehlermeldung der Text „nicht vorhanden“ in der Zelle. Du kannst auch ein leeres Zeichenpaar "" angeben, dann bleibt die Zelle optisch leer. Überlege, ob ein sichtbarer Hinweis nicht besser ist, damit fehlende Einträge auffallen.

Ungefähre Treffer, Platzhalter und letzter Treffer

Das fünfte Argument, der Vergleichsmodus, legt fest, wie genau die Suche arbeitet:

• 0: Genaue Übereinstimmung. Das ist der Standard.
• -1: Genaue Übereinstimmung oder der nächstkleinere Wert.
• 1: Genaue Übereinstimmung oder der nächstgrößere Wert.
• 2: Platzhalter wie * und ? sind erlaubt.

Die Modi -1 und 1 brauchst du für Staffeln, etwa Rabatte nach Menge oder Notenstufen. Du legst dazu eine Tabelle mit den Schwellen an und lässt Excel den passenden Eintrag suchen. Mit Modus 2 findest du Teilbegriffe: =XVERWEIS("*"&A2&"*"; Liste!B2:B500; Liste!A2:A500; "kein Treffer"; 2) sucht jede Bezeichnung, die den Text aus A2 enthält.

Das sechste Argument, der Suchmodus, steuert die Richtung. Mit -1 beginnt Excel am Ende der Liste und liefert damit den letzten Treffer, was bei Verlaufslisten mit mehreren Einträgen pro Person nützlich ist. Der Standardwert 1 liefert den ersten Treffer von oben.

Mehrere Spalten auf einmal zurückgeben

Gibst du als Rückgabematrix mehrere Spalten an, liefert XVERWEIS alle auf einmal. Beispiel: =XVERWEIS(A2; Liste!A2:A500; Liste!B2:D500) schreibt Bezeichnung, Menge und Preis in drei Zellen nebeneinander. Excel breitet das Ergebnis selbst aus, man spricht vom Überlauf. Die Zellen rechts neben der Formel müssen dafür leer sein, sonst erscheint der Fehler #ÜBERLAUF!.

Diese Eigenschaft gibt es nur in Excel-Versionen mit dynamischen Arrays. In Excel 2021 und Microsoft 365 ist sie vorhanden.

Zwei Suchkriterien kombinieren

Manchmal ist eine Zeile nur durch zwei Angaben eindeutig, zum Beispiel Nachname und Ort. Du verbindest die Kriterien mit dem Und-Zeichen und bildest dasselbe in der Suchmatrix nach:

=XVERWEIS(A2&B2; Liste!A2:A500&Liste!B2:B500; Liste!C2:C500; "nicht gefunden")

Excel verkettet beide Spalten zu einer Hilfsliste im Speicher und sucht darin. Du brauchst keine zusätzliche Hilfsspalte. Bei sehr großen Listen kann die Berechnung etwas länger dauern.

Vergleich mit SVERWEIS

Der größte praktische Unterschied ist der Standard der Suche. Ein SVERWEIS ohne viertes Argument sucht ungefähr und liefert bei unsortierten Listen falsche Treffer, ohne dass es auffällt. Wer das einmal erlebt hat, wechselt gern zu XVERWEIS.

Ältere Excel-Versionen: INDEX und VERGLEICH

Hast du Excel 2019 oder älter oder gibst du die Datei an jemanden mit einer älteren Version weiter, ist die Kombination aus INDEX und VERGLEICH der sichere Ausweg:

=INDEX(Liste!C2:C500; VERGLEICH(A2; Liste!A2:A500; 0))

VERGLEICH liefert die Position des Treffers, INDEX gibt den Wert an dieser Position zurück. Die Null am Ende steht für eine genaue Suche. Auch diese Variante sucht in beide Richtungen. Prüfe vor dem Weitergeben einer Datei, ob alle Empfänger eine Version mit XVERWEIS besitzen, sonst sehen sie nur Fehler.

Ob du auf eine neuere Office-Version wechselst oder eine Alternative nutzt, kann dir Microsoft 365 einfach erklärt helfen. In LibreOffice solltest du vorab prüfen, ob deine Version die Funktion unterstützt, siehe LibreOffice statt Microsoft Office.

Typische Fehler und ihre Ursachen

• #NV trotz vorhandenem Wert: Meist stehen Leerzeichen am Ende, oder eine Zahl ist als Text gespeichert. Prüfe mit GLÄTTEN oder wandle den Typ um.
• #WERT!: Such- und Rückgabematrix haben unterschiedlich viele Zeilen. Beide Bereiche müssen gleich lang sein.
• #ÜBERLAUF!: Zellen im Ausbreitungsbereich sind belegt. Leere den Bereich rechts oder unterhalb der Formel.
• #NAME?: Die Excel-Version kennt XVERWEIS nicht. Nutze INDEX und VERGLEICH.
• Falscher Wert: Die Suchmatrix enthält den Suchbegriff mehrfach, und du erwartest den letzten Treffer. Stelle den Suchmodus auf -1.
• Formel verrutscht beim Kopieren: Der Suchbereich hat keine absoluten Bezüge. Setze Dollarzeichen, etwa Liste!$A$2:$A$500.

Wenn du Daten aus mehreren Listen zusammenführst, helfen Sortieren und Filtern vorher, siehe Excel: Tabelle sortieren und filtern. Die Auswertung der Ergebnisse gelingt dann mit Excel-Pivot-Tabelle erstellen.

Häufige Fragen

Ersetzt XVERWEIS den SVERWEIS vollständig?

Funktional ja, denn XVERWEIS kann alles, was SVERWEIS kann, und mehr. Alte Dateien mit SVERWEIS funktionieren weiter. Du musst sie nicht umbauen.

Warum zeigt Excel statt der Funktion nur #NAME?

Die Excel-Version kennt die Funktion nicht. In Excel 2019 und älter fehlt sie. Verwende INDEX und VERGLEICH oder aktualisiere Office.

Kann ich über mehrere Blätter suchen?

Ja. Du gibst die Bereiche mit dem Blattnamen an, zum Beispiel Liste!A2:A500. Für Suche in mehreren Blättern verschachtelst du mehrere XVERWEIS-Funktionen mit dem vierten Argument.

Wie sortiere ich Treffer alphabetisch?

XVERWEIS liefert Werte, sortiert aber nicht. Das Sortieren erledigst du anschließend mit der Funktion SORTIEREN oder über das Datenmenü.

Ist die Funktion schneller als SVERWEIS?

Bei normalen Tabellen merkst du keinen Unterschied. Bei sehr großen Datenmengen kann der binäre Suchmodus die Berechnung beschleunigen, wenn die Liste sortiert ist.