Seit Excel 2021 steht mit der XVERWEIS-Funktion ein höchst leistungsfähiger Nachfolger für den gleichermaßen geliebten wie gehassten SVERWEIS zur Verfügung. Im heutigen Artikel zeige ich dir einen kleinen Trick, wie der XVERWEIS in 2 Richtungen funktioniert. Und stelle dir in dem Zusammenhang einen besonderen Operator vor, den du bisher vielleicht noch nicht kanntest.
Und so geht’s:
Beispieldatei herunterladen
Mehrere Spalten ausgeben
Was viele Anwender nicht wissen ist, dass der XVERWEIS nicht nur einen einzelnen Wert zurückliefern kann, sondern gleich eine ganze Reihe. Sehen wir uns dazu meine kleine Umsatztabelle aus der Beispieldatei an. Dort sind die Umsätze für verschiedene Verkäufer in verschiedenen Monaten aufgeführt:
Beispieltabelle mit Umsätzen nach Verkäufer und Monat
Möchte man den Umsatz für den Verkäufer Müller im Monat März mit dem XVERWEIS ausgeben, würde das so aussehen:
=XVERWEIS(B12;A4:A9;D4:D9)
Umsatz für einen ausgewählten Verkäufer im Monat März
Der Verkäufer steht in diesem Beispiel in Zelle B12 und für die Umsätze wird direkt auf die Spalte D verwiesen. Soweit die übliche Vorgehensweise.
Man könnte aber auch sämtliche Umsätze für den gewählten Verkäufer ausgeben:
=XVERWEIS(B16;A4:A9;B4:G9)
Sämtliche Umsätze für den ausgewählten Verkäufer
Es wird für die Rückgabematrix also nicht eine einzelne Spalte angegeben, sondern der komplette Datenbereich mit allen Umsätzen. Wie man in dem Bild oben sieht, läuft dann die Ausgabe von XVERWEIS dynamisch in die benachbarten Zellen über.
Mehrere Zeilen ausgeben
Genauso funktioniert es natürlich, wenn man den XVERWEIS in die andere Richtung einsetzt und die Umsätze für einen bestimmten Monat ausgibt:
=XVERWEIS(J16;B3:G3;B4:G9)
Sämtliche Umsätze für den ausgewählten Monat
Hier ist die Suchmatrix die Zeile mit den Monatsnamen, die Rückgabematrix ist wieder der komplette Datenbereich.
Den Umsatz am Schnittpunkt bestimmen
Und damit wären wir auch schon beim letzten Schritt. Es sollen nämlich sowohl der Verkäufer als auch der Monat dynamisch ausgewählt werden können. Alles, was dafür nötig ist, haben wir bereits in den beiden vorhergehenden Schritten erledigt.
Wir müssen also nur die beiden XVERWEIS-Funktionen in einer einzigen Formel zusammensetzen:
XVERWEIS(B16;A4:A9;B4:G9) XVERWEIS(J16;B3:G3;B4:G9)
Der Umsatz an der Schnittstelle von Verkäufer und Monat
Das Besondere daran ist, dass die beiden Funktionen durch ein Leerzeichen getrennt werden. Bei dem Leerzeichen handelt es sich um den sogenannten Schnittmengenoperator. Über diesen relativ unbekannten Operator hatte ich schon vor langer Zeit im Excel-Quickie Nr. 11 geschrieben und dort gezeigt, wofür man dies Besonderheit sonst noch nutzen kann.
Alternativ hätte man die beiden XVERWEIS-Funktionen auch miteinander verschachteln können:
XVERWEIS(B16;A4:A9;XVERWEIS(J16;B3:G3;B4:G9))
Alternative Lösung
Hier wird innerhalb des ersten XVERWEIS als Rückgabematrix einfach der zweite XVERWEIS angegeben. Diese Formel ist zwar etwas kürzer als die erste Variante, aber zumindest am Anfang etwas schwerer zu lesen.
Wie man an diesem Beispiel sieht, gibt es meistens mehrere Lösungen für ein Problem. Welche davon man umsetzt, bleibt dem persönlichen Geschmack überlassen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
wünsche dir noch ein frohes und gesundes Neues Jahr. Schön wieder von dir zu hören.
Ich muss gestehen ich arbeite immer noch mehr mit dem SVERWEIS als mit dem XVERWEIS obwohl er viele Vorteile hat.
Ich werde den XVERWEIS in Zukunft wohl öfters in meiner Arbeit einbinden.
Viele Grüße, Harald.
Hallo Harald,
dank, das wünsche ich dir auch!
Ich kann den XVERWEIS nur wärmstens empfehlen, in den allermeisten Fällen ist er dem SVERWEIS überlegen. Aber ich weiß aus eigener Erfahrung, dass man alte Gewohnheiten nicht so schnell ändert…
Schöne Grüße,
Martin
Sehr schön, mal wieder den Schnittmengenoperator in Erinnerung gerufen zu bekommen, vielen Dank dafür! Den verschachtelten XVERWEIS wie im zweiten Beispiel habe ich schon häufiger angewendet, wobei ich den Kollegen zuliebe doch meist wieder auf die Kombination INDEX und VERGLEICH zurückgreife 😉
Ich versuche diesmal, die Schnittmenge im „aktiven Wortschatz“ zu behalten und vielleicht mal im Controlling einzusetzen, es gibt sicher hin und wieder eine sinnvolle Möglichkeit
Hallo Julius,
dankeschön 🙂
INDEX und VERGLEICH sind auch eine gute Variante. Wie fast immer führen viele Wege ans Ziel.
Schöne Grüße,
Martin