Über Jahrzehnte haben Generationen von Excel-Nutzenden sehr erfolgreich die Verweisfunktionen SVERWEIS und WVERWEIS eingesetzt, um z. B. zu bestimmten Werten passende Werte aus einer korrespondieren Tabelle zu finden oder zu überprüfen, ob in einem Tabellenblatt bestimmte Werte enthalten sind. Sie haben sich aber auch häufig über die Restriktionen dieser Funktionen geärgert. SVERWEIS und WVERWEIS unterscheiden sich lediglich in der Suchrichtung, wobei die senkrechte Suchrichtung deutlich häufiger Anwendung findet als die waagerechte, daher wird in diesem Beitrag nur auf den SVERWEIS eingegangen.

Welche Restriktionen hat die Funktion SVERWEIS?

Suchspalte und Rückgabespalte müssen sich in einer zusammenhängenden Matrix befinden, wobei es zwingend ist, dass die Matrix so definiert ist, dass die Suchspalte die erste Spalte dieser Matrix darstellt. Die Rückgabespalte muss über einen numerischen Spaltenindex angegeben werden, also in der wievielten Spalte der Matrix befindet sich der Rückgabewert. Das erfordert ggf. Anpassungen, wenn Spalten hinzugefügt oder gelöscht werden. Sofern eine exakte Übereinstimmung des Suchbegriffes mit dem Wert in der Rückgabespalte erforderlich war, muss dies mit einem zusätzlichen optionalen Argument angegeben werden. Ohne dieses Argument sucht SVERWEIS nach ungefährer Übereinstimmung, was bedeutet, dass bei nicht exakter Übereinstimmung der größte Wert, der kleiner ist als der gesuchte, zurückgegeben wird.

Beispiel:

In folgender Provisionstabelle soll in Zelle B2 anhand des Umsatzes in Zelle B1 ermittelt werden, welchen Provisionssatz ein Vertreter erhält:

Die Formel dazu in Zelle B2 lautet: =SVERWEIS(B1;A5:B14;2)¹, in Worten: Suche den Wert aus der Zelle B1 in der ersten Spalte der Matrix A5:B14 und wenn gefunden gebe aus der 2. Spalte in dieser Zeile der Matrix den Wert zurück. In diesem Fall soll nach ungefährer Übereinstimmung gesucht werden, d. h. es wird der größte Wert gefunden, der kleiner als der gesuchte ist. Da 120.000 gesucht wird, was in der ersten Matrixspalte nicht gefunden wird, wird der größte Wert in der Suchspalte gefunden, der kleiner als der gesuchte ist, also 100.000, und der zugehörige Wert aus der 2. Spalte, nämlich 2%, als Funktionsergebnis zurückgegeben. Das 4. Argument der Funktion SVERWEIS, also „Bereich_Verweis“, ist hier nicht angesprochen, da es mit dem logischen Wert WAHR angenommen wird, wenn keine Angabe erfolgt, und dieser zur ungefähren Übereinstimmung führt. Eine Suche mit ungefährer Übereinstimmung setzt zwingend voraus, dass die Suchspalte der Matrix aufsteigend sortiert ist, es wird also immer von klein nach groß gesucht.

Ist eine exakte Übereinstimmung erforderlich, muss dieses Argument mit dem Wert FALSCH belegt werden (dies kann auch durch „0“ erfolgen). In folgendem Beispiel wird in B1 eine bestimmte Kostenstelle eingegeben und es soll in B2 ihre Bezeichnung ausgegeben werden:

Die Formel in B2 lautet: =SVERWEIS(B1;A5:B10;2;FALSCH)

Würde im ersten Beispiel in B1 ein Wert < 50.000 Euro oder würde im zweiten Beispiel in B1 eine Kostenstelle eingegeben, die nicht vorhanden ist, führt dies zur Fehlermeldung „#NV“ (=Nicht Vorhanden). Diese könnte mit der Funktion WENNNV abgefangen werden, was dazu führt, dass der Fehlerwert #NV durch einen alternativen Wert ersetzt wird.

Im ersten Beispiel z. B. durch 0: =WENNNV(SVERWEIS(B1;A5:B14;2);0), d. h. bei Umsätzen <50.000 wird als Provisionssatz 0% ausgegeben.

