Start › Excel-Funktionen › XVERWEIS
XVERWEIS in Excel: so funktioniert's, mit Beispielen
XVERWEIS sucht einen Wert in einem Bereich und gibt das passende Element aus einem anderen Bereich zurück. Er ersetzt SVERWEIS und WVERWEIS: Er kann nach links oder rechts suchen, findet standardmäßig genaue Übereinstimmungen und lässt Sie festlegen, was angezeigt wird, wenn nichts gefunden wird. Im englischen Excel heißt die Funktion XLOOKUP.
XVERWEIS · In dieser Anleitung
Syntax von XVERWEIS
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])Nur die ersten drei Argumente sind Pflicht.
| Argument | Bedeutung |
|---|---|
| Suchkriterium | Der Wert, der gefunden werden soll, zum Beispiel "Mango" oder eine Zelle wie F2. |
| Suchmatrix | Die Spalte (oder Zeile), in der Sie suchen. |
| Rückgabematrix | Die Spalte (oder Zeile), aus der Sie das Ergebnis wollen. Sie muss genauso groß sein wie die Suchmatrix. |
| wenn_nicht_gefunden (optional) | Was angezeigt wird, wenn es keine Übereinstimmung gibt, zum Beispiel "Nicht gefunden". Ohne dieses Argument erhalten Sie #NV. |
| Vergleichsmodus (optional) | 0 genaue Übereinstimmung (Standard), -1 genau oder nächstkleineres Element, 1 genau oder nächstgrößeres Element, 2 Übereinstimmung mit Platzhaltern. |
| Suchmodus (optional) | 1 sucht vom ersten bis zum letzten Element (Standard), -1 vom letzten zum ersten. 2 und -2 sind schnelle binäre Suchen in sortierten Daten. |
Welche Excel-Versionen haben XVERWEIS? Microsoft 365, Excel 2021, Excel 2024 und Excel für das Web. In Excel 2019 und älter ergibt die Formel #NAME?. Verwenden Sie dort SVERWEIS oder INDEX/VERGLEICH.
Einfaches Beispiel: einen Preis suchen
Diese Produktliste verwenden wir für alle Beispiele auf der Seite.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Produkt | Bestand | Preis |
| 2 | P-101 | Apfel | 120 | 0,35 € |
| 3 | P-102 | Banane | 85 | 0,25 € |
| 4 | P-103 | Kirsche | 300 | 0,10 € |
| 5 | P-104 | Mango | 40 | 1,20 € |
| 6 | P-105 | Birne | 60 | 0,30 € |
Diese Formel ergibt 1,20 €. Excel sucht „Mango“ in B2:B6, findet sie an 4. Stelle und gibt den 4. Wert aus D2:D6 zurück. Es gibt keinen Spaltenindex zu zählen: Eine später eingefügte Spalte macht die Formel also nicht kaputt.
In der Praxis verweisen Sie auf eine Zelle, statt den Wert zu tippen: =XVERWEIS(F2; $B$2:$B$6; $D$2:$D$6)
XVERWEIS üben: selbst ausprobieren
Lesen ist das eine, selbst tippen das andere. Bauen Sie die Formel unten Schritt für Schritt auf: Die Tipps sagen Ihnen, was Sie eingeben müssen, und Sie können auf Zellen klicken oder darüber ziehen, um sie hinzuzufügen, genau wie in Excel.
Aufgabe: Finden Sie in F2 den Preis des Produkts P-104: =XVERWEIS(E2;A2:A7;C2:C7)
Einen Wert links davon suchen
SVERWEIS kann nur Spalten rechts der Suchspalte zurückgeben. XVERWEIS ist die Richtung egal. Um den Code der Mango zu finden, der links vom Produktnamen steht:
=XVERWEIS("Mango"; B2:B6; A2:A6)Diese Formel ergibt P-104.
Eine eigene Meldung anzeigen, wenn nichts gefunden wird
Verwenden Sie das vierte Argument, statt die Formel in WENNFEHLER zu packen:
=XVERWEIS("Kiwi"; B2:B6; D2:D6; "Nicht gefunden")Die Kiwi steht nicht in der Liste: Das Ergebnis ist also Nicht gefunden statt #NV. Sie können auch 0 oder einen leeren Text "" zurückgeben, wenn Ihnen das lieber ist.
Mehrere Spalten auf einmal zurückgeben
Ist die Rückgabematrix breiter als eine Spalte, gibt XVERWEIS die ganze passende Zeile dieses Bereichs zurück. Das Ergebnis „läuft über“ in die Zellen rechts davon.
=XVERWEIS("Mango"; B2:B6; C2:D6)In F2 eingegeben, setzt sie den Bestand (40) in F2 und den Preis (1,20 €) in G2. Achten Sie darauf, dass die Zellen, in die sie überläuft, leer sind; sonst sehen Sie #ÜBERLAUF!.
Ungefähre Übereinstimmung: Stufen und Staffeln
Für Stufen wie Noten oder Rabattstaffeln setzen Sie den Vergleichsmodus auf -1 (genaue Übereinstimmung oder nächstkleineres Element).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Mindestpunkte | Note | Punkte | Note | |
| 2 | 0 | F | 73 | B | |
| 3 | 50 | D | |||
| 4 | 60 | C | |||
| 5 | 70 | B | |||
| 6 | 80 | A |
In Spalte A gibt es keine 73: XVERWEIS nimmt den nächstkleineren Wert, 70, und gibt B zurück. Beachten Sie das leere vierte Argument: Die zwei aufeinanderfolgenden Semikolons überspringen wenn_nicht_gefunden. Anders als bei der ungefähren Übereinstimmung von SVERWEIS funktioniert das auch, wenn die Liste nicht sortiert ist.
Die letzte statt der ersten Übereinstimmung finden
Setzen Sie den Suchmodus auf -1, um von unten nach oben zu suchen. Das ist praktisch für Protokolle oder Bestelllisten, bei denen Sie den neuesten Eintrag wollen: =XVERWEIS("Anna"; A2:A100; C2:C100; ; 0; -1) gibt den letzten Wert von Anna zurück.
Häufige XVERWEIS-Fehler
#NV
Es wurde keine Übereinstimmung gefunden. Prüfen Sie Tippfehler, überflüssige Leerzeichen (GLÄTTEN entfernt sie) und als Text gespeicherte Zahlen. Oder geben Sie einen Wert für wenn_nicht_gefunden an.
#WERT!
Suchmatrix und Rückgabematrix sind unterschiedlich groß, zum Beispiel B2:B6 und D2:D7. Geben Sie ihnen dieselbe Höhe.
#NAME?
Ihre Excel-Version hat kein XVERWEIS, oder der Funktionsname ist falsch geschrieben.
#ÜBERLAUF!
Die Formel gibt mehr als einen Wert zurück, aber Zellen, die sie braucht, sind nicht leer. Leeren Sie die Zellen rechts oder darunter.
XVERWEIS im Vergleich zu SVERWEIS
| XVERWEIS | SVERWEIS | |
|---|---|---|
| Standard-Übereinstimmung | Genau | Ungefähr (außer Sie geben FALSCH an) |
| Nach links suchen | Ja | Nein |
| Bricht beim Einfügen von Spalten | Nein | Ja (fester Spaltenindex) |
| Eigener Wert, wenn nichts gefunden | Eingebaut | Braucht WENNFEHLER |
| Von unten suchen | Ja | Nein |
| Funktioniert in Excel 2019 und älter | Nein | Ja |
Haben alle, die die Datei öffnen, eine aktuelle Excel-Version, verwenden Sie XVERWEIS. Geht die Datei an Personen mit älteren Versionen, bleiben Sie bei SVERWEIS oder INDEX/VERGLEICH.
Häufige Fragen
Ist XVERWEIS besser als SVERWEIS?
In den meisten Fällen ja. Er ist standardmäßig genau, sucht in alle Richtungen, bricht nicht, wenn Spalten verschoben werden, und hat eine eingebaute Option für „nicht gefunden“. Einziger Nachteil: Es gibt ihn nicht in Excel 2019 und älter.
Unterscheidet XVERWEIS zwischen Groß- und Kleinschreibung?
Nein. „mango“ und „MANGO“ passen zur selben Zeile.
Kann XVERWEIS mehrere Werte zurückgeben?
Er kann eine ganze Zeile von Werten zurückgeben, wenn die Rückgabematrix mehrere Spalten breit ist. Um alle passenden Zeilen zurückzugeben, verwenden Sie die Funktion FILTER.
Warum zeigt XVERWEIS #NAME? an?
Ihre Excel-Version unterstützt XVERWEIS nicht. Er ist in Microsoft 365, Excel 2021, Excel 2024 und Excel für das Web verfügbar.