Es gibt eine relativ neue Funktion in Excel: Den XVerweis. Diese Funktion ermöglicht es – ähnlich wie der SVerweis – Daten zu durchsuchen und einen Wert zurückzugeben. Der XVerweis ist sozusagen ein verbesserter SVerweis, weil er deutlich mehr Möglichkeiten bietet.
Der XVerweis ist zweifellos eine praktische Ergänzung der bestehenden Excel Funktionen, der eine leistungsfähigere und flexiblere Möglichkeit bietet, Daten zu durchsuchen. Er ist – finde ich – eine einfachere, dabei aber leistungsfähigere Möglichkeit, bestimmte Daten zu finden, als der SVerweis.
XVerweis in Excel einfach erklärt
Lerne, wie man den neuen Excel XVerweis verwendet – einfach erklärt mit Beispielen.
Viele sagen, der XVerweis ist der neue SVerweis. Und das stimmt auch: Der neue XVerweis ist einfacher anzuwenden und kann mehr. Außerdem ist er nicht so fehleranfällig.
Die Funktion kann verwendet werden, wenn man innerhalb einer Tabelle oder eines Datenbereichs nach bestimmten Elementen sucht. So kann zum Beispiel nach dem Preis von einem bestimmten Produkt gesucht werden. Aber es kommt noch besser: Man kann sich auch mehrere Daten ausgeben lassen, also zum Beispiel den Lagerbestand, den Einkaufspreis und den Verkaufspreis eines Produktes.
Wir sehen und zunächst den Aufbau der Funktion an. Ich zeige dir, was für Angaben du machen musst, damit du sie korrekt verwenden kannst. Danach zeige ich dir mehrere Beispiele, die die Verwendung der Funktion verdeutlichen.
Fangen wir also mit dem Aufbau – auch Syntax genannt – an.
Excel XVerweis: Syntax
So ist der Excel XVerweis aufgebaut:
=XVERWEIS(Suchkritierium;Suchmatrix;Rückgabematrix;[wenn_nicht_gefunden];[Vergleichsmodus];[Suchmodus])
Nicht erschrecken, es ist nicht so schlimm, wie es aussieht. 🙂
Du möchtest dein Excel Wissen erweitern? Dann schau dir doch mal dieses Buch an: Datenvisualisierung – Ansprechende Diagramme und Grafiken*.
=XVerweis ist der Name der Funktion.
Zwingend angeben musst du nur die ersten drei Argumente in der Klammer, also Suchkriterium, Suchmatrix und Rückgabematrix:
Das Suchkriterium ist das, wonach du suchst.
Die Suchmatrix ist der Bereich, in dem du suchst.
Die Rückgabematrix ist der Wert, der zurückgegeben werden soll.
Diese theoretische Einteilung klingt immer kompliziert, man hätte sich auch einfachere Begriffe ausdenken können. Aber wenn man einmal das, was hinter den Begriffen steht, verstanden hat, ist der XVerweis recht einfach zu verwenden.
Obwohl es anfangs vielleicht einschüchternd wirken mag, sich mit diesen abstrakten Begriffen auseinanderzusetzen, lohnt es sich, sich die Zeit zu nehmen, sie zu verstehen. Denn sobald man hinter die Kulissen schaut und das zugrunde liegende Konzept erfasst hat, wird klar, dass der XVerweis eigentlich recht intuitiv ist.
Der Schlüssel liegt darin, sich von der abstrakten Fachsprache nicht abschrecken zu lassen und stattdessen einen Blick auf die eigentlichen Mechanismen zu werfen. Wenn man einmal versteht, wie der XVerweis funktioniert und welche Rolle seine Bestandteile spielen, wird die Anwendung dieses Tools zu einer recht leichten Aufgabe. Man beginnt, die Macht und Effizienz des XVerweises zu verstehen und kann ihn unkompliziert für seine Zwecke nutzen.
Im Folgenden ein paar Beispiele zur Veranschaulichung.
Beispiele für den XVerweis
Hier nun ein paar Beispiele für die Verwendung des Excel XVerweis.
Stelle dir eine Tabelle vor, in der wir diverse Artikel aufgelistet haben mit Angaben wie Artikelnummer, Einkaufspreis, Lagerbestand und so weiter:
Ich könnte nun zum Beispiel eine bestimmte Artikelnummer haben und den dazugehörigen Artikel suchen – mit dem XVerweis:
Das Suchkriterium ist die Artikelnummer. Die Suchmatrix ist der Bereich, in dem meine Artikelnummern stehen, also A2:A10. Und die Rückgabematrix ist der Bereich, in dem meine gesuchten Werte stehen – also die Artikel in B2:B10.
Standardmäßig gibt der XVerweis nur genaue Ergebnisse aus. Wenn ich mich also bei der Artikelnummer vertippe, wird ein Fehlerwert ausgegeben:
Sieh dir auch mein Video zum Thema an:
Sie sehen gerade einen Platzhalterinhalt von YouTube. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenXVerweis: Wenn nicht gefunden – Fehlermeldung
Bisher haben wir nur die ersten drei Kriterien angegeben. Kommen wir zum vierten Kriterium: wenn_nicht_gefunden.
Anstatt #NV kann ich mir damit eine andere Fehlermeldung anzeigen lassen, wenn mein Suchwert nicht in der Tabelle vorhanden ist. Diese gewünschte Fehlermeldung gebe ich in der Formel als vierten Wert an, bei „Wenn nicht gefunden“:
Ich schreibe in so einem Fall die Meldung, die ich bekommen möchte – Artikelnummer nicht vorhanden -, als vierten Wert in die Formel zwischen “ “ Anführungszeichen:
=XVERWEIS(B12;A2:A10;B2:B10;„Artikelnummer nicht vorhanden“)
Nun bekomme ich als Ausgabewert diese Meldung „Artikelnummer nicht vorhanden“, wenn ich eine Artikelnummer eingebe, die es nicht gibt.
Mehrere Werte ausgeben lassen vom XVerweis
Der XVerweis kann aber noch mehr. Ja, noch mehr! Man kann sich damit nämlich auch mehrere Ergebnisse ausgeben lassen. Zum Beispiel: Ich möchte nicht nur den Artikel bekommen, der zu der Artikelnummer gehört, sondern auch den Lagerbestand.
Um mehrere Werte zu bekommen, muss ich meine Rückgabematrix erweitern. Mir soll nicht nur der Artikel aus Spalte B ausgegeben werden, sondern auch der Lagerbestand aus Spalte C. Daher erweitere ich die Rückgabematrix auf B2:C10.
=XVERWEIS(B12;A2:A10;B2:C10;“Artikelnummer nicht vorhanden“)
Bereite die Zellen, in die die gesuchten Werte eingetragen werden sollen, entsprechend auf: Vergib entsprechende Überschriften, damit du weißt, wofür die ausgegebenen Werte stehen, also zum Beispiel „Artikel“ und „Lagerbestand“.
Der XVerweis kann auch nach links oder oben suchen
Und es wird noch besser: Der Excel XVerweis kann nicht nur nach rechts, sondern auch nach links oder nach oben suchen (Der SVerweis kann das nicht!).
Wenn ich also zum Beispiel den Artikel habe und die Artikelnummer wissen möchte, verwende ich diese Formel:
=XVERWEIS(B12;B2:B10;A2:A10)
Das Suchkriterium ist Laptop A, die Artikel stehen im Bereich B2:B10, und mein Rückgabewert steht links davon im Bereich A2:A10.
Nun wird mir völlig korrekt die Artikelnummer 103547 ausgegeben.
Mit dem älteren SVerweis ist eine solche Ausgabe nicht möglich.
Vergleichsmodus im XVerweis in Excel
Über den Vergleichsmodus des XVerweis kannst du festlegen, welche Werte ausgegeben werden:
0 entspricht der genauen Übereinstimmung (das ist Standard, auch wenn du den Wert nicht angibst).
-1 nimmst du wenn du eine genaue Übereinstimmung oder den nächst kleineren Wert möchtest.
1 nimmst du wenn du eine genaue Übereinstimmung oder den nächst größeren Wert möchtest.
2 wenn du eine Platzhalterübereinstimmung suchst.
-1 und 1 nimmt man also zum Beispiel, wenn man Zahlenbereiche hat (etwa ein Einkommen zwischen 2.000 und 3.000 Euro).
2 verwendet man, wenn man mit Platzhaltern sucht (also mit „*“):
Du weißt nur noch den Nachnamen einer Person und möchtest den ganzen Namen ausgegeben haben.
=XVERWEIS(„*“&E3;…)
Wir nehmen an, du schreibst den Nachnamen in Zelle E3, dann kannst du so nach dem gesamten Namen suchen. Zusätzlich musst du als Vergleichsmodus 2 angeben:
=XVERWEIS(„*“&E3;A3:A8;B3:B8;“Name nicht vorhanden“;2)
Suchmodus des XVerweis
Nun noch das letzte Kriterium: Der Suchmodus. Damit kannst du festlegen, wie der XVerweis durch deine Daten sucht:
1 Vom ersten zum letzten Element.
-1 Vom letzten zum ersten Element.
2 aufsteigend.
-2 absteigend.
Passende Blogbeiträge für dich:
Extrem praktisch: Ohne Formeln Teilergebnisse anzeigen lassen mit Excel Teilergebnis.
Mal nehmen: Multiplikation in Excel.
So wird es klappen: In Excel Kreisdiagramme erstellen.
Noch mehr Informationen zum XVerweis
Warum haben nicht alle diese Funktion?
Falls du den XVerweis in deiner Excel Version nicht findest: Nicht alle Versionen haben diese recht neue Funktion zur Verfügung. Als erstes wurde sie nur für Office 365 ausgerollt, für alte Versionen (2016 und 2019) ist sie nicht verfügbar.
Was ist der Unterschied zum SVerweis?
Der SVerweis ist fehleranfälliger, ganz besonders, wenn die Daten nicht exakt richtig sortiert sind. Außerdem kann der SVerweis nur in eine Richtung suchen, der XVerweis dagegen kann in alle Richtungen suchen. Das heißt, du kannst dir zum Beispiel einen Wert ausgeben lassen, der links von deinem Suchkriterium steht. Insgesamt hast du mit dem XVerweis einfach viel mehr Möglichkeiten und Funktionen.
Besonders toll finde ich die Möglichkeit, sich mehrere Werte ausgeben zu lassen. Wenn du also zum Beispiel die Artikelnummer hast, kannst du dir gleichzeitig alle dazugehörigen Daten ausgeben lassen (Produkt, Preis, Lagermenge und so weiter).
Über den Vergleichsmodus kannst du recht genaue Angaben machen, wie genau das Ergebnis sein soll: 0 steht dabei für eine genaue Übereinstimmung. Du kannst dir aber auch den nächst höheren oder den nächst kleineren Wert ausgeben lassen, falls es keine genaue Übereinstimmung gibt. Und auch eine Platzhaltersuche ist möglich. Zum Vergleich kannst du beim SVerweis nur die Werte wahr oder falsch mitgeben.
Mit dem Suchmodus kannst du die Suchrichtung vorgeben, also zum Beispiel aufsteigend oder absteigend.
Wofür kann man den XVerweis noch verwenden?
Hier noch ein paar weitere Verwendungsbeispiele für den XVerweis:
- Du hast eine Liste mit Telefonnummern deiner Kunden und suchst die Telefonnummer für einen bestimmten Kunden.
- Du könntest auch eine Liste aller Mitarbeiter, Mitarbeiternummern, Abteilungen, Telefonnummern, Standorten und Gehaltsklasse haben und in dieser Tabelle nach bestimmten Informationen zu einem Mitarbeiter suchen. Oder vielleicht möchtest du wissen, welche Mitarbeiter einer bestimmten Gehaltsklasse angehören?
- Im Grunde kannst du die Funktion mit jeder Art von Tabelle über Produkte, Umsätze, Filialen, Einkaufs- und Verkaufspreisen, Verkäufern etc. verwenden.
Fazit
Die XVerweis-Funktion ermöglicht es dir, in einem Zellbereich in einem Excel Arbeitsblatt nach einem bestimmten Wert zu suchen und dir die entsprechenden Einträge aus diesem Bereich ausgeben zu lassen. Auf Englisch heißt diese Funktion XLOOKUP, was soviel wie „nachschlagen“ bedeutet und die Funktion viel besser beschreibt als unser deutsches „X Verweis“.
Du kannst also beispielsweise in einer Tabelle nach einem bestimmten Wert suchen (welcher Artikel gehört zu Artikelnummer 1234). Das Besondere an der Funktion ist aber, dass du dir mehrere Werte ausgeben lassen kannst, also zusätzliche Daten wie etwa der Lagerbestand oder die verkaufte Menge.
Außerdem kannst du in unterschiedliche Richtungen suchen, nicht nur nach rechts, sondern auch nach oben, unten oder nach rechts.
Der XVerweis ist also eine äußerst praktische Funktion, die es jedem ermöglich, sich einfach und schnell bestimmte Werte aus einer Tabelle oder Liste ausgeben zu lassen – ohne manuell danach zu suchen. Dabei ist die Funktion äußerst flexibel und an viele Bedürfnisse anpassbar. Obwohl es sich um eine leistungsstarke Funktion handelt, ist der XVerweis relativ einfach zu erlernen und anzuwenden, selbst für Excel-Nutzer mit grundlegenden Kenntnissen.