Im zweiten Beispiel durch einen entsprechenden Hinweistext: =WENNNV(SVERWEIS(B1;A5:B10;2;FALSCH);“Kostenstelle nicht vorhanden“)

Welche Vorteile hat nun die Funktion XVERWEIS gegenüber SVERWEIS?

XVERWEIS verlangt keinen zusammenhängenden Zellbereich von Such- und Rückgabematrix. Die Rückgabematrix kann sich auch in einem getrennten Bereich und auch links von der Suchmatrix befinden. Such- und Rückgabematrix können sich auch in unterschiedlichen Tabellenblättern und sogar unterschiedlichen Arbeitsmappen befinden. Die Rückgabematrix wird durch Zellbezug und nicht durch Spaltenindex angegeben, was Anpassungen bei Einfügung oder Löschung von Spalten oder Zeilen überflüssig macht. Einzige Anforderung ist gleiche Größe des Zeilen- und Spaltenbereichs von Such- und Rückgabematrix, ansonsten würde der Fehler „#WERT“ ausgegeben.

Die Ausgabe eines Wertes, wenn der gesuchte Wert nicht gefunden wird, ist nunmehr in XVERWEIS integriert. Es ist also nicht mehr erforderlich, hier mit WENNNV zu arbeiten.

Auch wenn ungefähre Übereinstimmung gesucht wird, ist es ggf. nicht mehr erforderlich, die Suchspalte aufsteigend zu sortieren.

Standardmäßig wird mit XVERWEIS eine exakte Übereinstimmung gesucht. Dieses Argument muss also in diesem Fall nicht mehr belegt werden. Wird eine ungefähre Übereinstimmung gesucht, besteht die Wahlmöglichkeit zwischen nächstkleinerem Wert (wie bisher bei SVERWEIS) oder nächstgrößerem Wert. Zudem kann auch eine Platzhaltersuche verwendet werden.

Auch die Suchrichtung kann variiert werden. SVERWEIS beginnt immer beim ersten Element des Suchbereichs, mit XVERWEIS kann auch beim letzten Element begonnen werden.

Schlussendlich kann XVERWEIS nicht nur einen einzelnen Wert zurückgeben, sondern auch einen Mehrfachwert, also eine Matrix, was es z. B. ermöglicht, mehrere Suchbegriffe zu kombinieren.

Wie wird XVERWEIS konkret eingesetzt?

Hierzu ist ein Blick auf die Syntax der Funktion sinnvoll:

XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])

Suchkriterium ist der Wert, nach dem gesucht wird. Dies entspricht dem gleichnamigen Argument der Funktion SVERWEIS.

Suchmatrix ist der Tabellenbereich, in dem der Wert gesucht wird. Dies entspricht der Spalte 1 der Matrixdefinition der Funktion SVERWEIS.

Rückgabematrix ist der Tabellenbereich, in dem sich der Rückgabewert bei gefundenem Suchkriterium befindet. Dieser kann sich wie oben beschreiben auf einem anderen Tabellenblatt oder einer Arbeitsmappe, also getrennt von Formelzelle und/oder Suchmatrix befinden. Einzige Anforderung ist die gleiche Dimension wie Suchmatrix.

Das sind die Pflichtargumente der Funktion XVERWEIS. Die weiteren Argumente sind optional, aber bei optionalen Argumenten ist es immer sehr ratsam zu wissen, welchen Wert Excel als Standard verwendet, wenn das Argument nicht angegeben ist.

wenn_nicht_gefunden: Das Argument gibt an, welcher Wert anstelle vom Fehler „#NV“ (Standardwert) ausgegeben werden soll, wenn das Suchkriterium nicht in der Suchmatrix gefunden wird, siehe hierzu oben die Ausführungen zu WENNNV.

Vergleichsmodus: Standardwert ist 0, gilt also auch bei Weglassen des Arguments. Es wird dann genaue Übereinstimmung gesucht. Wenn diese nicht gefunden wird, wird entweder der Fehler „#NV“ oder wenn angegeben der Wert im Argument „wenn_nicht_gefunden“ ausgegeben.

