Die neue XVERWEIS-Funktion in Excel stellt einen mächtigen Ersatz für die weit verbreitete SVERWEIS-Funktion dar. Sie behebt einige der Beschränkungen von SVERWEIS und erweitert die Funktionalität. Hier erfahren Sie, was Sie wissen müssen.
Was ist XVERWEIS?
XVERWEIS überwindet die Einschränkungen von SVERWEIS und ersetzt zudem HVERWEIS. Im Gegensatz zu SVERWEIS kann XVERWEIS beispielsweise nach links suchen, verwendet standardmäßig eine genaue Übereinstimmung und ermöglicht die Angabe eines Zellbereichs anstelle einer Spaltennummer. SVERWEIS ist nicht so benutzerfreundlich oder vielseitig. Wir zeigen Ihnen, wie es funktioniert.
Aktuell ist XVERWEIS nur für Teilnehmer des Insider-Programms verfügbar. Sie können am Insider-Programm teilnehmen, um frühzeitig auf neue Excel-Funktionen zuzugreifen. Microsoft plant, die Funktion bald allen Office 365-Nutzern zugänglich zu machen.
Wie man XVERWEIS verwendet
Betrachten wir ein Anwendungsbeispiel. Gegeben seien die folgenden Daten. Wir möchten die Abteilung aus Spalte F für jede ID in Spalte A abrufen.
Dies ist ein typischer Fall für eine genaue Übereinstimmungssuche. XVERWEIS benötigt dazu nur drei Angaben.
Das Bild unten zeigt XVERWEIS mit sechs Argumenten, aber für eine genaue Übereinstimmung sind nur die ersten drei erforderlich. Konzentrieren wir uns auf diese:
Suchkriterium: Der gesuchte Wert.
Suchbereich: Der Bereich, in dem gesucht wird.
Ergebnisbereich: Der Bereich, der den zurückzugebenden Wert enthält.
Die Formel für dieses Beispiel lautet: =XVERWEIS(A2;$E$2:$E$8;$F$2:$F$8)
Lassen Sie uns nun einige Vorteile von XVERWEIS gegenüber SVERWEIS beleuchten.
Keine Spaltenindexnummer mehr
Das dritte Argument von SVERWEIS, die Spaltennummer der zurückzugebenden Informationen, entfällt bei XVERWEIS. Hier wählen Sie einfach den Ergebnisbereich (in diesem Beispiel Spalte F).
Zudem kann XVERWEIS – anders als SVERWEIS – auch Daten links der Suchspalte abrufen. Dazu später mehr.
Auch Probleme mit fehlerhaften Formeln durch das Einfügen neuer Spalten gehören der Vergangenheit an. Der Ergebnisbereich passt sich automatisch an.
Genaue Übereinstimmung als Standard
Bei der Verwendung von SVERWEIS war es oft verwirrend, warum man explizit eine genaue Übereinstimmung angeben musste.
XVERWEIS verwendet standardmäßig die genaue Übereinstimmung – den häufigsten Anwendungsfall für Suchfunktionen. Das reduziert Fehler und macht die Formel benutzerfreundlicher.
Kurz gesagt: XVERWEIS ist einfacher, benutzerfreundlicher und robuster als SVERWEIS.
XVERWEIS kann nach links suchen
Die Möglichkeit, den Ergebnisbereich frei zu wählen, macht XVERWEIS vielseitiger. Die Spaltenreihenfolge ist irrelevant.
SVERWEIS war darauf beschränkt, in der linken Spalte einer Tabelle zu suchen und dann eine bestimmte Anzahl von Spalten nach rechts zu gehen.
Im folgenden Beispiel suchen wir eine ID (Spalte E) und geben den Namen (Spalte D) zurück.
Die Formel lautet: =XVERWEIS(A2;$E$2:$E$8;$D$2:$D$8)
Was tun bei Nicht-Fund
Anwender von Suchfunktionen kennen die #N/A-Fehlermeldung, wenn SVERWEIS oder VERGLEICH den gesuchten Wert nicht finden. Oft hat das eine logische Ursache.
Da diese Meldung nicht hilfreich ist, suchen Benutzer schnell nach Wegen, sie zu unterdrücken. Und es gibt Mittel und Wege.
XVERWEIS hat ein eingebautes „Wenn nicht gefunden“-Argument zur Fehlerbehandlung. Wir sehen uns das im vorherigen Beispiel mit einer falsch eingegebenen ID an.
Die folgende Formel gibt „Falsche ID“ anstelle einer Fehlermeldung aus: =XVERWEIS(A2;$E$2:$E$8;$D$2:$D$8;“Falsche ID“)
XVERWEIS für Bereichssuchen
Weniger häufig als die genaue Übereinstimmung, aber dennoch sehr nützlich, ist die Suche nach Werten innerhalb von Bereichen. Hier ein Beispiel: Wir möchten einen Rabatt basierend auf dem ausgegebenen Betrag zuordnen.
Wir suchen nicht nach einem genauen Wert, sondern müssen ermitteln, wo die Werte in Spalte B in die Bereiche von Spalte E fallen, um den entsprechenden Rabatt zu bestimmen.
XVERWEIS hat ein optionales fünftes Argument (standardmäßig die genaue Übereinstimmung) namens Modus der Übereinstimmung.
XVERWEIS bietet bei ungefähren Übereinstimmungen mehr Möglichkeiten als SVERWEIS.
Es ist möglich, die nächste Übereinstimmung zu finden, die kleiner (-1) oder größer (1) als der gesuchte Wert ist. Ebenso können Platzhalterzeichen (2) wie „?“ oder „*“ verwendet werden. Diese Einstellung ist nicht standardmäßig aktiv wie bei SVERWEIS.
Die Formel in diesem Beispiel gibt den nächstkleineren Wert zurück, wenn keine genaue Übereinstimmung gefunden wird: =XVERWEIS(B2;$E$3:$E$7;$F$3:$F$7;-1)
Allerdings gibt es in Zelle C7 einen Fehler (#N/A), da das „wenn nicht gefunden“-Argument nicht verwendet wurde. Hier wäre ein Rabatt von 0% korrekt, da die Ausgabe von 64 die Rabattkriterien nicht erfüllt.
Ein weiterer Vorteil von XVERWEIS ist, dass die Nachschlagetabelle – anders als bei SVERWEIS – nicht aufsteigend sortiert sein muss.
Fügen Sie eine neue Zeile zur Nachschlagetabelle hinzu und erweitern Sie den verwendeten Bereich der Formel durch Ziehen der Eckpunkte.
Die Formel korrigiert den Fehler sofort. Die „0“ am Ende des Bereichs ist kein Problem.
Ich persönlich würde die Tabelle trotzdem nach der Suchspalte sortieren, da eine „0“ am Ende etwas seltsam wirkt. Aber die Tatsache, dass die Formel nicht fehlerhaft wurde, ist bemerkenswert.
XVERWEIS ersetzt auch HVERWEIS
Wie bereits erwähnt, ersetzt XVERWEIS auch HVERWEIS. Zwei Funktionen durch eine zu ersetzen ist bemerkenswert!
HVERWEIS ist die horizontale Suche entlang von Zeilen.
HVERWEIS ist zwar nicht so bekannt wie SVERWEIS, aber dennoch nützlich, z. B. wenn Überschriften in Spalte A stehen und die Daten in den Zeilen 4 und 5.
XVERWEIS kann in beide Richtungen suchen – nach unten in Spalten und horizontal entlang von Zeilen. Wir benötigen keine zwei unterschiedlichen Funktionen mehr.
In diesem Beispiel gibt die Formel den Verkaufswert für den Namen in Zelle A2 zurück. Sie sucht in Zeile 4 nach dem Namen und gibt den Wert aus Zeile 5 zurück: =XVERWEIS(A2;B4:E4;B5:E5)
XVERWEIS kann von unten nach oben suchen
Normalerweise sucht man in einer Liste nach dem ersten (oft einzigen) Vorkommen eines Wertes. XVERWEIS bietet ein sechstes Argument namens Suchmodus. Damit können wir die Suche umkehren, sodass sie am Ende beginnt und nach dem letzten Vorkommen eines Wertes sucht.
Im folgenden Beispiel wollen wir den Lagerbestand für jedes Produkt in Spalte A ermitteln.
Die Nachschlagetabelle ist nach Datum sortiert und enthält mehrere Lagerkontrollen pro Produkt. Wir möchten den Lagerbestand aus der letzten Kontrolle (dem letzten Vorkommen der Produkt-ID) zurückgeben.
Das sechste Argument der XVERWEIS-Funktion bietet vier Optionen. Wir wählen die Option „Suche vom letzten zum ersten Element“.
Die vollständige Formel lautet: =XVERWEIS(A2;$E$2:$E$9;$F$2:$F$9;;;-1)
Das vierte und fünfte Argument wurden in dieser Formel ignoriert, da sie optional sind und wir die Standardeinstellung der genauen Übereinstimmung verwenden wollten.
Zusammenfassung
XVERWEIS ist der lang erwartete Nachfolger von SVERWEIS und HVERWEIS.
In diesem Artikel wurden die Vorteile von XVERWEIS anhand verschiedener Beispiele demonstriert. XVERWEIS kann unter anderem blatt-, arbeitsmappen- und tabellenübergreifend eingesetzt werden. Die Beispiele wurden einfach gehalten, um das Verständnis zu erleichtern.
Mit der Einführung dynamischer Arrays in Excel wird XVERWEIS bald auch einen Wertebereich zurückgeben können. Dies ist ein Thema, das es wert ist, weiter untersucht zu werden.
Die Tage von SVERWEIS sind gezählt. XVERWEIS ist da und wird bald die Standard-Suchformel sein.