Die neue Funktion XVERWEIS vereint die Funktionalitäten von SVERWEIS und WVERWEIS in einer Funktion. Und darüber hinaus kann zusätzlich von rechts nach links, oder aber auch von unten nach oben gesucht werden. Damit hat die neu eingeführte Funktion das Potenzial dem ebenso beliebten wie bekannten SVERWEIS den Rang abzulaufen.
Einen Haken gibt es bei der Sache allerdings. Die Funktion ist nur in den neuesten Excelversionen integriert. Im Einzelnen sind das Microsoft 365, Excel für Microsoft 365, für Mac Excel für das Web, Excel 2021, Excel 2021 für Mac, Excel für iPad, Excel für iPhone, Excel für Android-Tablets und Excel für Android-Smartphones. In den Versionen Excel 2016 und Excel 2019 wird die neue Funktion nicht unterstützt. Erhalten Sie eine Exceldatei, die mit einer neueren Excelversion erstellt wurde wird die XVERWEIS-Funktion möglicherweise in dieser Form angezeigt: _xlfn.XLOOKUP. Der Präfix _xlfn. wird immer dann angezeigt, wenn die Arbeitsmappe eine Funktion enthält, die in der aktuell verwendeten Excelversion nicht unterstützt wird.
Aufgrund dieser Einschränkungen empfiehlt sich die Funktion allerdings nur, wenn alle Nutzer der Arbeitsmappe mit einer unterstützenden Excelversion arbeiten!
Teil 1: Wissenswert: Sverweis Funktion und Makro Alternative
Teil 2: Besser als SVERWEIS: Die XVERWEIS Funktion
Funktionsweise und Syntax der Funktion: XVERWEIS
Mit der XVERWEIS-Funktion kann sowohl in Spalten, als auch in Zeilen nach Werten gesucht werden. Für die Funktion sind mindestens ein Suchkriterium, die Suchmatrix und die Rückgabematrix erforderlich. Die weiteren Bestandteile der Funktion sind optional.
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])Suchkriterium = Zelle der den Suchbegriff beinhaltet.
Suchmatrix = Array oder Spalten-/Zeilenbereich in dem das Suchkriterium gesucht werden soll.
Rückgabematrix = Gibt den Array oder Bereich an, der zurückgegeben wird.
[wenn_nicht_gefunden] (Optional) = Hier kann die Fehlermeldung #N/A abgefangen werden, wenn das Suchkriterium nicht gefunden wurde. (Empfohlen!)
[Vergleichsmodus] (Optional) =
- 0: genaue Übereinstimmung. Wenn keine gefunden wird, wird “#N/V” zurückgegeben. Dies ist die Standardeinstellung.
- -1: genaue Übereinstimmung. Wenn keine gefunden wurde, geben Sie das nächstkleinere Element zurück.
- 1: genaue Übereinstimmung. Wenn keine gefunden wurde, geben Sie das nächstgrößere Element zurück.
- 2: eine Platzhalterübereinstimmung, wobei *, ? und ~ eine Sonderbedeutung haben.
[Suchmodus] (Optional) =
- 1: Führen Sie eine Suche durch, die beim ersten Element beginnt. Dies ist die Standardeinstellung.
- -1: Führen Sie eine umgekehrte Suche durch, die beim letzten Element beginnt.
- 2: Führen Sie eine Binärsuche durch, die darauf basiert, dass eine Suchmatrix in aufsteigender Reihenfolge sortiert ist. Ist diese nicht so sortiert, werden ungültige Ergebnisse zurückgegeben.
- -2: Führen Sie eine Binärsuche durch, die darauf basiert, dass eine Suchmatrix in absteigender Reihenfolge sortiert ist. Ist diese nicht so sortiert, werden ungültige Ergebnisse zurückgegeben.
Anwendungsbeispiel für die XVERWEIS-Funktion:
Den klassischen SVERWEIS können Sie sehr einfach über den XVERWEIS ersetzen. Im nachfolgenden Beispiel wird das Suchkriterium mit exakter Übereinstimmung in Spalte B gesucht um den passenden Wert in Spalte F auszugeben. Um die Funktion übersichtlich zu gestalten durchsuchen wir jeweils die gesamte Spalte.
Das lässt sich mit folgendem SVERWEIS realisieren:
=SVERWEIS([Suchkriterium];B:F;5;FALSCH)Die Zahl 5 beinhaltet die Spalte ausgehend von der Suchmatrix B:F, die ausgegeben werden soll. (1 = B, 2 = C, 3 = D, 4 = E, 5 = F)
Die Angabe „FALSCH“ gibt an, dass es eine exakte Übereinstimmung mit dem Suchkriterium geben muss.
Die alternative Lösung mit dem neuen XVERWEIS sieht dann wie folgt aus:
=XVERWEIS([Suchkriterium];B:B;F:F)
In diesem Beispiel liegt der wesentliche Unterschied zwischen den Funktionen nur darin, dass der XVERWEIS zwei getrennte Bereiche für Suchmatrix und Rückgabematrix benötigt. Es entfällt dadurch die Angabe der Rückgabespalte. Auch die exakte Übereinstimmung muss nicht definiert werden, da dies die Standardoption beim XVERWEIS darstellt.
Was kann der XVERWEIS zusätzlich leisten?
Der SVERWEIS sucht immer von links nach rechts. Der XVERWEIS kennt diese Einschränkung zum Glück nicht. Sie können auch von rechts nach links suchen. Für das obige Beispiel könnte das Suchkriterium zum Beispiel auch in Spalte F liegen.
=XVERWEIS([Suchkriterium];J:J;F:F)Eine weitere Möglichkeit besteht darin mehr als ein Ergebnis zu erhalten. Sie können zum Suchkriterium (z.B. Spalte B) die dazugehörigen Werte bzw. Zellbereiche ausgeben oder für eine Weiterverarbeitung im Array nutzen.
=XVERWEIS([Suchkriterium];B:B;E:I)HINWEIS: Zum Suchkriterium werden hier die Werte aus Spalte E bis I ermittelt. Da diese fünf Werte nicht in einer Zelle dargestellt werden können, werden die benachbarten Zellen mit den weiteren Werten gefüllt. Hierbei ist entsprechend zu beachten, dass diese Zellen nicht bereits genutzt sein dürfen.
Alternative für den WVERWEIS
Analog kann auch der WVERWEIS durch einen XVERWEIS ersetzt werden. Im Beispiel wird das Suchkritierum in Zeile 1 gesucht und der dazugehörige Wert aus Zeile 5 ausgegeben.
=WVERWEIS([Suchkriterium];1:5;5;FALSCH)Der WVERWEIS kann durch folgenden XVERWEIS ersetzt werden.
=XVERWEIS([Suchkriterium];1:1;5:5)
Auch hier gibt es wieder die Möglichkeit von unten nach oben zu suchen. Wenn das Suchkriterium in Zeile 6 steht ergibt das entsprechend folgende Formel:
=XVERWEIS([Suchkriterium];38:38;5:5)Verschachtelter XVERWEIS für die Suche nach einem Schnittpunkt
Der XVERWEIS eignet sich auch perfekt um SVERWEIS und WVERWEIS zu kombinieren und den Zellwert des Schnittpunkts zu ermitteln. Im nächsten Beispiel haben wir zwei Suchkriterien in Spalte A und Zeile 1. Mit zwei XVERWEIS-Funktionen die ineinander verschachtelt sind können wir nun den Wert des Schnittpunkts ermitteln und ausgeben.
=XVERWEIS([Suchkriterium Zeile];B2:B37;XVERWEIS([Suchkriterium Spalte];B1:I1;B2:I37))
Die Muster-Arbeitsmappe enthält die obigen Beispiele zum nachvollziehen und testen. Bitte beachten Sie den Hinweis am Anfang des Beitrags, dass die Funktion nur in den Excelversionen funktioniert, die diese Version auch unterstützen.
xverweis_beispiele.xlsx