XVERWEIS in Excel (XLOOKUP): So verwendest du es
Mit XVERWEIS gibst du an, wo Excel suchen soll und wo die Antwort steht. Es funktioniert in beide Richtungen und sucht standardmäßig eine genaue Übereinstimmung. Du brauchst Excel 2021 oder 365.
Probier die ersten Übungen kostenlos ausIn der Lektion Nachschlagen mit XVERWEIS
Was macht XVERWEIS?
XVERWEIS sucht einen Wert in einer Spalte und gibt dir zurück, was in derselben Zeile in einer anderen Spalte steht. Du zeigst einfach auf die beiden Spalten: die, in der Excel suchen soll, und die mit der Antwort.
Du musst also nichts zählen, die Antwort darf links oder rechts der Suchspalte stehen, und XVERWEIS sucht standardmäßig eine genaue Übereinstimmung. Mit dem vierten Argument legst du gleich deine eigene Meldung für einen Wert fest, den Excel nicht findet.
Wann verwendest du XVERWEIS?
- Der Preis zu einem Code: =XVERWEIS(E2;A2:A50;C2:C50).
- Der Code zu einem Produktnamen, also nach links: =XVERWEIS(E2;B2:B50;A2:A50).
- Deine eigene Meldung, wenn ein Wert fehlt: =XVERWEIS(E2;A2:A50;C2:C50;"Unbekannt").
- Der Rabatt für einen Betrag, der zwischen zwei Grenzen liegt: =XVERWEIS(B2;F2:F5;G2:G5;0;-1).
Syntax
Die Syntax ist das feste Muster einer Funktion. Die Wörter zwischen den Klammern sind Platzhalter: Dort setzt du selbst etwas ein, zum Beispiel eine Zelle, eine Zahl oder einen Text. Alles andere tippst du genau so, wie es dasteht.
Mit einem Beispiel: =XVERWEIS("Bildschirm";B2:B5;A2:A5;"Unbekannt")
So liest du die Formel, Teil für Teil:
| Teil | Was es bedeutet | Im Beispiel |
|---|---|---|
= | Jede Formel beginnt mit einem Gleichheitszeichen. Ohne = hält Excel, was du tippst, für gewöhnlichen Text. | = |
XVERWEIS | Der Name der Funktion: So weiß Excel, was es tun soll. Du darfst ihn kleinschreiben, Excel setzt ihn selbst in Großbuchstaben. | XVERWEIS |
( ) | Direkt nach dem Namen kommen die Klammern. Dazwischen steht, was die Funktion zum Arbeiten braucht: die Argumente. | ( ) |
Suchkriterium | Was du suchst. | "Bildschirm" |
Suchmatrix | Die Spalte oder Zeile, in der du suchst. | B2:B5 |
Rückgabematrix | Die Spalte oder Zeile mit der Antwort. | A2:A5 |
[wenn_nicht_gefunden] | Optional: was du sehen willst, wenn Excel den Wert nicht findet. | "Unbekannt" |
[Vergleichsmodus] | Optional: 0 für eine genaue Übereinstimmung (Standard), -1 für den nächstkleineren und 1 für den nächstgrößeren Wert. | weggelassen |
[Suchmodus] | Optional: -1, um unten zu beginnen. | weggelassen |
; | Ein Semikolon trennt die Argumente voneinander. In einem englischen Excel ist das ein Komma. | ; |
" " | Text tippst du in Anführungszeichen, wie "Berlin". Eine Zahl oder eine Zelle, wie 500 oder A2, tippst du ohne. | |
[ ] | Was in eckigen Klammern steht, darfst du weglassen. Die eckigen Klammern selbst tippst du nie. |
XVERWEIS Schritt für Schritt
E1 enthält =XVERWEIS("Bildschirm";B2:B5;A2:A5;"Unbekannt"). Sucht nach links, was SVERWEIS nicht kann. Ändere die Zahlen oder die Formel und schau, was passiert.
So baust du die Formel selbst auf:
- Tippe in E1 die Funktion mit einer Klammer.
=XVERWEIS( - Tippe, was du suchst: "Bildschirm". Tippe ein Semikolon.
=XVERWEIS("Bildschirm"; - Markiere, wo Excel suchen soll: die Produktnamen in B2:B5. Tippe ein Semikolon.
=XVERWEIS("Bildschirm";B2:B5; - Markiere, wo die Antwort steht: die Codes in A2:A5. Diese Spalte darf links von der Suchspalte stehen.
=XVERWEIS("Bildschirm";B2:B5;A2:A5 - Tippe ein Semikolon und eine Meldung für ein Produkt, das es nicht gibt: "Unbekannt". Schließ die Klammer und drück die Eingabetaste: P-204.
=XVERWEIS("Bildschirm";B2:B5;A2:A5;"Unbekannt")
Weitere Beispiele
Der Preis zu einem Code
E1 enthält =XVERWEIS("P-102";A2:A5;C2:C5). XVERWEIS sucht in den Codes und holt die Antwort aus den Preisen. Eine Spaltennummer brauchst du nicht.
Deine eigene Meldung
E1 enthält =XVERWEIS("P-999";A2:A5;C2:C5;"Unbekannt"). Den Code gibt es nicht. Statt #NV siehst du die Meldung aus dem vierten Argument.
Der nächstkleinere Wert
D1 enthält =XVERWEIS(750;A2:A4;B2:B4;0;-1). Mit -1 als fünftem Argument nimmt Excel den nächstkleineren Wert, wenn es 750 nicht gibt: 500, also 5 % Rabatt.
Die Vorteile
Kein Spaltenzählen mehr, eine Suche in alle Richtungen und deine eigene Meldung, wenn nichts gefunden wird. Mit -1 als sechstem Argument beginnt Excel unten, praktisch, um den letzten Eintrag zu finden.
XVERWEIS oder SVERWEIS?
XVERWEIS sucht standardmäßig eine genaue Übereinstimmung, schaut auch nach links und geht nicht kaputt, wenn du eine Spalte hinzufügst, weil du keine Spalten zählst. Wer eine Version vor Excel 2021 hat, sieht #NAME?. Teilst du deine Dateien mit vielen Leuten, bleibt SVERWEIS oder INDEX mit VERGLEICH die sicherste Wahl.
Mehrere Spalten auf einmal holen
Markierst du mehrere Spalten als Antwort, zum Beispiel =XVERWEIS(E2;A2:A5;B2:C5), füllt Excel die Nachbarzellen mit dem Produkt und dem Preis. Diese Zellen müssen leer sein, sonst bekommst du #ÜBERLAUF!.
Wo es oft schiefgeht
- Funktioniert nicht in Excel 2019 oder älter.
- Suchmatrix und Rückgabematrix müssen gleich groß sein.
Übe das in der Lektion Nachschlagen mit XVERWEIS. Die ersten Übungen sind kostenlos.
Häufige Fragen
Wie finde ich den nächstliegenden Wert?
Gib -1 als fünftes Argument für den nächstkleineren Wert an, oder 1 für den nächstgrößeren.