Mit dem Wert „-1“ in diesem Argument wird gesteuert, dass bei nicht exakter Übereinstimmung das nächstkleinere Element aus dem Rückgabebereich ausgegeben wird. Dies entspricht der Vorgehensweise von SVERWEIS in diesem Fall. Mit dem Wert „1“ in diesem Argument wird dagegen gesteuert, dass bei nicht exakter Übereinstimmung das nächstgrößere Element aus dem Rückgabebereich ausgegeben wird.

Mit dem Wert „2“ in diesem Argument wird letztlich noch gesteuert, dass im Argument Suchkriterium auch Platzhalterzeichen (also „*“ oder „?“) verwendet werden können. Wichtig: Wenn Platzhalterzeichen im Suchkriterium verwendet werden, muss das Argument auf „2“ gesetzt werden, sonst wird das Platzhalterzeichen als Teil des Suchbegriffes gelesen.

Suchmodus: SVERWEIS sucht vom ersten zum letzten Wert im Suchbereich, gefunden wird also das erstmalige Auftreten des gesuchten Wertes, das ist auch der Standardwert bei XVERWEIS (Argumentwert 1). XVERWEIS kann aber auch vom letzten zum ersten Wert suchen und findet folglich das letzte Auftreten des gesuchten Wertes im Suchbereich (Argumentwert -1).

SVERWEIS verlangt bei Suche mit ungefährer Übereinstimmung, dass die Suchspalte aufsteigend sortiert sein muss. Dies ist bei XVERWEIS nur dann erforderlich, wenn die sog. Binärsuche eingesetzt wird, die einen Geschwindigkeitsvorteil bei sehr großen Datenbeständen bietet. Hier sind die Argumentwerte 2 bei Binärsuche in aufsteigender Reihenfolge und -2 bei absteigender Reihenfolge

Anwendungsbeispiele für XVERWEIS

Im Folgenden wird gezeigt, wie der XVERWEIS verwendet werden kann. Die folgenden Anwendungsbeispiele basieren auf dieser Tabelle:

Beispiel 1

Zeige in Zelle I2 den ersten Artikel, der mindestens 1.000 € (Zelle H2) Einkaufpreis hat.

Formel in Zelle I2: =XVERWEIS(H2;C2:C27;B2:B27;;1)

Erläuterung: Gesucht wird der Wert in H2 im Bereich C2:C27. Im fünften Argument (Vergleichsmodus) wird durch den Wert „1“ gesteuert, dass das nächstgrößere Element zurückgegeben wird, wenn keine genaue Übereinstimmung gefunden wird. Da es keine genaue Übereinstimmung gibt, wird der nächstgrößere Wert gefunden, also 1.100 €. Dieser befindet sich in der 6. Zelle der Suchmatrix. Folglich wird der Wert in der 6. Zelle der Rückgabematrix (B2:B27) als Funktionsergebnis ausgegeben, also Artikel-Nr. A-113. Es wird also, anders als bei SVERWEIS, der Rückgabewert nicht (zwingend) in der gleichen Zeile gefunden wie der Suchwert.

Beispiel 2

Es soll die Artikel-Nr. gefunden werden für den Artikel, der mindestens 1.600 € Verkaufspreis hat. Sofern dieser nicht gefunden wird, soll der Text „Kein Artikel“ ausgegeben werden.

Formel in Zelle I4: =XVERWEIS(H4;D2:D27;B2:B27;“Kein Artikel“;1)

Erläuterung: Gesucht wird der Wert in H4 im Bereich D2:D27. Im fünften Argument (Vergleichsmodus) wird wieder durch den Wert „1“ gesteuert, dass das nächstgrößere Element zurückgegeben wird, wenn keine genaue Übereinstimmung gefunden wird. Da es keine genaue Übereinstimmung gibt, wird der nächstgrößere Wert als 1.600 € gesucht, der jedoch nicht vorhanden ist, was grundsätzlich zur Fehlermeldung „#NV“ führt. Da aber im vierten Argument gesteuert wird, dass in diesem Fall der genannte Text ausgegeben wird, lautet das Funktionsergebnis „Kein Artikel“.

Beispiel 3

Es soll, beginnend vom letzten Element, der Verkaufspreis eines Artikels gefunden werden, der sich im Regal 15 befindet.

