Den XVERWEIS ausreizen (2024)

Wer Excel aus Microsoft 365/Office 365 oder das brandneue Excel 2021 einsetzt, hat vermutlich schon Bekanntschaft mit der XVERWEIS-Funktion gemacht oder zumindest davon gehört. Es lohnt sich unbedingt, sich mit dem Nachfolger des berühmt-berüchtigten SVERWEIS zu beschäftigen, bringt er doch wesentliche Verbesserungen mit sich.

Darüber hatte ich auch vor längerer Zeit schon im Artikel Goodbye SVERWEIS, willkommen XVERWEIS! geschrieben.

Wenn du also schon mit XVERWEIS gearbeitet hast, dann zeige ich dir heute mit ein paar Spezialfällen, wie man noch viel mehr herausholen kann. Oder du kommst auf den Geschmack, dich endlich mit dieser tollen Funktion zu beschäftigen und den SVERWEIS endgültig zu begraben.

Und so geht’s:

Wichtiger Hinweis zu Beginn:
Falls du noch Excel 2019 oder älter einsetzt, steht dir diese Funktion leider nicht zur Verfügung. Weiterlesen darfst du natürlich trotzdem 😉

Spezialfall 1: XVERWEIS in zwei Richtungen

Ich habe eine klein Umsatztabelle vorbereitet, die für sechs Verkäufe die Umsätze für die Monate Januar bis Juni enthält. Nun möchte ich über zwei Eingabefelder einen Monat und einen Verkäufer festlegen und mir den dazu passenden Umsatz ausgeben lassen (die Beispieldatei kannst du dir hier herunterladen):

Der normale XVERWEIS mit nur einem Suchkriterium funktioniert so:
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn nicht gefunden]; [Vergleichsmodus]; [Suchmodus])
Die letzten drei Parameter sind optional und für unsere Zwecke nicht erforderlich.

Würde ich nur nach einem Verkäuferumsatz für den, sagen wir mal, März suchen, wäre die Formel bezogen auf mein Beispiel:
=XVERWEIS(E13;A4:A9;D4:D9)

Wie schafft man es nun, dass die Suchmatrix variabel ist?

Ganz einfach: Über einen zweiten XVERWEIS im XVERWEIS!

Spezialfall 2: Dynamische Summenspalten über alle Zeilen

Im Normalfall gibt XVERWEIS einen einzelnen Wert zurück, so wie auch im vorhergehenden Beispiel. In Kombination mit der SUMME-Funktion lässt sich damit aber auch ganze Zellenbereiche zurückliefern.

In diesem Beispiel möchte ich die Summe der Umsätze aller Verkäufer zwischen zwei frei wählbaren Monaten ausgeben:

Auch das ist kein Problem. Benötigt werden wieder zwei XVERWEIS-Funktionen, die innerhalb der SUMME-Funktion zum Einsatz kommen und dort den „von..bis“-Bereich bestimmen:

Der erste XVERWEIS liefert sozusagen die Von-Spalte, der zweite XVERWEIS die Bis-Spalte, getrennt durch einen Doppelpunkt. Das Geniale ist, die Monate müssen in den beiden Eingabezellen nicht einmal in der richtigen Reihenfolge angegeben werden. Von Februar bis April liefert das gleiche Ergebnis wie von April bis Februar!

Spezialfall 3: Dynamische Summenspalten über eine Zeile

Setzen wir gleich noch eins drauf. Zu den frei wählbaren Anfangs- und Endmonaten möchte ich jetzt auch noch einen bestimmten Verkäufer angeben:

Die Formel wird schon etwas umfangreicher und sieht jetzt so aus:

Das heißt, die beiden Von..Bis-Bereiche innerhalb der SUMME-Funktion werden jeweils durch verschachtelte XVERWEIS-Funktionen dynamisiert. Jeder dieser verschachtelten XVERWEISE ist im Grunde identisch mit dem Beispiel aus Spezialfall 1. Nur dass hier eben zwei davon zum Einsatz kommen.

Spezialfall 4: Dynamische Summenspalten und dynamische Zeilen

Um dem ganzen die Krone aufzusetzen, soll jetzt zum Monatsbereich auch noch ein Verkäufer-Bereich angegeben werden.

Du ahnst es vermutlich schon und musst jetzt ganz tapfer sein. Die Formel wird nämlich ein ziemliches Monster:

Ich gebe zu, dass diese Formel nicht ganz einfach und auch nicht für den täglichen Gebrauch geeignet ist. Aber wir besprechen hier schließlich Spezialfälle und da ist es doch irgendwie beruhigend zu wissen, dass es ginge, falls man es bräuchte 🙂

Welche interessanten Anwendungsmöglichkeiten für den XVERWEIS fallen dir noch ein?
Lass es uns in den Kommentaren wissen!