Die XVERWEIS-Funktion

Die XVERWEIS-Funktion in Excel

Die XVERWEIS-Funktion in Excel ist eine neuere Funktion, die in Excel 365 eingeführt wurde. Sie ähnelt SVERWEIS und WVERWEIS, ist aber einfacher und flexibler. Während SVERWEIS beispielsweise nur nach Werten rechts von der Suchspalte suchen kann, kann XVERWEIS sowohl nach links als auch nach rechts suchen - und das mit weniger Argumenten. Außerdem benötigt XVERWEIS zwar nur drei Pflichtargumente, es gibt aber zusätzliche optionale Argumente, mit denen du die XVERWEIS-Funktion an deine Bedürfnisse anpassen kannst.

Syntax der XVERWEIS-Funktion

Wie oben erwähnt, hat XVERWEIS drei erforderliche Argumente (Funktionsparameter) und drei optionale Argumente. Diese werden im Folgenden erläutert.

=XVERWEIS(lookup_value; lookup_range; return_range; [not_found]; [match_mode]; [search_mode])

lookup\_value (erforderlich): Das ist der Wert, den du XVERWEIS übergibst und anhand dessen die Funktion die Position des entsprechenden Werts ermittelt, den du zurückerhalten möchtest.
lookup\_range (erforderlich): Das ist der Zellbereich, in dem sich der lookup\_value befindet.
return\_range (erforderlich): Dies ist der Zellbereich, der den Wert enthält, den XVERWEIS zurückgeben soll.
not\_found (optional): XVERWEIS gibt diesen Wert zurück, wenn keine gültige Übereinstimmung mit dem lookup\_value gefunden wird. Standardmäßig gibt XVERWEIS in diesem Fall #N/A zurück.
match\_mode (optional): Dieses Argument legt fest, was geschehen soll, wenn Excel keine exakte Übereinstimmung mit dem lookup\_value findet: Es kann nach dem nächsthöheren oder nächstniedrigeren Wert suchen. Außerdem kannst du hier bei XVERWEIS den Platzhalter-Abgleich verwenden, wenn du diesen Parameter angibst.
search\_mode (optional): Dieses Argument legt fest, ob XVERWEIS von links nach rechts (von oben nach unten) oder von rechts nach links (von unten nach oben) suchen soll.

Beispiel für die Verwendung von XVERWEIS in Excel: Vertikale Suche

Das folgende Beispiel für die Funktion XVERWEIS dient der vertikalen Suche, ähnlich wie es traditionell bei SVERWEIS der Fall ist. Angenommen, du hast die folgende Excel-Tabelle und möchtest wissen, in wie vielen Szenen Dwight aufgetreten ist.

	A	B	C	D
1	Charakter	Folgen	Szenen
2	Michael	137	3033
3	Dwight	186	2367
4	Jim	185	2150
5	Pam	182	1922
6	Andy	144	1341

Du würdest die folgende XVERWEIS-Formel verwenden:

=XVERWEIS("Dwight"; A:A; C:C)

Oder

=XVERWEIS("Dwight"; A2:A6; C2:C6)

Diese Formel würde 2367 zurückgeben, da dies die Zelle im Rückgabebereich ist, die dem Suchwert im Suchbereich entspricht (gleiche Position wie dieser). Denk daran, dass „Dwight“ in Anführungszeichen steht, da Textwerte in Excel in Anführungszeichen gesetzt werden müssen.

Mit XVERWEIS von rechts nach links suchen

Denk daran, dass die XVERWEIS-Funktion im Gegensatz zur SVERWEIS-Funktion in Excel Werte von rechts nach links durchsuchen kann. Nehmen wir dieselbe Excel-Tabelle wie im obigen Beispiel: Angenommen, du weißt, dass eine der Figuren in 185 Folgen aufgetreten ist, und du möchtest herausfinden, um welche Figur es sich handelt.

=XVERWEIS(185; B:B; A:A)

Diese Formel gibt „Jim“ zurück, da sich die Zelle, die „Jim“ im Rückgabebereich (A:A) enthält, an derselben Position befindet wie die Zelle, die 185 im Suchbereich (B:B) enthält. Es spielt keine Rolle, ob du von links nach rechts oder von rechts nach links suchst.

Beispiel für die Verwendung von XVERWEIS in Excel: Horizontale Suche

Die XVERWEIS-Funktion in Excel kann auch für horizontale Suchen verwendet werden, ähnlich wie es bisher mit WVERWEIS gemacht wurde. Nimm die folgende Excel-Tabelle und stell dir vor, du möchtest den Geburtsort von Lil Wayne herausfinden.

	A	B	C	D	E
1		Snoop Dogg	Tupac	Jay-Z	Lil Wayne
2	Vorname	Calvin	Tupac	Shawn	Dwayne
3	Geburtsjahr	1971	1971	1969	1982
4	Geburtsort	Long Beach	New York	New York	New Orleans

Du würdest die folgende XVERWEIS-Formel verwenden:

