Office-Blog

Dieter Schiecke hat in seinem Blogbeitrag SVERWEIS: Der Klassiker lebt weiter! ein paar neue Seiten des alten Bekannten SVERWEIS vorgestellt. Dabei hat er 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 seinem Beitrag mit dem XVERWEIS umsetzen. Am Ende wird sich dann zeigen, welche Funktion das Rennen macht. RING FREI!

Das sind die zu lösenden Aufgaben

Hier sind die drei Aufgabenstellungen, die im besagten Blogbeitrag mit dem SVERWEIS gelöst wurden:

1. Mehrere zusammenhängende Spalten mit nur einer XVERWEIS-Formel ausgeben
2. Nur ausgewählte Spalteninformationen mit nur einer XVERWEIS-Formel anzeigen
3. Die Ergebnisse nicht horizontal, sondern vertikal anordnen

Wie lässt sich das mit dem XVERWEIS umsetzen?

Hinweis:
Die Funktion XVERWEIS ist 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, steht in diesem Artikel.

1) 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 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. Nur ist hierfür eben der von Dieter Schiecke gezeigte Kniff mit den geschweifte Klammern + manuelle Angabe der Spaltennummern notwendig.

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

2) Nur ausgewählte Spalten mit 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, lassen sich 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 3) 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

3) Und wenn die Ausgabe untereinander und nicht nebeneinander erfolgen soll?

Die nächste Anforderung lautet, die ausgelesenen Daten nicht nebeneinander, sondern untereinander anzuordnen.

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 Excel 365.

=ZUSPALTE(XVERWEIS(B3;tbl_Kunden[KdNr];tbl_Kunden[[Firma]:[Ort]]))

ZUSPALTE 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 in den geschweiften Klammern nur durch ein Semikolon getrennt werden müssen, statt des vorher verwendeten Punkts.

Somit sehen beide Lösungen in der Gegenüberstellung wie aus:

XVERWEIS benötigt ZUSAPLTE als Hilfsfunktion

Auch wenn ich es nur ungern zugebe:
Endstand XVERWEIS gegen SVERWEIS = 1:2

Fazit

• Wie an diesem kleinen – nicht ganz ernst gemeinten – Schlagabtausch gut zu sehen ist, 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.