Kürzlich hat mein Excel-Kollege Dieter Schiecke in einem Gastartikel ein paar neue Seiten des alten Bekannten SVERWEIS vorgestellt. Und dabei die provokante Frage aufgeworfen, ob der SVERWEIS vielleicht doch besser als der XVERWEIS sein könnte.
Diese kleine Herausforderung möchte ich heute gerne annehmen und werde dazu die Beispiele aus Dieters Beitrag mit dem XVERWEIS umsetzen. Am Ende wird sich dann zeigen, welche Funktion das Rennen macht.
Ring frei!
Beispieldatei herunterladen
Hier sind die drei Aufgabenstellungen, die im besagten Artikel mit dem SVERWEIS gelöst wurden:
- Mehrere aufeinander folgende Spalten mit nur einer SVERWEIS-Formel ausgeben
- Nur ausgewählte Spalten mit nur einer SVERWEIS-Formel ausgeben
- Und wenn die Ausgabe untereinander und nicht nebeneinander erfolgen soll?
Wer den Gastartikel zum SVERWEIS verpasst hat, sollte ihn unbedingt hier nachlesen.
Wie würde man diese Aufgaben mit dem XVERWEIS umsetzen?
Hinweis:
Diese Funktion ist erst ab Excel 2021 und in Microsoft 365 verfügbar. Wer eine ältere Version im Einsatz hat, kann den XVERWEIS aber auch mit der kostenlosen Webversion von Excel testen. Wie das geht, erfährst du in diesem Artikel.
Mehrere zusammenhängende Spalten mit nur einer XVERWEIS-Formel ausgeben
Zu der gegebenen Kundennummer in Zelle B3 sollen bestimmte Informationen aus der Datentabelle ausgegeben werden. Dabei handelt es sich um eine formatierte („intelligente“) Tabelle mit den Namen tbl_Kunden.
Aus einer Tabellen sollen mehrere Informationen zurückgegeben werden
Um mehrere Spalten mit nur einer XVERWEIS-Formel zurückliefern zu können, müssen im dritten Argument für die Rückgabematrix einfach nur die gewünschten Spalten in der Datentabelle markiert werden. Für unser kleines Beispiel sieht das dann so aus:
Die gewünschten Ausgabespalten werden einfach markiert
Zur Erinnerung:
Auch der SVERWEIS ist dazu grundsätzlich in der Lage, wie Dieter Schiecke in seinem Beitrag demonstriert hat. Nur ist hierfür eben ein kleiner Kniff notwendig (geschweifte Klammern + manuelle Angabe der Spaltennummern).
Das folgende Bild zeigt die beiden Lösungen im direkten Vergleich:
Gegenüberstellung von XVERWEIS und SVERWEIS
Auch wenn die SVERWEIS-Funktion absolut gesehen kürzer ist, punktet der XVERWEIS mit einfacherer Bedienbarkeit und Transparenz.
Zwischenstand XVERWEIS gegen SVERWEIS = 1:0
Nur ausgewählte Spalten mit nur einer XVERWEIS-Formel ausgeben
Etwas kniffeliger wird es hingegen, wenn die auszugebenden Spalten keinen zusammenhängenden Bereich bilden:
Nicht zusammenhängende Spalten sollen zurückgegeben werden
Hier kommt der XVERWEIS an seine Grenzen und benötigt die Unterstützung einer zweiten Funktion: SPALTENWAHL
SPALTENWAHL gehört zu den neuen dynamischen Array-Funktionen in Microsoft 365. Wie der Name schon erahnen lässt, kann man damit gezielt bestimmte Spalten aus einer Tabelle ausgeben.
=SPALTENWAHL(Matrix;Spaltennummer1;[Spaltennummer2];[Spaltennummer3];...)
Um die beiden Spalten für die Firma (= Spalte 2) und Ort (= Spalte 5) zu erhalten, sähe die Formel so aus:
Die Funktionsweise von SPALTENWAHL
Da es sich hier um eine dynamische Funktion handelt, „läuft“ die Ausgabe automatisch in so viele Zeilen und Spalten über, wie eben gerade benötigt werden.
Verwendet man diese Formel für die Rückgabematrix im XVERWEIS, erhält man das gewünschte Ergebnis:
=XVERWEIS(B3;tbl_Kunden[KdNr];SPALTENWAHL(tbl_Kunden;2;5))
In der direkten Gegenüberstellung macht hier eindeutig der SVERWEIS das Rennen, denn hier wird keine zweite Funktion benötigt:
XVERWEIS benötigt SPALTENWAHL als Hilfsfunktion
Zwischenstand XVERWEIS gegen SVERWEIS = 1:1
Und wenn die Ausgabe untereinander und nicht nebeneinander erfolgen soll?
Und was ist, wenn die Daten nicht nebeneinander, sondern untereinander ausgegeben werden sollen?
Ziel: Vertikale statt horizontale Ausgabe
Auch hierfür reicht der XVERWEIS alleine nicht aus, es muss eine weitere Funktion bemüht werden: ZUSPALTE, eine ebenfalls neue Funktion in Microsoft 365.
=ZUSPALTE(XVERWEIS(B3;tbl_Kunden[KdNr];tbl_Kunden[[Firma]:[Ort]]))
Diese Funktion transformiert den angegebenen Bereich in eine einzige Spalte. Genau wie SPALTENWAHL läuft auch ZUSPALTE dynamisch in so viele Zeilen über, wie für die Ausgabe benötigt werden. Als Quellbereich kann dabei sowohl eine einzelne Zeile als auch eine komplette Tabelle angegeben werden, die dann eben in einer einzigen Spalte ausgegeben wird.
Alternativ wäre auch die alte MTRANS-Funktion denkbar:
=MTRANS(XVERWEIS(B3;tbl_Kunden[KdNr];tbl_Kunden[[Firma]:[Ort]]))
Dieter Schiecke hat hingegen für den SVERWEIS eine deutlich elegantere Lösung aufgezeigt, bei der die Spaltennummern nur durch ein Semikolon getrennt werden müssen, anstatt des bisher verwendeten Punkts.
Somit stellen sich die beiden Lösungen in der Gegenüberstellung wie folgt dar:
XVERWEIS benötigt ZUSAPLTE als Hilfsfunktion
Auch wenn ich es nur ungern zugebe:
Endstand XVERWEIS gegen SVERWEIS = 1:2
Fazit
Wie man an diesem kleinen – nicht ganz ernst gemeinten – Schlagabtausch gut sieht, kann der „alte“ SVERWEIS auch heute noch gegenüber dem modernen XVERWEIS punkten.
Auch wenn letzterer in vielen Fällen deutlich komfortabler und flexibler ist, muss das nicht immer so sein. Und für Anwender älterer Excel-Versionen stellt sich die Frage ohnehin nicht.
Es gibt also kein Richtig oder Falsch, am Ende zählen persönliche Präferenzen, technische Rahmenbedingungen (Excel-Version) und der konkrete Anwendungsfall.
Oder anders gesagt:
Alle Wege führen nach Rom und viele Wege führen in Excel zu einer Lösung.
Was sind deine Erfahrungen und Vorlieben hinsichtlich SVERWEIS und XVERWEIS? Lass es uns in den Kommentaren wissen!
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.
Sehr interessanter Austausch/Gegenüberstellung!!
Ich bevorzuge, seit ich ihn kenne, den XVERWEIS. Mein größtes Problem mit dem SVERWEIS war immer die absolute Spalteneingabe, meines Erachtens eine große Fehlerquelle. Und auch wenn ich dieses Problem zuletzt immer über die Funktion SPALTE umgangen habe, finde ich den XVERWEIS insgesamt einfach viel übersichtlicher 🙂 Trotzdem interessant zu sehen, dass man mit einigen Tricks und Kniffen auch noch Vorteile für den SVERWEIS findet. Die Funktion ZUSPALTE kannte ich übrigens noch nicht. Ich könnte mir vorstellen, dass ich die noch brauchen werde – vielleicht bietet sie sogar die Lösung für die Nutzung von EINDEUTIG über mehrere Spalten…
Hallo Rebekka,
danke für deinen Kommentar, ich persönlich bevorzuge mittlerweile auch meistens den XVERWEIS.
Und es stimmt, mit ZUSPALTE und EINDEUTIG lassen sich eindeutige Werte auch über mehrere Spalten finden, das ist ein ausgezeichneter Anwendungsfall.
Schöne Grüße,
Martin
Hallo zusammen,
das Problem mit der Spaltenangabe im SVERWEIS lässt sich auch durch VERGLEICH umgehen. So lässt sich eine SVERWEIS-Formel komplett dynamisch halten und man kann sie einmal eingeben und dann für alle Zeilen und Spalten kopieren.
Im Anwendungsfall 1 wäre das dann folgende Formel:
=SVERWEIS($B$3;tbl_Kunden1;VERGLEICH(C$16;tbl_Kunden1[#Kopfzeilen];0);FALSCH)
Hallo Toshi,
vielen Dank für diese Ergänzung, das umgeht das Problem sehr elegant.
Schöne Grüße,
Martin
Bitte noch den kleinen Fehler korrigieren: In der Zeile direkt über der 5. Grafik muss es heißen „Ort (= Spalte 5)“. (nicht 3) Dann passt’s.
Danke auch für den Tipp!
Danke für den Hinweis, Axel. Ist jetzt korrigiert.
Schöne Grüße,
Martin
Hallo,
ich finde die SVERWEIS trotzdem unpraktisch, da die Suchspalte immer links von der Rückgabematrix stehen muss. Das schränkt die Formellösung ein.
Hallo Zafer,
ja, das sehe ich grundsätzlich genauso. Mehr Komfort bietet eindeutig der XVERWEIS.
Schöne Grüße,
Martin