XVERWEIS: Das kann die Excel-Funktion

Excel hat mit XVERWEIS eine relativ neue Funktion, die im August 2019 erstmals als Beta-Feature integriert wurde und momentan nur in Microsoft 365 zur Verfügung steht (Stand: Juli 2021). Wenn Sie zu dieser Nutzergruppe gehören und regelmäßig mit großen Datensammlungen in Excel arbeiten, lohnt es sich, diese Formel zu beherrschen. In unserer Anleitung erklären wir anhand von Beispielen, wie Sie sich die Datensuche mit dem XVERWEIS erheblich erleichtern und wann die Funktion normalerweise zum Einsatz kommt.

Was ist XVERWEIS?

Der XVERWEIS in Excel gehört zur Familie der Nachschlage- und Verweisfunktionen. Er stellt eine der nützlichsten Funktionen in der beliebten Tabellenkalkulation von Microsoft dar. Mit XVERWEIS lässt sich auf einfachste Weise nach bestimmten Einträgen in einem Zellbereich suchen. Dabei werden die Einträge in einem vorher festgelegten Zellbereich aufbereitet wiedergegeben. Klingt bekannt? Nach diesem Prinzip funktioniert auch der SVERWEIS. Mit dem flexibleren Excel-XVERWEIS können Sie jedoch nicht nur einen, sondern gleich mehrere Einträge nachschlagen. Außerdem ist es möglich, Werte senkrecht und waagerecht zu suchen.

Was heißt das in der Praxis? Stellen Sie sich vor, Sie haben eine digitale Kundendatenbank als Excel-Datei und suchen nach der Anschrift und Telefonnummer einer bestimmten Person. Mit dem XVERWEIS können Sie nun anhand des Namens nach den zugehörigen Einträgen suchen und sich die gewünschten Informationen sofort anzeigen lassen. Dabei ist es völlig egal, ob sich die gesuchten Werte in einer Spalte, Zeile oder in einer Tabelle auf einer anderen Seite befinden. Somit ersetzt der XVERWEIS nicht nur den SVERWEIS, sondern auch den WVERWEIS.

Fakt

Auf Englisch heißt die XVERWEIS-Funktion XLOOKUP, was „nachschlagen“ enthält und die Bedeutung etwas besser beschreibt.

XVERWEIS: Typische Anwendungsfälle für die Funktion

Bevor wir die Syntax der Excel-XVERWEIS-Funktion und ihre Anwendung anhand von ein paar Beispielen erläutern, hier ein kurzer Überblick über die häufigsten Anwendungsfälle:

  • Nachschlagen eines einzelnen Eintrags
  • Nachschlagen mehrerer Einträge auf einmal
  • Durchsuchen von Zeilen
  • Suchen nach genauer Übereinstimmung und nach nächstkleineren oder -größeren Einträgen
  • Verwenden von Platzhalterzeichen im Suchkriterium

XVERWEIS in Excel: Wie sieht die Syntax aus?

Um effizient mit dem XVERWEIS zu arbeiten, sollten Sie sich zunächst mit den einzelnen Parametern vertraut machen. Jede Excel-Funktion hat eine bestimmte Syntax, die spezifische Parameter erfordert. Selbst bei kleinsten Abweichungen kann es zu einem falschen Ergebnis oder einer Fehlermeldung kommen. Für den XVERWEIS gilt folgende Syntax:

XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])

Was genau die einzelnen Parameter bedeuten, fasst die nachfolgende Tabelle zusammen. Die mit einem Sternchen versehenen Argumente sind optional:

Suchkriterium

Der Eintrag, nach dem Sie suchen; dabei kann es sich um Text, Zahlen oder Platzhalterzeichen handeln.

Suchmatrix

Der Bereich oder das Array, in dem Excel nach den gewünschten Informationen suchen soll

Rückgabematrix

Der Bereich oder das Array, in dem Excel die gewünschten Informationen wiedergeben soll

wenn_nicht_gefunden *

Falls Excel keine gültige Übereinstimmung findet, kann ein von Ihnen bereitgestellter Text oder Wert zurückgegeben werden. Andernfalls zeigt das Programm die Meldung „#N/A“ an.

Vergleichsmodus *

Mithilfe des Vergleichsmodus können Sie einen Übereinstimmungstyp angeben.

Suchmodus *

Hiermit bestimmen Sie, wo die Suche beginnen soll („1“ für das erste und „-1“ für das letzte Element) oder starten eine Binärsuche.

Hinweis

Parallel zum neuen XVERWEIS gibt es auch den XVERGLEICH, der die altbewährte VERGLEICH-Funktion in Excel mit erweiterten Suchfunktionen ersetzt.

Der XVERWEIS an Beispielen erklärt

Beispiel 1: Standardsuche mit einem Rückgabewert

