Syntax:
XVERWEIS(Suchkriterium; Suchbereich; Rückgabebereich [; WennNichtGefunden] [; Vergleichsmodus] [; Suchmodus])
Beschreibung:
Diese Funktion sucht im angegebenen Suchbereich nach dem ersten Vorkommen von Suchkriterium. Wird es gefunden, liefert die Funktion den Inhalt der Zelle, die sich in der gleichen Zeile im Rückgabebereich befindet (bei senkrecht zu lesenden Tabellen). Bei waagerecht zu lesenden Tabellen liefert die Funktion den Inhalt der Zelle, die sich in der gleichen Spalte im Rückgabebereich befindet. In den folgenden Ausführungen wird zur Vereinfachung nur die senkrechte Suchrichtung betrachtet.
Anmerkung: Während SVERWEIS nur in senkrechter Suchrichtung arbeitet und WVERWEIS nur in waagerechter Richtung, kann XVERWEIS sowohl vertikal als auch horizontal suchen und außerdem ganze Zeilen/Spalten als Ergebnis zurückliefern.
Wichtig: XVERWEIS verwendet standardmäßig eine genaue Übereinstimmung – anders als SVERWEIS oder WVERWEIS, die ohne das Sortierargument eine ungefähre Suche erwarten.
Suchkriterium ist der gesuchte Wert. Die Groß-/Kleinschreibung spielt keine Rolle.
Suchbereich ist der zu durchsuchende Zellbereich und darf nur eine Spalte breit sein. Die Werte können Zeichenketten, Zahlen oder Wahrheitswerte sein.
Rückgabebereich ist der Bereich mit den auszugebenden Werten. Diese befinden sich bei senkrechter Suchrichtung in der gleichen Zeile wie das Suchkriterium. Der Rückgabebereich kann eine oder auch mehrere Spalten groß sein.
WennNichtGefunden (optional) ist der von Ihnen definierte Ausdruck, wenn keine gültige Übereinstimmung für das Suchkriterium gefunden wurde. Als Ergebnis wird dann der Text zurückgegeben, den Sie hier eintragen. Wird dieses Argument weggelassen (Standardeinstellung) und keine gültige Übereinstimmung gefunden, liefert die Funktion den Fehlerwert #NV.
Vergleichsmodus (optional) steuert die Art der Übereinstimmung. Mögliche Werte:
0 = Genaue Übereinstimmung. Dies ist der Standardwert (gilt somit auch, wenn das Argument weggelassen wird). Wird keine genaue Übereinstimmung gefunden, dann wird der Fehlerwert #NV zurückgegeben bzw. der im Argument WennNichtGefunden definierte Text.
-1 = Bei nicht genauer Übereinstimmung wird das nächstkleinere Element zurückgegeben (Rückgabebereich muss in absteigender Reihenfolge sortiert sein).
1 = Bei nicht genauer Übereinstimmung wird das nächstgrößere Element zurückgegeben (Rückgabebereich muss in aufsteigender Reihenfolge sortiert sein).
2 = Teilt der Funktion mit, dass Platzhalterzeichen ("*", "?" und "~") in Suchkriterium vorkommen.
Suchmodus (optional) steuert die Suchrichtung / Suchmethode: Mögliche Werte:
1 = Suche von oben nach unten (bei senkrechter Suchrichtung). Dies ist der Standardwert, gilt somit auch, wenn das Argument weggelassen wird.
-1 = Suche von unten nach oben (bei senkrechter Suchrichtung).
2 = Binäre Suche (schnell bei großen Datenbeständen) – erfordert sortierte Daten in aufsteigender Reihenfolge.
-2 = Binäre Suche (schnell bei großen Datenbeständen) – erfordert sortierte Daten in absteigender Reihenfolge.
Hinweise:
▪Sie können die Funktion auch auf mehrspaltige Tabellen anwenden und die Formel wird automatisch auf die angrenzenden Zellen ausgedehnt, wie es benötigt wird (drücken Sie dazu einfach wie gewohnt die Eingabetaste nach Abschluss der Formeleingabe in die Zelle). Zudem passt sich das Ergebnis automatisch an, wenn Änderungen an der Ausgangsliste vorgenommen werden. Aus diesem Grund gelten solche Formeln auch als Funktionen mit dynamischem Ausgabebereich.
| Wenn Sie die Funktion auf mehrspaltige Tabellen anwenden und dadurch im Ausgabebereich ein Überlauf in angrenzende Zellen entsteht, die bereits durch Inhalte belegt sind, wird der Fehlerwert #ÜBERLAUF! angezeigt. Achten Sie daher darauf, dass der angrenzende Bereich soweit leer ist, wie es für die vorgesehene Ausdehnung der Formel benötigt wird. |
▪XVERWEIS benötigt keinen zusammenhängenden Zellbereich von Suchbereich und Rückgabebereich. Der Rückgabebereich kann auch links vom Suchbereich liegen (bei senkrecht zu lesenden Tabellen) oder sogar in anderen Arbeitsblättern oder Arbeitsmappen.
▪Werden Suchbereich und Rückgabebereich in ihren Dimensionen nicht zueinander passend angegeben (unterschiedliche Anzahl Zeilen bei senkrecht zu lesenden Tabellen), liefert die Funktion den Fehlerwert #WERT!.
▪Wird kein Treffer gefunden und das Argument WennNichtGefunden nicht angegeben, liefert die Funktion den Fehlerwert #NV.
▪Platzhalter (*, ?, ~) können beim Vergleich mit Vergleichsmodus = 2 verwendet werden.
▪XVERWEIS unterscheidet standardmäßig nicht zwischen Groß-/Kleinschreibung. Eine echte Groß-/Kleinschreibung erreicht man nur über zusätzliche Funktionen/Kombinationen (z. B. Formel IDENTISCH).
▪Für ungefähre Treffer (Vergleichsmodus = 1 oder -1 bzw. binäre Suchmodi) müssen die Daten im Suchbereich in der jeweils geforderten Reihenfolge sortiert sein – andernfalls sind die Ergebnisse nicht verlässlich.
Kompatibilitätshinweise:
Microsoft Excel unterstützt diese Funktion erst ab Version 2021. In älteren Versionen ist die Funktion unbekannt.
Beispiele (senkrechte Suchrichtung):
Sie verkaufen Blumen zu unterschiedlichen Stückpreisen – je nachdem, wie viele Pflanzen der Kunde abnimmt. Dazu haben Sie eine Tabelle mit einer Rabattstaffel erstellt:
Jetzt können Sie die Funktion XVERWEIS einsetzen, um abhängig von der Stückzahl den Preis für eine bestimmte Blumensorte zu ermitteln.
Verwenden Sie dazu folgende Argumente:
Als Suchkriterium geben Sie den Namen der Blumensorte an (also "Rosen", "Nelken" oder "Tulpen").
Als Suchbereich geben Sie die den Zellbereich an, der die Blumensorten enthält – hier also die erste Spalte mit dem Bereich A2:A4.
Als Rückgabebereich geben Sie die den Zellbereich an, der die Preise enthält. Der Preis für 1 Stück befindet sich in Spalte 2 des gewählten Bereichs, der Preis für 2 Stück in Spalte 3 und der Preis für 3 Stück in Spalte 4. Sie können jetzt entweder einen Bereich bestehend aus einer Spalte (z.B. B2:B4) angeben, es sind aber auch mehrere Spalten möglich (z.B. B2:D4).
Das Argument WennNichtGefunden können Sie optional einsetzen für Fälle, in denen keine gültige Übereinstimmung gefunden wurde. Statt des Fehlerwerts #NV wird ein von Ihnen definierter Ausdruck (z.B. "nicht vorhanden") zurückgegeben.
Beispiele (senkrechte Suchrichtung):
XVERWEIS("Rosen"; A2:A4; B2:B4) liefert den Stückpreis bei Abnahme von 1 Rose, also 5,50.
XVERWEIS("Rosen"; A2:A4; C2:C4) liefert den Stückpreis bei Abnahme von 2 Rosen, also 5,10.
XVERWEIS("Rosen"; A2:A4; D2:D4) liefert den Stückpreis bei Abnahme von 3 Rosen, also 4,95.
XVERWEIS("Nelken"; A2:A4; D2:D4) liefert 4,50.
XVERWEIS("Petunien"; A2:A4; D2:D4) liefert den Fehlerwert #NV, weil "Petunien" in A2:A4 nicht vorkommt.
XVERWEIS("Petunien"; A2:A4; D2:D4; "nicht vorhanden") liefert stattdessen den von Ihnen definierten Text "nicht vorhanden" für das Argument WennNichtGefunden.
XVERWEIS("Rosen"; A2:A4; B2:D4) liefert alle Stückpreise bei der Abnahme von 1, 2 oder 3 Rosen nebeneinander in einer Zeile.
Beispiel (waagerechte Suchrichtung):
XVERWEIS("Rosen"; B1:D1; B2:D2) liefert den Stückpreis bei Abnahme von 1 Rose, also 5,50.
Beispiel (ungefähre Suche):
Das folgende Beispiel erläutert den Einsatz des Arguments Vergleichsmodus.
Als Kunde erreicht man abhängig vom erfolgten Umsatz eine höhere Prämien-Stufe. Dazu haben Sie eine Tabelle in senkrechter Suchrichtung mit folgenden Prämien-Stufen erstellt:
Hinweis: Das Argument WennNichtGefunden soll für diese Tabelle weggelassen werden (Standardeinstellung). Es muss allerdings in der Formel mit der Zeichenfolge ;; als 4. Argument gekennzeichnet werden, damit das 5. Argument Vergleichsmodus zum Funktionieren der Formel an der richtigen Position erscheint.
XVERWEIS(E2; A2:A6; B2:B6; ; -1) liefert die Prämien-Stufe "Silber", da der genau übereinstimmende Wert für einen Umsatz von 2.550 nicht gefunden werden kann und das nächstkleinere Element (im Suchbereich: 2000) zurückgegeben wird (im Rückgabebereich: Silber).
Tipp: Für Suchkriterium bestehen generell die alternativen Möglichkeiten, es als Ausdruck anzugeben (z.B. "Rosen" wie in den Beispielen weiter oben) oder als Zelle, die den gesuchten Wert beinhaltet (wie hier beispielsweise mit Zelle "E2").
Siehe auch:
SVERWEIS, WVERWEIS, VERWEIS, INDEX, VERGLEICH, ERSTERWERT, WAHL, WENNS