Mit XVERWEIS holen Sie einen Wert aus einer anderen Tabelle, ohne Spalten zu zählen und ohne darauf zu achten, in welcher Reihenfolge sie stehen. Die Funktion ist der Nachfolger von SVERWEIS und nimmt die Ergebnisspalte als Bereich entgegen, nicht als Nummer. Diese Spalte darf auch links von der Suchspalte liegen.
In diesem Beitrag bauen Sie die Formel Schritt für Schritt auf, holen Werte nach links, fangen fehlende Treffer ab und übernehmen mehrere Spalten mit einer einzigen Formel. Die Beispiele stammen aus derselben Arbeitsmappe wie der Beitrag zur SVERWEIS-Funktion.
Voraussetzung: XVERWEIS gibt es in Microsoft 365, Excel 2021 und Excel 2024. In Excel 2016 und Excel 2019 fehlt die Funktion. Dort erscheint stattdessen die Meldung #NAME?.
Der Aufbau von XVERWEIS
Die Funktion braucht nur drei Angaben. Ein Spaltenindex wie bei SVERWEIS entfällt, stattdessen markieren Sie die Ergebnisspalte direkt.
| Argument | Bedeutung |
| Suchkriterium | Der Wert, nach dem gesucht wird |
| Suchmatrix | Die Spalte, in der gesucht wird |
| Rückgabematrix | Die Spalte, aus der das Ergebnis kommt |
| wenn_nicht_gefunden | Optional. Der Text, der bei keinem Treffer erscheint |
| Vergleichsmodus | Optional. Standard ist die genaue Übereinstimmung |
| Suchmodus | Optional. 1 sucht von oben nach unten, -1 von unten nach oben. 2 und -2 nutzen eine Binärsuche und setzen eine sortierte Liste voraus |
Der wichtigste Unterschied steckt im letzten Punkt: XVERWEIS sucht von sich aus genau. Bei SVERWEIS müssen Sie dafür ausdrücklich FALSCH schreiben, sonst liefert die Funktion stillschweigend den nächstkleineren Treffer.
Die erste Formel: Bezeichnung zur Artikelnummer
Auf dem Blatt Bestellungen stehen die Artikelnummern in Spalte C. Die Bezeichnungen liegen auf dem Blatt Preisliste. Klicken Sie in E4 und geben Sie ein:
=XVERWEIS($C4;Preisliste!$A$4:$A$13;Preisliste!$B$4:$B$13)- $C4 ist das Suchkriterium, die Artikelnummer dieser Zeile.
- Preisliste!$A$4:$A$13 ist die Suchmatrix, also die Spalte mit den Artikelnummern.
- Preisliste!$B$4:$B$13 ist die Rückgabematrix mit den Bezeichnungen.
Beide Bereiche müssen gleich viele Zeilen umfassen. Sind sie unterschiedlich lang, meldet Excel #WERT!.
Kopieren Sie die Formel bis Zeile 11 nach unten. Jede Zeile erhält ihre Bezeichnung.
Nach links suchen
Hier zeigt sich der größte Vorteil. Gesucht ist diesmal die Artikelnummer zu einer bekannten Bezeichnung. Die Artikelnummer steht links von der Bezeichnung, SVERWEIS kommt damit nicht zurecht.
=XVERWEIS(G4;$B$4:$B$13;$A$4:$A$13)
Suchmatrix und Rückgabematrix sind zwei unabhängige Bereiche. Ihre Reihenfolge im Arbeitsblatt spielt keine Rolle. Damit entfällt auch das Zählen von Spalten.
Fehlende Treffer abfangen
Findet XVERWEIS nichts, erscheint #NV. Das vierte Argument ersetzt diese Meldung durch einen eigenen Text. Bei SVERWEIS brauchen Sie dafür zusätzlich WENNFEHLER.
=XVERWEIS(G4;$B$4:$B$13;$A$4:$A$13;"nicht im Sortiment")
Ein leerer Text funktioniert ebenso. Schreiben Sie dafür zwei Anführungszeichen direkt hintereinander, dann bleibt die Zelle bei fehlendem Treffer leer.
Mehrere Spalten mit einer Formel holen
Die Rückgabematrix darf mehrere Spalten breit sein. Excel gibt dann alle Werte nebeneinander aus, ohne dass Sie eine zweite Formel schreiben.
=XVERWEIS(C4;Preisliste!$A$4:$A$13;Preisliste!$B$4:$D$13)
Die Formel steht nur in E4, die Werte in F4 und G4 entstehen automatisch. Excel nennt das einen Überlauf. Wichtig dabei: Die Zellen rechts daneben müssen leer sein. Steht dort etwas, erscheint #ÜBERLAUF!.
Der Überlauf setzt Microsoft 365, Excel 2021 oder Excel 2024 voraus. Das sind dieselben Versionen, die auch XVERWEIS mitbringen. Wer mit Excel 2019 arbeitet, schreibt je Spalte eine eigene Formel mit SVERWEIS oder mit INDEX und VERGLEICH.
Das fünfte Argument: ungefähre Suche
Wie SVERWEIS beherrscht auch XVERWEIS die Suche nach dem nächstliegenden Wert, etwa für eine Rabattstaffel. Diese Suche steuert das fünfte Argument, der Vergleichsmodus.
| Wert | Bedeutung |
0 | genaue Übereinstimmung, der Standard |
-1 | genau oder der nächstkleinere Wert |
1 | genau oder der nächstgrößere Wert |
2 | Suche mit Platzhaltern * und ? |
Für eine Rabattstaffel entspricht -1 dem, was WAHR bei SVERWEIS leistet:
=XVERWEIS(F4;Rabatte!$A$4:$A$8;Rabatte!$B$4:$B$8;;-1)Die beiden Semikolons hintereinander sind kein Tippfehler. Sie überspringen das vierte Argument, das hier nicht gebraucht wird.
Ein Unterschied zu SVERWEIS: Die Staffel muss dafür nicht sortiert sein. Bei SVERWEIS mit WAHR führt eine unsortierte Staffel zu falschen Werten ohne Fehlermeldung.
Von unten nach oben suchen
Ein sechstes Argument bestimmt die Suchrichtung. Mit -1 beginnt XVERWEIS beim letzten Eintrag und arbeitet sich nach oben.
=XVERWEIS(C4;Preisliste!$A$4:$A$13;Preisliste!$D$4:$D$13;;0;-1)Das ist nützlich bei Listen, die fortlaufend ergänzt werden. Steht ein Artikel mehrfach darin, weil sich der Preis geändert hat, liefert diese Schreibweise den jüngsten Eintrag statt des ältesten. SVERWEIS kann das nicht.
XVERWEIS oder SVERWEIS
| SVERWEIS | XVERWEIS | |
| Verfügbar in | allen Excel-Versionen | Microsoft 365, Excel 2021 und 2024 |
| Suchrichtung | nur nach rechts | nach rechts und nach links |
| Ergebnisspalte | als Nummer | als Bereich |
| Spalte eingefügt | Ergebnis wird falsch | Ergebnis bleibt richtig |
| Ohne Treffer | #NV | eigener Text möglich |
| Wenn das Argument für die Art der Suche fehlt | ungefähre Suche | genaue Suche |
| Mehrere Spalten auf einmal | nicht möglich | möglich |
Ein Punkt in dieser Tabelle verdient eine Erklärung. Der Spaltenindex bei SVERWEIS ist eine feste Zahl. Fügen Sie innerhalb der Matrix eine Spalte ein, zeigt die Formel auf die falsche Spalte, ohne dass eine Meldung erscheint. XVERWEIS arbeitet mit Bereichen, die Excel bei solchen Änderungen mitzieht.
Wann SVERWEIS die bessere Wahl bleibt
Wenn Sie die Datei weitergeben und nicht wissen, welche Excel-Version die Empfänger nutzen. Öffnet jemand mit Excel 2019 eine Datei mit XVERWEIS, erscheint in allen betroffenen Zellen #NAME?. Die Werte sind dann nicht mehr lesbar.
Für Dateien, die im Haus bleiben und auf bekannten Versionen laufen, ist XVERWEIS die kürzere Schreibweise. Ihre eigene Version finden Sie unter Datei → Konto.
XVERWEIS auf Englisch
In einer englischen Excel-Version heißt die Funktion XLOOKUP. Die Argumente lauten lookup_value, lookup_array, return_array und if_not_found.
Übungsdatei zum Herunterladen
Die Tabellen dieses Beitrags stecken in derselben Arbeitsmappe wie die übrigen Beiträge zum Thema Verweisfunktionen.
Was in der Übungsdatei steckt
- Bestellungen und Preisliste für die Grundlagen
- Zahlungen für den Abgleich zweier Listen
- Kundenpreise für die Suche mit zwei Kriterien
- Rabatte für die Umsatzstaffel
- Fehlersuche mit dem Klassiker Zahl gegen Text
Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.
Für ältere Versionen führt die Kombination aus INDEX und VERGLEICH zum selben Ergebnis.
Weitere Anleitungen zu Verweisfunktionen
- SVERWEIS Schritt für Schritt: der Vorgänger, der in jeder Excel-Version läuft.
- INDEX und VERGLEICH: die Alternative für Excel 2016 und 2019.
- SVERWEIS mit zwei Suchkriterien: XVERWEIS mit zwei Bedingungen, ohne Hilfsspalte.
- Alle Treffer ausgeben: wenn Sie nicht einen, sondern alle Treffer brauchen.