Formel in Zelle I6: =XVERWEIS(H6;E2:E27;D2:D27;;;-1)

Erläuterung: Gesucht wird der Wert in Zelle H6 im Bereich der Lager-Orte, also E2:E27, und es soll der Verkaufspreis (D2:D27) des zuerst gefundenen Artikels zurückgegeben werden, wobei die Suche bei letzten Element beginnt, was durch „-1“ im sechsten Argument gesteuert wird, so dass der Verkaufspreis 1.280 € (des Artikels Q-114) als Funktionsergebnis ausgegeben wird.

Beispiel 4

Es soll die Bezeichnung des ersten Artikels ermittelt werden, dessen Nummer mit „X“ beginnt.

Formel in Zelle I8: =XVERWEIS(H8;B2:B27;A2:A27;;2)

Erläuterung: Suche im Bereich B2:B27 den ersten Wert, der mit „X“ beginnt (H8) und gib den entsprechenden Wert aus dem Bereich A2:A27 zurück. Dass mit Jokerzeichen gesucht wird, steuert das fünfte Argument mit dem Wert „2“. Würde dieses Argument weggelassen, sucht die Funktion nach der Artikel-Nummer „X*“, die jedoch nicht vorhanden ist. Wenn das sechste Argument (Suchrichtung) mit „-1“ belegt wird, würde die Suchrichtung mit dem letzten Element beginnen und folglich der „Artikel 026“ gefunden werden.

Beispiel 5

Die Funktion XVERWEIS kann nicht nur einen Wert zurückgeben, sondern ein sog Array, also einen Mehrfachwert. Dazu ist erforderlich, die Rückgabematrix entsprechend mehrspaltig zu definieren. Beispiel: Für den Artikel D-490 sollen Einkaufspreis, Verkaufspreis und Lager-Ort ausgegeben werden:

Formel in Zelle I10: =XVERWEIS(H10;B2:B27;C2:E27)

Erläuterung: Suche den Wert in Zelle H10 im Bereich B2:B27 und wenn gefunden gib die entsprechenden Rückgabewerte aus den Spalten C, D und E zurück, diese werden in der Zelle I10 und den rechts angrenzenden Zellen ausgegeben. Wichtig: Änderungen der Formel dürfen nur in I10 erfolgen, nicht in den rechts angrenzenden abhängigen Zellen!

Beispiel 6

XVERWEIS ist weiterhin in der Lage, mehrere Suchkriterien zu verbinden. Dazu ist vorab wichtig zu wissen, dass die Funktion intern mit einer Tabelle arbeitet, in der Wahrheitswerte für die einzelnen Bedingungen erzeugt werden, wobei ein logisches WAHR mit dem Wert 1 und ein logisches FALSCH mit dem Wert 0 gespeichert wird. Sind also mehrere Bedingungen definiert, wird für jede Bedingung ein entsprechender Wahrheitswert definiert, und wenn deren numerische Werte multipliziert werden, ergibt sich 1, wenn alle Wahrheitswerte logisch WAHR lauten bzw. 0, wenn einer der Wahrheitswerte zum logisch FALSCH führt.

Auf dieser Grundlage lässt sich dann z. B. ermitteln, welcher Artikel im Regal 06 einen Einkaufpreis von mindestens 500 € hat.

Formel in Zelle I13: =XVERWEIS(1;(E2:E27=H12)*(C2:C27>=H13);B2:B27)

Erläuterung: Es wird der Artikel gesucht, der sowohl vom Lager-Ort das Kriterium aus Zelle H12 (Lager-Ort = Regal 06) als auch vom Einkaufspreis das Kriterium aus Zelle H13 (>= 500 €) erfüllt. Das Erfüllen der Kriterien wird intern mit dem Wert „1“ belegt, so dass sich in der Multiplikation der Wert „1“ ergibt, wenn beide Kriterien erfüllt sind. Suchwert der Funktion ist also 1 und dies trifft als erstes auf den Artikel Z-558 zu.

Kombination mehrerer XVERWEIS-Funktionen