=XVERWEIS("Lil Wayne"; 1:1; 4:4)

Oder

=XVERWEIS("Lil Wayne"; B1:E1; B4:E4)

Die Formel würde in Zeile 1 nach dem Wert „Lil Wayne“ suchen und anschließend in Zeile 4 nach dem entsprechenden Wert suchen, um „New Orleans“ als Ergebnis auszugeben.

Im Gegensatz zu WVERWEIS kann die XVERWEIS-Funktion hingegen von unten nach oben suchen. Angenommen, du möchtest wissen, welcher Rapper 1969 geboren wurde. Dann könntest du folgende Formel verwenden:

=XVERWEIS(1969; 3:3; 1:1)

Oder

=XVERWEIS(1969; B3:E3; B1:E1)

Diese Formeln würden in Zeile 3 nach dem Wert 1969 suchen und den Wert in der entsprechenden Zelle von Zeile 1 zurückgeben, nämlich „Jay-Z“.

XVERWEIS-Fehler in Excel

Hier sind einige Beispiele für Fehler, die bei der Verwendung der XVERWEIS-Funktion in Excel auftreten können, sowie Tipps, wie du sie beheben kannst.

\#WERT-Fehler bei XVERWEIS

Die Funktion XVERWEIS gibt den Fehler #WERT zurück, wenn der Suchbereich und der Rückgabebereich nicht kompatibel sind. Oft liegt das daran, dass die beiden Bereiche unterschiedlich groß oder unterschiedlich aufgebaut sind. Nimm zum Beispiel noch einmal unsere Tabelle mit den Office-Charakteren.

	A	B	C	D
1	Charakter	Folgen	Szenen
2	Michael	137	3033
3	Dwight	186	2367
4	Jim	185	2150
5	Pam	182	1922
6	Andy	144	1341

Nehmen wir mal an, wir suchen Pams Episodenanzahl mit der folgenden Formel heraus:

=XVERWEIS("Pam"; A1:A6; B:B)

Diese Formel würde den Fehler #WERT zurückgeben, da der Bereich A1:A6 eine andere Größe hat als der Bereich B:B. Um diesen Fehler zu beheben, müssten wir sicherstellen, dass beide Bereiche dieselbe Größe und dieselben Abmessungen haben:

=XVERWEIS("Pam"; A1:A6; B1:B6)

\#N/A-Fehler bei XVERWEIS

Die Funktion XVERWEIS in Excel gibt den Fehler #N/A zurück, wenn sie den Suchwert im Suchbereich nicht finden kann. Nehmen wir zum Beispiel an, du möchtest, dass XVERWEIS eine Figur zurückgibt, die in 184 Folgen aufgetreten ist.

	A	B	C	D
1	Charakter	Folgen	Szenen
2	Michael	137	3033
3	Dwight	186	2367
4	Jim	185	2150
5	Pam	182	1922
6	Andy	144	1341

Du verwendest die folgende Formel:

=XVERWEIS(184; B:B; A:A)

Diese Formel gibt den Fehler #N/A zurück, da der Suchwert (184) im Suchbereich (B:B) nicht vorhanden ist.

Eine Möglichkeit, den #N/A-Fehler zu vermeiden, ist die Angabe des Arguments „not\_found“. Das ist das vierte Argument der XVERWEIS-Funktion und optional, weist die Funktion jedoch an, einen bestimmten Wert zurückzugeben, wenn der Suchwert nicht im Suchbereich gefunden wird.

=XVERWEIS(185; B:B; A:A; "Character not found")

Diese Formel würde „Jim“ zurückgeben, da 185 im Suchbereich vorkommt.

=XVERWEIS(184; B:B; A:A; "Character not found")

Diese Formel würde „Zeichen nicht gefunden“ zurückgeben, da 184 im Suchbereich nicht vorkommt.

Eine weitere Möglichkeit, den #N/A-Fehler zu beheben, besteht darin, ein Argument für den Übereinstimmungsmodus wie unten beschrieben anzugeben.

Übereinstimmungsmodus für XVERWEIS

Die XVERWEIS-Funktion in Excel erlaubt ein optionales Argument für den Suchmodus, das als fünftes Argument der XVERWEIS-Funktion angegeben wird. Der Suchmodus legt fest, wie Excel vorgehen soll, wenn XVERWEIS den Suchwert im Suchbereich nicht finden kann. Hier sind die zulässigen Werte für match_mode:

0 (oder weggelassen): Gib N/A (oder das Argument „not_found“) zurück, wenn der Suchwert nicht gefunden wurde
1: Wenn der Suchwert nicht gefunden wird, den nächsthöheren Wert zuordnen
-1: Wenn der Suchwert nicht gefunden wird, den nächstniedrigsten Wert zuordnen
2: Platzhalter-Übereinstimmung

Nimm zum Beispiel unsere Excel-Tabelle unten und nehmen wir an, wir wollen die Figur ermitteln, die in 184 Szenen aufgetreten ist.

	A	B	C	D
