Vereinfachter (S-)Verweis - Anwendung der Formel XVERWEIS

Die Formel "SVERWEIS" ist eine der bekanntesten in Excel - da sie mächtige Überprüfungen durchführen und Ergebnisse liefern kann, aber eben auch manch einem Benutzer Probleme bereitet. Diverse Angaben und Regeln müssen erfüllt werden, damit man zum gewünschten Resultat gelangt. Ein Nachteil der Formel ist zudem, dass die Ergebnisspalte rechts von dem Suchwert stehen muss (siehe hierzu eine ebenfalls valide Lösung SVERWEIS nach links).

In diesem Beitrag wird eine neuere Formel "XVERWEIS" vorgestellt. Diese Funktion ist deutlich intuitiver, wie die ersten drei Argumente der Syntax zeigen (nur diese drei sind notwendig, die Angaben in den eckigen Klammern können ignoriert werden):

XVERWEISSyntax

Die erste Angabe verlangt analog dem SVERWEIS ein Suchkriterium - nach welchem Wert soll gesucht werden? Die Suchmatrix ist z.B. die Spalte, in welcher das Suchkriterium steht. Beim SVERWEIS wird es bereits an dieser Stelle kompliziert, da die Matrix sowohl das Suchkriterium wie auch das Resultat enthalten muss. In dieser Formel ist das dritte Argument die Rückgabematrix (z.B. die Spalte, in der das Resultat steht). Wie erwähnt hat die Formel drei weitere Argumente in eckigen Klammern - diese sind fakultativ und bieten weitere Möglichkeiten, auf die wir hier nicht eingehen.

Schauen wir uns ein konkretes und simples Beispiel an. Eine Tabelle enthält die Regionalleiter und deren E-Mail-Adressen. In Spalte F suchen wir nach der Region, um die E-Mail zu erhalten:

XVERWEISSpalten1

Wechseln wir das Suchkriterium von "Nordeuropa" zu "Nordamerika", erhalten wir die entsprechende E-Mail-Adresse.

XVERWEISSpalten2

Dies ist die effektive Formel in der Zelle "G3":

=XVERWEIS(F3;B3:B8;D3:D8)

Selbstverständlich könnte man auch hier Werte "absolut" setzen (Dollarzeichen), damit man die Formel vereinfacht kopieren könnte - hier wird allerdings nur ein einziger Wert gesucht.

Beachte zudem: Mit der Formel ist nicht nur der "SVERWEIS" (Spalten) möglich, sondern auch der "waagrechte" Verweis (WVERWEIS). Die oben aufgeführte Tabelle habe ich transponiert, um den XVERWEIS auf ein solches Format anzuwenden. Unten wird die "gedrehte" (transponierte) Tabelle sowie die angepasste XVERWEIS-Formel dargestellt. Um die Abbildung übersichtlicher zu gestalten, habe ich gewisse Spalten (D bis G) gruppiert und ausgeblendet.

XVERWEISZeilen1

Die Formel in Zelle "C7" lautet:

=XVERWEIS(B7;C2:H2;C4:H4)

SVERWEIS verbessern

In diesem Beitrag möchte ich Dir zeigen, wie Du Deinen SVERWEIS verbessern kannst. Bei etwaigen Änderungen in der Datenquelle soll unsere Formel weniger fehleranfällig sein. Wir fokussieren uns dabei auf den sogenannten "Spaltenindex". Zum Anfang hier einmal die einzelnen Argumente der Formel "SVERWEIS" im Überblick:

Spaltenindex

Der Spaltenindex ist also das dritte Kriterium. Zur Illustration verwenden wir die nachfolgende Auflistung von Tieren in unserem Zoo:

ZooDaten

Mit einem SVERWEIS können wir demzufolge die Anzahl eines bestimmten Tieres abrufen. In dieser Formel würde eine "2" als Spaltenindex angegeben. Soweit so gut. Falls nun jedoch zwischen den Spalten "Tier" und "Anzahl" eine weitere Spalte "Standort" eingefügt wird, gibt unser SVERWEIS leider nicht mehr die Anzahl sondern eben diesen Standort aus - der Spaltenindex wird nicht automatisch auf 3 geändert. Wir können uns in dieser Hinsicht absichern und den SVERWEIS verbessern, indem wir beim Spaltenindex die Formel "SPALTEN" anwenden. Konkret: Als Matrix (Datenquelle) wird beispielsweise "A:B" verwendet. Demnach lautet die Formel für den Index "SPALTEN(A:B)". Fügst Du nun eine neue Spalte in der Mitte der Datenquelle ein, wird der Bezug direkt auf "A:C" erweitert und Dein SVERWEIS liefert stets den gewünschten Output.

P.S.: Beim WVERWEIS verwendest Du ganz simpel die Formel "=ZEILEN".

SVERWEIS nach links

Mit der Formel "SVERWEIS" wird ja bekanntlich die erste Spalte Deiner Matrix nach Deinem Suchkriterium durchsucht. Sobald ein Treffer gefunden wurde, wird der Wert aus derjenigen Spalte laut Deines "Spaltenindex" ausgegeben. Schauen wir uns hierzu ein konkretes Beispiel an:

SVERWEIS1

Suchen wir also nach "Eisbär" und verwenden als Spaltenindex die 2, wird "Arktis" als Ergebnis unserer Formel angegeben. Die Formel "SVERWEIS" verläuft also stets nach rechts. In diesem Beitrag möchten wir jedoch eine Möglichkeit anschauen, wie wir beispielsweise nach "Arktis" suchen können und dann "Eisbär" als Resultat erhalten - also einen "SVERWEIS nach links"!

Folgendes können wir im Vornherein definieren: Unsere Suchspalte ist in diesem Fall B, die Ergebnisspalte ist A. Unser Suchbegriff lautet "Arktis". Wir verwenden nun jedoch nicht die Formel "SVERWEIS" (Einen negativen Spaltenindex wie beispielsweise -1 wird leider nicht toleriert.) sondern die flexiblere Version "=INDEX". Anders als bei der herkömmlichen Formel müssen wir zuerst die Ergebnisspalte definieren - "A:A". Für die Ermittlung der korrekten Zeile verwenden wir "VERGLEICH("Arktis";B:B;0)". Zusammengesetzt und vollständig lautet die Formel demnach:

=INDEX(A:A;VERGLEICH("Arktis";B:B;0))

... und das Resultat: "Eisbär"! Mittels diesen beiden Formeln kannst Du also auch eine Suche nach links durchführen.

Hattest Du schon einmal nach dieser Methode gesucht oder gar Deine Tabelle umgestellt, damit Du nach wie vor den "SVERWEIS" anwenden konntest? Fragen oder Anregungen kannst Du wie immer sehr gerne in die Kommentare schreiben.