Da XVERWEIS nicht nur einen einzelnen Wert, sondern auch einen Mehrfachwert (eine Matrix) als Funktionsergebnis ausgeben kann, lassen sich über Kombinationen dieser Funktion mehrere Parameter verbinden. Beispiel ist folgende Provisionstabelle, nach der sich der Provisionssatz aus der Kombination von Umsatz und Größe (Mitarbeiterzahl) des jeweiligen Kunden ergibt. Dabei folgt die Logik der Tabelle der Überlegung, dass es bei größeren Kunden vergleichsweise einfacher ist, größere Aufträge zu akquirieren als bei kleineren, folglich also dort die Provisionssätze höher sind.

Die entsprechende Formel in Zelle B20 lautet also:

=XVERWEIS(B18;B5:B14;XVERWEIS(B19;C4:F4;C5:F14;;-1);0;-1)

Erläuterung: Über die „innere“ XVERWEIS-Funktion

  XVERWEIS(B19;C4:F4;C5:F14;;-1)

wird der Wert in Zelle B19 gesucht, und zwar im Bereich C4:F4, also den Angaben zu den Mitarbeiterzahlen. Rückgabematrix ist der Bereich von C5:F14, also im jeweiligen Ergebnis die Spalte, die der angegebenen Mitarbeiterzahl entspricht, im Beispiel also Spalte E, somit E5:E14, da im 5. Argument über „-1“ gesteuert wird, dass bei nicht exakter Übereinstimmung das nächstkleine Element gefunden wird, hier also 100, Spalte E. Das sechste Argument ist nicht belegt, da Standardsuchrichtung vom ersten zum letzten Element ist, was hier verwendet werden soll.

Die „äußere“ XVERWEIS-Funktion greift nun bezüglich der Rückgabematrix auf das Ergebnis der „inneren“ XVERWEIS-Funktion zurück, hier also den Zellbereich E5:E14. Gesucht wird der Wert in der Zelle B18 im Bereich B5:B14, Rückgabematrix ist das Ergebnis der inneren XVERWEIS-Funktion, also im Beispiel E5:E14. Da auch hier über „-1“ im 5. Argument gesteuert wird, dass bei nicht exakter Übereinstimmung der nächst kleinere Wert (hier 400.000 €) gefunden wird, führt die Kombination beider Funktionen zum Ergebnis, dass bei einem Umsatz von 420.000 € und einer Mitarbeiterzahl des Kunden von 150 ein Provisionssatz von 14% anzuwenden ist.

Kompatibilität mit Vorversionen

Wichtig zu beachten ist, dass die Funktion XVERWEIS nur in der Abonnementversion Microsoft 365 sowie in Excel-Versionen ab 2021 zur Verfügung steht. Was passiert aber, wenn man eine Arbeitsmappe mit dieser Funktion in einer früheren Excel-Version öffnet? Das letzte Ergebnis der Funktion ist zwar noch vorhanden, aber sobald z. B. durch Veränderung des Suchkriteriums eine Aktualisierung des Funktionsergebnisses ausgelöst wird, erscheint der Fehler „#NAME“ in der Formelzelle, da diese Funktion der Version nicht bekannt ist. In der Bearbeitungszeile erscheint dann: =@xlfn.XLOOKUP(…)

Fazit: Arbeitsmappen, in denen das neue XVERWEIS verwendet wird, sind in Excel-Versionen vor 2021 nicht aktiv nutzbar.

Um festzustellen, ob eine Arbeitsmappe Kompatibilitätsprobleme mit Vorversionen verursacht, steht unter dem Register „Datei“ (sog. „Backstagebereich“) in der Rubrik „Informationen“ die Schaltfläche „Auf Probleme überprüfen“ zur Verfügung, mit der Kompatibilitätsprobleme zu früheren Versionen überprüft werden können.

^{1 Aus Übersichtsgründen wird hier auf den absoluten Bezug ($A$5:$B$14) bei der Zellangabe im Argument Matrix verzichtet, dieser sollte jedoch zumindest immer dann verwendet werden, wenn die entsprechende Formel ausgefüllt (kopiert) wird.}

Sie arbeiten bereits einige Zeit mit Excel und kennen sich mit den Grundfunktionen gut aus. Nun möchten Sie Excel intensiver nutzen? Dann empfehlen wir Ihnen unser Seminar Excel – Aufbaustufe. Wir freuen uns auf Sie!