Für unser erstes Beispiel verwenden wir nur die drei Pflichtangaben „Suchkriterium“, „Suchmatrix“ und „Rückgabematrix“. Unsere exemplarische Excel-Tabelle, deren Inhalte in den Zellen B2 bis D10 liegen, bildet verschiedene Länder und die passenden Vorwahlen ab. Mit der XVERWEIS-Funktion wollen wir nun herausfinden, welche telefonische Vorwahl das Land Ungarn hat. Das Ergebnis soll im Bereich G2 angezeigt werden (markieren Sie hierfür einfach die Zelle). Die erforderliche Formel lautet wie folgt:

=XVERWEIS(F2;B2:B10;D2:D10)

F2 ist das Suchkriterium, in diesem Fall „Ungarn“. Die Suchmatrix besteht aus der Vorwahl-Spalte und erstreckt sich von B2 bis B10. Die Rückgabematrix ist die Spalte, in der der zugehörige Wert stehen soll, besteht im Beispiel also aus dem Bereich D2 bis D10. Sobald Sie auf die Eingabetaste drücken, wird Ihnen die gesuchte Vorwahl angezeigt.

Tipp

Sie wollen den XVERWEIS in Excel nicht per Hand eingeben? Wählen Sie einfach in der Menüleiste die Punkte „Formeln“ und „Funktion einfügen“ aus und suchen Sie den XVERWEIS in der Auflistung. Wählen Sie unter „Kategorie auswählen“ die Option „Alle“ aus, falls Sie zunächst keinen Eintrag finden sollten.

Beispiel 2: Standardsuche mit zwei Rückgabewerten

Im nächsten Beispiel soll Excel per XVERWEIS nicht nur die Vorwahl, sondern auch die zugehörige Abkürzung des ausgewählten Landes ausgeben. Hierfür gilt es, die Rückgabematrix zu erweitern, damit Excel beide Ergebnisse finden kann:

=XVERWEIS(F2;B2:B10;C2:D10)
Tipp

Gibt es in der Tabelle exakte Übereinstimmungen, zeigt die XVERWEIS-Funktion nur einen der Treffer an. Um fehlerhafte Ergebnisse zu vermeiden, können Sie Duplikate vor Ihrer Suche mit der Duplikate-entfernen-Funktion von Excel beseitigen.

Beispiel 3: Suche mit dem optionalen Argument wenn_nicht_gefunden

Die Formel in unserem nächsten Beispiel enthält nicht nur die drei Pflichtangaben, sondern auch das optionale Argument wenn_nicht_gefunden. Mit diesem Zusatz können Sie angeben, welcher Text bei einer Nichtübereinstimmung ausgeworfen werden soll. Für das Beispiel definieren wir den Text: „Ungültige Angabe“ als Ausgabetext und suchen nach dem Land „Portugal“, das nicht Teil unseres Excel-Datensatzes ist.

=XVERWEIS(F2;B2:B10;C2:D10;"Ungültige Angabe")

Beispiel 4: Suche mit drei optionalen Argumenten

In diesem Beispiel verwenden wir neben den drei Pflichtangaben die optionalen Argumente wenn_nicht_gefunden, Vergleichsmodus und Suchmodus.

In der Beispieltabelle arbeiten wir zu diesem Zweck mit verschiedenen Steuersätzen und ihren zugehörigen Einkommensgrenzen. Mit der passenden XVERWEIS-Formel wollen wir nach der Eingabe der Einkünfte eines Mitarbeiters in Zelle E2 den richtigen Steuersatz in F2 präsentiert bekommen.

=XVERWEIS(E2;C2:C7;B2:B7;0;1;1)

Für den Parameter wenn_nicht_gefunden haben wir in diesem Fall den Wert „0“ angegeben. Bei einer Nichtübereinstimmung wird so „0“ statt „#/NA“ angezeigt. Da Sie nicht nach einer exakten Übereinstimmung suchen, ist für den Vergleichsmodus der Wert „1“ gesetzt, was bedeutet, damit das nächstgrößere Element angezeigt wird. Der Suchmodus ist ebenfalls auf „1“ festgelegt, damit Excel die Tabelle vom ersten bis zum letzten Element durchsucht.

Beispiel 5: Geschachtelte XVERWEIS-Funktion

Abschließend verwenden wir eine geschachtelte Excel-XVERWEIS-Funktion. Diese bietet die Möglichkeit, gleichzeitig eine vertikale und horizontale Übereinstimmung durchzuführen – die eigentliche Besonderheit, die den XVERWEIS ausmacht. Im Beispiel suchen wir nach dem Umsatz des Verkäufers mit der ID „1002“ im Monat April. Die Formel lautet wie folgt:

=XVERWEIS(B2;B6:B11;XVERWEIS(C2;C5:H5;C6:H11))
Hinweis

Soll sich die Formel beim Kopieren nicht ändern, stellen Sie einfach einen absoluten Zellbezug, indem Sie vor der Spaltenangabe und Zeilenangabe ein Dollarzeichen ($) setzen.


Halt! Wir haben noch etwas für Sie!

Eigene E-Mail-Adresse
schon ab 1,- €/Mon.
Professionell: 100% werbefrei
Firmen- oder Private E-Mail-Adresse inkl. Domain
E-Mail-Weiterleitung, Spam- und Virenschutz