Überblick
Mit der Excel XVERWEIS Funktion kann innerhalb eines Bereiches nach einem Kriterium gesucht werden, um anschließend den gewünschten Wert aus einem weiteren Bereich auszugeben. Dabei ist sowohl die Suchmatrix als auch die Rückgabematrix voneinander nicht abhängig und kann individuell definiert werden. Die XVERWEIS Funktion kann ein Suchkriterium sowohl senkrecht als auch waagrecht finden, und ersetzt somit sowohl den SVERWEIS als auch den WVERWEIS.
Die XVERWEIS Formel kann aktuell leider nur in Office 365 und Office 2021 aufgerufen werden. Zu einer Implementierung in älteren Office-Versionen wird es voraussichtlich nicht kommen. Falls eine ältere Excel Version im Einsatz ist, kann auf andere Methoden zurückgegriffen werden.
Inhaltsverzeichnis
- XVERWEIS Grundlagen
- XVERWEIS an einem Beispiel
- XVERWEIS mit mehreren Suchbegriffen
- XVERWEIS mit mehreren Ergebnissen
Syntax
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])
Argumente
Das Suchkriterium ist der Wert, nach welchem gesucht wird. Hierbei kann es sich um eine Zahl oder einen Text in Kombination mit einem Platzhalter handeln.
Die Suchmatrix ist die Matrix, in welcher nach dem Suchkriterium gesucht werden soll. Da der XVERWEIS eine separate Ausgabe hat, darf die Suchmatrix maximal eine Spalte betragen.
Die Rückgabematrix definiert den Ausgabebereich. Hier können mehrere Zeilen und Spalten eingetragen werden.
Beim XVERWEIS ist es nicht relevant, ob die Suchmatrix und die Rückgabematrix waagrecht oder senkrecht angeordnet sind. Daher kann der Bereich sowohl eine Zeile, als auch eine Spalte darstellen.
wenn_nicht_gefunden ergänzt die Ausgabe durch eine Aussage, die anstelle von einem leeren Wert ausgegeben wird. Dies kommt zum Einsatz, wenn der XVERWEIS keinen korrespondierenden Wert gefunden hat. In älteren Versionen dieser Formel musste dies durch die WENNFEHLER Funktion umgesetzt werden.
Der Vergleichsmodus legt die Art fest, wie das Suchkriterium in der Suchmatrix vorgehen soll. Dabei sucht die 0 eine genaue Übereinstimmung. Eine 1 sucht eine genaue Übereinstimmung oder den nächst größeren Wert. Die -1 dahingegen den nächst kleineren Wert. Diese Varianten funktionieren nur mit Zahlenwerten, da ein Text natürlich keine nächsten Werte besitzt. Falls im Suchkriterium ein Platzhalter verwendet wurde, muss hier eine 2 eingetragen werden.
Der Suchmodus gibt an, von wo in der Suchmatrix gesucht werden soll. Bei der Standard 1 wird vom ersten bis zum letzten Wert in der Tabelle gesucht. Bei -1 wird beim letzten Wert im Zellbereich begonnen. Mit 2 und -2 aktiviert man die Binärsuche mit derselben Suchrichtung.
Alle Grundlagen der XVERWEIS Funktion
Der große Vorteil der XVERWEIS Funktion zur wahrscheinlich bekannten SVERWEIS Funktion ist die Vielfältigkeit der Möglichkeiten in einer Formel. Was beim SVERWEIS und dem INDEX VERGLEICH mit verschachtelten Funktionen gelöst werden musste, wurde beim XVERWEIS direkt integriert. Außerdem wurden als direkter Nachfolger alle Vorteile der Vorgänger zusammengefasst.
Die wichtigste Logik der XVERWEIS Funktion ist die Methode der Suche, welcher separat zur Ausgabe funktioniert. Das bedeutet, dass Suche und Ausgabe voneinander getrennt sind, und nicht im gleichen Zellbereich vorkommen müssen. Zu beachten ist dabei, dass die Suchspalte und Rückgabespalte dieselbe Dimension aufweisen müssen. Werden also zur Suche 30 Zeilen ausgewählt, muss auch die Rückgabespalte 30 Zeilen ausmachen.
In älteren Varianten existiert keine elegante Lösung, um fehlende oder falsche Werte in der Funktion darzustellen. Falls der Wert im Suchmodus nicht gefunden wurde, oder bei der Datensuche kein Ergebnis eingetragen ist, wurde lediglich ein #NV Fehler ausgegeben. Durch das Argument wenn_nicht_gefunden, kann nun ein Platzhalter wie „Kein Wert gefunden“ definiert werden. Über eine bedingte Formatierung kann hier schnell farblich hervorgehoben werden, falls Werte fehlerhaft sein sollten.
Der Suchmodus wurde implementiert, um die Suchtabelle, aber auch die Rückgabetabelle nicht verändern zu müssen, und trotzdem eine invertierte Rückgabematrix nutzen zu können. Wenn der Vergleichsmodus mit dem nächst größeren und kleineren Wert funktionieren soll, muss die Tabelle auch der Größe nach sortiert werden. Das Problem hierbei entsteht, wenn zwei gleiche Werte in der Tabelle vorhanden sind. Dabei wird die XVERWEIS Funktion immer das Ergebnis wählen, da sie danach die Suche abbricht und zur Rückgabematrix wechselt. Möchte man jedoch die letzte Zeile mit dem gesuchten Wert, kann mit dem Suchmodus sofort der Algorithmus umgedreht werden, und das Suchkriterium wird von unten gesucht. Dieses Vorgehen war in älteren Varianten der Suche deutlich komplizierter.
Die Schwächen der XVERWEIS Funktion im Vergleich sind sehr gering. Da die Funktion in Excel viele Einträge bietet, muss natürlich mehr verarbeitet werden, wenn Excel die Funktion berechnet. Wenn man jedoch keine große Tabelle hat, fällt dies in der Rechenleistung und Geschwindigkeit der Tabelle nicht ins Gewicht. Daten unter mehreren tausend Zeilen oder Spalten werden keine Nachteile zu herkömmlichen Funktionen verspüren.
Alles was du noch wissen musst, kannst du kurz und knapp im folgenden Video nachsehen.
Die XVERWEIS Funktion an einem Beispiel
Wie oben kurz erklärt, handelt es sich beim XVERWEIS nicht um Spalten, sondern um Matrizen. Das hat zur Folge, dass der Funktion egal ist, ob die Suchmatrix und Rückgabematrix waagrecht oder senkrecht ausgerichtet sind, solange diese kohärent sind.
In diesem Beispiel wird der Vergleich zum SVERWEIS gezeigt. Es würde jedoch kein Unterschied zum WVERWEIS entstehen, wäre das Beispiel um 90 Grad gedreht.
Ein weiterer Unterschied zum SVERWEIS entsteht durch die Anordnung der Spalten. Beim XVERWEIS ist es irrelevant, ob die Suchmatrix oder Rückgabematrix zuerst kommt. Beim SVERWEIS ist es nicht möglich, nach links zu suchen. Ist die Rückgabe als vor der Suche muss die XVERWEIS Formel verwendet werden.
In diesem Beispiel wird nach Informationen von einem Produkt gesucht. Die Informationen, die ausgegeben werden sollen, stehen in Spalte A. Die Spalte B beinhaltet die zu durchsuchenden Produkte. Falls kein Produkt gefunden wird, soll kein Wert zurückgegeben werden.
Leider sind von diesem Produkt zwei Varianten vorhanden, die mit L und XL beschrieben werden. Daher scheitert die Suche am Suchkriterium. Hier muss nun hinter dem Produkt ein Platzhalter gesetzt werden, wodurch alles hinter dem Produktnamen irrelevant wird. Die Nutzung vom Platzhalter muss ebenfalls in der XVERWEIS Funktion vermerkt werden.
Eigentlich wäre diese Ausgabe jetzt korrekt. Allerdings soll in diesem Fall die XL Variante angezeigt werden. Da diese im Produktkatalog immer nach den kleineren Varianten kommt, kann die Suche invertiert werden, und von unten gesucht werden. Dadurch wird die XL Variante zuerst gefunden, und ausgegeben.
Mit diesen Einstellungen kann der XVERWEIS genau den Bedürfnissen angepasst werden und sich einige andere Formeln erspart lassen.
Mehrere Suchkriterien beim Excel XVERWEIS
Natürlich ist es, wie beim SVERWEIS, auch beim XVERWEIS möglich mehrere Suchkriterien in eine Suchanfrage zu verpacken. Im Gegensatz zu anderen Funktionen ist dies hier jedoch eine leichte Aufgabe. Microsoft hat auch hier in Excel die Wünsche gehört, und für mehrere Suchkriterien ein einfaches Und-Symbol eingeführt.
Statt mit anderen Funktionen zu hantieren und zu verschachteln, kann sowohl das Suchkriterium, als auch die Suchmatrix mit einem & verbunden werden, um mit dem XVERWEIS mehrere Spalten durchsuchen zu können. Dadurch werden Funktionen wie SUMMENPRODUKT nicht mehr benötigt.
Mit dem XVERWEIS mehrere Ergebnisse erhalten
Neben Suchkriterien ist es auch möglich, mit der XVERWEIS Formel mehrere Ergebnisse anzeigen zu können. Da es sich beim XVERWEIS um eine Matrix-Formel handelt, ist es Excel möglich Ergebnisse nicht nur in einer Zelle, so wie beim SVERWEIS, anzeigen zu lassen, sondern einen Überlauf zu generieren und alle Einträge im Bereich zur Verfügung zu stellen.
Ausschlaggebend dabei ist, dass die Rückgabematrix nicht aus einer Spalte bestehen darf, sondern alle Spalten mit Ergebnissen in die Formel integriert werden müssen. Dabei muss auch die Ausrichtung der Formel beachtet werden. Ist die Suchmatrix eine Spalte, also horizontal, liest Excel beim XVERWEIS die Rückgabematrix ebenfalls horizontal, und dabei alle Ergebnisse eines Suchkriteriums vertikal. Einfacher zu erkennen am folgenden Beispiel.
SVERWEIS, INDEX VERGLEICH oder XVERWEIS Funktion
Wenn inzwischen die Ergebnisse der dargestellten Problemstellungen betrachtet werden, kann ein klares Fazit der Funktionen gezogen werden. Der INDEX VERGLEICH ist eine Kombination aus Funktionen, welche bereits vor vielen Jahren in Excel die Funktion für das Suchen von Verweisen eingeführt hat. Die SVERWEIS Funktion und WVERWEIS ist die erste Iteration von Funktionen, welche speziell dafür gedacht war, mit einem Suchbegriff einen Wert über eine Formel zu suchen.
Mit dem XVERWEIS wird nun seit kurzem über eine Beta Feature eingeführte Funktion ein Tool angeboten, welche alle positiven Aspekte der Vorgänger in eine Funktion vereint. Viele Zusatzfunktionen, welche über die Jahre von Anwendern und Anwenderinnen gefordert wurden, konnten in der XVERWEIS Funktion eingebunden werden.
Dies macht die Funktion mit Abstand zur Nummer #1 Verweisfunktion in Excel. Der einzige Nachteil ist, dass sie nicht in allen Versionen zur Verfügung steht.
Weitere Ressourcen über den XVERWEIS
Solltest du noch mehr über die Funktion nachlesen wollen, empfiehlt sich der offizielle Beitrag auf der Microsoft Webseite zum XVERWEIS.