1	Charakter	Folgen	Szenen
2	Michael	137	3033
3	Dwight	186	2367
4	Jim	185	2150
5	Pam	182	1922
6	Andy	144	1341

=XVERWEIS(184; B:B; A:A)

Wenn das Argument „match mode“ weggelassen wird oder den Wert 0 hat, gibt XVERWEIS #N/A zurück, da 184 im Suchbereich nicht vorkommt.

=XVERWEIS(184; B:B; A:A; "Character not found")

Diese Formel würde „Zeichen nicht gefunden“ zurückgeben, da 184 im Suchbereich nicht vorkommt, aber wir haben das Argument „not\_found“ angegeben.

=XVERWEIS(184; B:B; A:A; "Character not found"; 1)

Hier übergeben wir 1 an das Argument „match_mode“ von XVERWEIS, sodass der nächsthöhere Wert herangezogen wird, da 184 nicht vorhanden ist. Der nächsthöhere Wert in B:B ist 185, daher würde XVERWEIS „Jim“ zurückgeben.

=XVERWEIS(184; B:B; A:A; "Character not found"; -1)

Hier übergeben wir -1 an die XVERWEIS-Funktion, damit sie den nächstniedrigeren Wert in B:B findet, nämlich 182. In diesem Fall würde XVERWEIS „Pam“ zurückgeben.

Die XVERWEIS-Funktion in Excel unterstützt auch den Platzhalterabgleich, wenn „match_mode“ auf 2 gesetzt ist. Beim Platzhalterabgleich in Excel ersetzt ein Sternchen (\*) eine beliebige Anzahl von Zeichen, während ein Fragezeichen (?) genau ein Zeichen ersetzt.

=XVERWEIS("M*"; A:A; C:C; "Character not found"; 2)

Diese Formel würde 3033 zurückgeben, da „M\*“ mit „Micheal“ übereinstimmt und den entsprechenden Wert aus dem Bereich C:C zurückgibt.

Suchmodus für XVERWEIS

Die XVERWEIS-Funktion in Excel bietet ein sechstes und letztes Argument namens search\_mode, das optional ist. Das search\_mode-Argument in Excel teilt XVERWEIS mit, ob von oben nach unten oder von unten nach oben gesucht werden soll (bzw. von links nach rechts oder von rechts nach links bei horizontalen Suchvorgängen). Hier ist eine Liste der zulässigen Werte für „search\_mode“:

1: Starte die Suche beim ersten Element. Das ist die Standardeinstellung und weist XVERWEIS an, von oben (oder links) nach unten (oder rechts) zu suchen.
-1: Beginne die Suche beim letzten Element in umgekehrter Reihenfolge. Weist XVERWEIS an, von unten (rechts) nach oben (links) zu suchen.
2: Führe eine binäre Suche durch, wenn die Liste in aufsteigender Reihenfolge sortiert ist (wird in diesem Tutorial nicht behandelt).
-2: Führe eine binäre Suche durch, wenn die Liste absteigend sortiert ist (wird in diesem Tutorial nicht behandelt).

Nehmen wir zum Beispiel an, du hättest die untenstehende Tabelle mit Rappern und wolltest den Rapper finden, der 1971 geboren wurde. Wenn du genau hinschaust, wirst du feststellen, dass zwei Rapper 1971 geboren wurden.

	A	B	C	D	E
1		Snoop Dogg	Tupac	Jay-Z	Lil Wayne
2	Vorname	Calvin	Tupac	Shawn	Dwayne
3	Geburtsjahr	1971	1971	1969	1982
4	Geburtsort	Long Beach	New York	New York	New Orleans

=XVERWEIS(1971; 3:3; 1:1; "Rapper not found"; 0)

In diesem Fall wird „search\_mode“ in der Formel weggelassen, sodass die Formel „Snoop Dogg“ zurückgibt, da XVERWEIS standardmäßig beim ersten Element mit der Suche beginnt. Die erste Zelle, die den Wert 1971 enthält, ist B3, daher ist das Ergebnis die entsprechende Zelle im Rückgabebereich, also B1.

=XVERWEIS(1971; 3:3; 1:1; "Rapper not found"; 0; 1)

Diese Formel liefert das gleiche Ergebnis, da der Wert 1 für „search\_mode“ XVERWEIS anweist, mit dem ersten Wert zu beginnen - was dem Standardverhalten entspricht.

=XVERWEIS(1971; 3:3; 1:1; "Rapper not found"; 0; -1)

Diese Formel hat einen „search\_mode“-Wert von -1, was XVERWEIS anweist, in umgekehrter Reihenfolge zu suchen, beginnend mit dem letzten Element. In diesem Fall durchsucht XVERWEIS die Zeile 3:3 von rechts nach links nach dem Wert 1971. Da sich die erste Übereinstimmung in Zelle C3 befindet, gibt XVERWEIS „Tupac“ zurück.

Weiter zu den XVERWEIS-Übungsaufgaben! →

Verwendete Bilder

https://excelexercises.com/logo2.png
https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png