XVERWEIS
Den XVERWEIS in Excel kannst du nutzen, um dir bestimmte Daten aus einer Tabelle anzeigen zu lassen. Wie das geht, siehst du hier und im Video !
Inhaltsübersicht
Excel XVERWEIS einfach erklärt
Du kannst den XVERWEIS nutzen, um beispielsweise mit einer ID einen Namen aus einer Tabelle abzurufen. Im Beispiel hast du die Tabelle mit den Mitarbeitendendaten und willst zur ID 106 den Namen heraussuchen. Schau dir direkt an, wie die Funktion in Zelle B2 aussieht:
=XVERWEIS(A2; A5:A16; B5:B16)
=XVERWEIS(Suchkriterium; Suchbereich; Rückgabebereich)
Excel gibt den Namen Sophie Dupont zurück. Durch die Angabe des Rückgabebereichs auf B5 bis B16 wird nur der Name übernommen und keine weiteren Spalten.
Übrigens: Der XVERWEIS ist die englische Funktion XLOOKUP auf Deutsch.
XVERWEIS Syntax
Der XVERWEIS in Excel ist so aufgebaut:
=XVERWEIS(A2;A5:A16;B5:B16;“ID nicht vorhanden“;0;1)
- Name der Funktion: =XVERWEIS( )
- Kriterium, nach dem gesucht wird (Wonach?): A2
- Suchbereich, in dem gesucht wird (Wo?): A5:A16
- Rückgabebereich: Welche Spalteninformationen übertragen werden sollen. Achte darauf, dass Suchbereich und Rückgabebereich gleich hoch sind: B5:B16
- wenn nicht vorhanden: optional; was angezeigt werden soll, wenn das Kriterium nicht gefunden wird.
- Übereinstimmungstyp: optional; standardmäßig genaue Übereinstimmung (0)
- Suchmodus: optional; standardmäßig Suche von oben (1)
Übereinstimmungstyp
- 0: Standard — genaue Übereinstimmung, sonst Fehlermeldung
- −1: genaue Übereinstimmung, ansonsten nächstkleineres Element
- 1: genaue Übereinstimmung, alternativ nächstgrößeres Element
- 2: Platzhalter im Kriterium (*, ? und ~)
Suchmodus
- 1: Standard — Suche beginnt beim ersten Element
- −1: Suche beginnt beim letzten Element
- 2: Tabelle muss in aufsteigender Reihenfolge sortiert sein
- −2: Tabelle muss in absteigender Reihenfolge sortiert sein
Übrigens: Der XVERWEIS gibt immer nur das erste Ergebnis aus.
XVERWEIS mit zwei oder mehr Kriterien
Neben dem Namen kannst du dir die Angaben zu Land und Abteilung einfach zusätzlich ausgeben lassen. Dazu erweiterst du den Rückgabebereich um die anderen Spalten C und D.
=XVERWEIS(A2;A5:A16;B5:D16;“ID nicht vorhanden“)
Hier im Beispiel wurde zusätzlich die Angabe für „wenn nicht vorhanden“ gemacht. Wenn es eine in A2 eingegebene ID nicht gibt, zeigt Excel ID nicht vorhanden an.
Um mehrere unterschiedliche Suchkriterien zu kombinieren, nutzt du einfach das &-Zeichen. Beispiel: =XVERWEIS(“Emma Müller“&“Deutschland“;B5:B16&C5:C16;B5:C16) gibt dir den Namen und das Land aus.
Einordnen in Tabelle mit XVERWEIS
Der XVERWEIS ordnet auch Werte in ein Schema ein. Zum Beispiel kannst du dir, auf Basis des Einkaufswerts eines Kunden, den erhaltenen Rabatt ausgeben lassen. Links steht die Tabelle dazu und der Wert in D2 wird eingegeben. Die Funktion zur Ausgabe des Rabatts in E2 sieht so aus.
=XVERWEIS(D2;B2:B4;A2:A4;““;-1)
Die Funktion sucht nach dem Einkaufswert in D2 im Bereich B2 bis B4. Zurückgegeben werden soll der jeweilige Rabatt in A2 bis A4. Da es den Wert 1.743 € nicht genau so in der Tabelle gibt, wird -1 als Übereinstimmungstyp angegeben. Das heißt, Excel sucht von oben nach dem nächstkleineren Element, wenn es keine genaue Übereinstimmung gibt. ““ sorgt dafür, dass die Spalte leer bleibt, wenn der Einkaufswert unter 1.000 € liegt. Der Rabatt bei einem Einkaufswert von 1.743 € beträgt also 3 %.
Im Beispiel kannst du sehen, dass der Suchbereich links vom Kriterienbereich liegt. Das funktioniert nur mit XLOOKUP auf Deutsch (XVERWEIS) und nicht mit dem SVERWEIS .
XVERWEIS vs. SVERWEIS
Die Funktionen XVERWEIS und SVERWEIS klingen im ersten Moment sehr ähnlich und doch unterscheiden sie sich an einigen Stellen. Während der X-VERWEIS Spalten oder Zeilen zurückgeben kann, ist der S-VERWEIS deutlich eingeschränkter. Er ermöglicht es, nur eine Spalte zurückzugeben und kann nur von oben nach unten suchen.
Der X-VERWEIS ist sozusagen der S(senkrechter)-Verweis und W(waagrechter)-Verweis in einem und ermöglicht die Suche in alle Richtungen. Selbst wenn der Suchbereich links vom Kriterienbereich liegt, ist das für den X-VERWEIS kein Problem. Und auch das Abzählen der Spalten ist nicht mehr nötig.
Übrigens: Der Excel XVERWEIS ist eine relativ neue Funktion, die erst ab Excel 2021 zur Verfügung steht.
XVERWEIS verschachteln
Einen verschachtelten XVERWEIS kannst du verwenden, um eine vertikale und horizontale Suche gleichzeitig durchzuführen. Im Beispiel hast du die Umsätze von verschiedenen Produkten, aufgeteilt auf die Quartale. Du suchst die Umsätze aus Quartal 2 für alle Lampen. Die gesuchten Lampen schreibst du in Zeile 1 und baust die Funktion in C2 so auf.
=XVERWEIS(C1;$A5:$A11;XVERWEIS($B2;$B4:$E4;$B5:$E11))
Achte darauf, die Spaltenangaben mit $ zu fixieren, bevor du die Formel auf die Zellen D2 und E2 überträgst.
XVERWEIS — häufigste Fragen
-
Wie funktioniert der XVERWEIS?
Die XVERWEIS-Funktion sucht in einem Bereich nach einem Kriterium und gibt die erste gefundene Übereinstimmung zurück. Beispiel: =XVERWEIS(Suchkriterium; Suchbereich; Rückgabebereich)
-
Wann XVERWEIS vs. SVERWEIS?
Der XVERWEIS kann sowohl senkrecht als auch waagrecht suchen und dabei im Gegensatz zum SVERWEIS Werte suchen, die links vom Kriterienbereich liegen.
Excel FILTER Funktion
Die Funktion XLOOKUP in Excel, also den deutschen XVERWEIS, hast du jetzt kennengelernt. In manchen Fällen suchst du mehrere Datensätze auf einmal, zum Beispiel alle Mitarbeitenden einer bestimmten Abteilung. Wie du dafür die FILTER-Funktion einsetzt, erfährst du hier.
