Formeln und Funktionen

XVERWEIS

XVERWEIS (Funktion)

Verwenden Sie die XVERWEIS-Funktion, um Nach-Unten-Informationen in einer Tabelle oder einem Bereich nach Zeile zu finden. Sie können z. B. nach dem Preis eines Kfz-Teils anhand der Artikelnummer suchen oder nach einem Mitarbeiternamen anhand seiner Mitarbeiter-ID. Mit XVERWEIS können Sie in einer Spalte nach einem bestimmten Suchbegriff suchen und ein Ergebnis aus derselben Zeile in einer anderen Spalte abrufen, und zwar unabhängig davon, auf welcher Seite sich die Ergebnisspalte befindet.

Ihr Browser unterstützt kein Video.

Syntax

Die XVERWEIS-Funktion durchsucht einen Bereich oder ein Array und gibt dann das Element zurück, das der ersten gefundenen Übereinstimmung entspricht. Wenn keine Übereinstimmung vorhanden ist, kann XVERWEIS die nächste (ungefähre) Übereinstimmung zurückgeben. 

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

Argument

Beschreibung

Suchkriterium

Erforderlich*

Der Wert, nach dem gesucht werden soll

*Wenn nicht angegeben, gibt XVERWEIS leere Zellen zurück, die inder lookup_array.   

Suchmatrix

Erforderlich

Das Array oder der Bereich, das/der durchsucht werden soll

Rückgabematrix

Erforderlich

Das Array oder der Bereich, das/der zurückgegeben werden soll

[wenn_nicht_gefunden]

Optional

Wenn keine gültige Übereinstimmung gefunden wird, wird der von Ihnen bereitgestellte "[wenn_nicht_gefunden]"-Text zurückgegeben.

Wenn keine gültige Übereinstimmung gefunden wird und [if_not_found] fehlt, wird #N/A zurückgegeben.

[match_mode]

Optional

Geben Sie den Übereinstimmungstyp an:

0: genaue Übereinstimmung. Wenn keine gefunden wird, wird "#N/V" zurückgegeben. Dies ist die Standardeinstellung.

-1: genaue Übereinstimmung. Wenn keine gefunden wurde, geben Sie das nächstkleinere Element zurück.

1: genaue Übereinstimmung. Wenn keine gefunden wurde, geben Sie das nächstgrößere Element zurück.

2: eine Platzhalterübereinstimmung, wobei *, ? und ~ eine Sonderbedeutung haben.

[search_mode]

Optional

Geben Sie den zu verwendenden Suchmodus an:

1: Führen Sie eine Suche durch, die beim ersten Element beginnt. Dies ist die Standardeinstellung.

-1: Führen Sie eine umgekehrte Suche durch, die beim letzten Element beginnt.

2: Führen Sie eine Binärsuche durch, die darauf basiert, dass eine Suchmatrix in aufsteigender Reihenfolge sortiert ist. Ist diese nicht so sortiert, werden ungültige Ergebnisse zurückgegeben.

-2: Führen Sie eine Binärsuche durch, die darauf basiert, dass eine Suchmatrix in absteigender Reihenfolge sortiert ist. Ist diese nicht so sortiert, werden ungültige Ergebnisse zurückgegeben.

Beispiele

In Beispiel 1    wird XVERWEIS verwendet, um einen Ländernamen in einem Bereich nach zu suchen und dann die Landesnummer für telefonieren zurück zu geben. Sie enthält die lookup_value (Zelle F2), lookup_array (Bereich B2:B11) und return_array (Bereich D2:D11). Es enthält nicht das Argument match_mode, da XVERWEIS standardmäßig eine exakte Übereinstimmung erzeugt.

Beispiel für die Verwendung der XVERWEIS-Funktion, um den Namen und die Abteilung eines Mitarbeiters anhand der Mitarbeiter-ID zurückzugeben. Die Formel lautet: =XVERWEIS(B2;B5:B14;C5:C14).

Hinweis: XVERWEIS verwendet ein Nachschlagearray und ein Rückgabearray, während SVERWEIS ein einzelnes Tabellenarray gefolgt von einer Spaltenindexnummer verwendet. Die entsprechende SVERWEIS-Formel in diesem Fall wäre: =SVERWEIS(F2;B2:D11;3;FALSCH)

———————————————————————————

Beispiel 2    sucht mitarbeiterbezogene Informationen basierend auf einer Mitarbeiter-ID-Nummer. Im Gegensatz zu SVERWEIS kann XVERWEIS ein Array mit mehreren Elementen zurückgeben, sodass eine einzelne Formel sowohl den Namen des Mitarbeiters als auch die Abteilung aus den Zellen C5:D14 zurückgeben kann.

Beispiel für die Verwendung der XVERWEIS-Funktion, um den Namen und die Abteilung eines Mitarbeiters anhand der Mitarbeiter-ID zurückzugeben. Die Formel lautet: =XVERWEIS(B2;B5:B14;C5:D14;0;1)

———————————————————————————

Beispiel 3    fügt dem vorherigen If_not_found ein Argument hinzu.

Beispiel für die Verwendung der XVERWEIS-Funktion, um den Namen und die Abteilung eines Mitarbeiters anhand der Mitarbeiter-ID mit dem Argument wenn_nicht_gefunden zurückzugeben. Die Formel lautet: =XVERWEIS(B2;B5:B14;C5:D14;0;1;"Mitarbeiter nicht gefunden")

———————————————————————————

Beispiel 4    sucht in Spalte C nach den persönlichen Einkünften, die in Zelle E2 eingegeben wurden, und findet einen entsprechenden Steuersatz in Spalte B. Das Argument if_not_found auf 0 (Null) zurück, wenn nichts gefunden wird. Das argument match_mode ist auf 1festgelegt, was bedeutet, dass die Funktion nach einer exakten Übereinstimmung sucht, und wenn sie eine nicht finden kann, gibt sie das nächste größere Element zurück. Schließlich ist das argument search_mode auf 1festgelegt, was bedeutet, dass die Funktion vom ersten Element bis zum letzten Element sucht.

Abbildung der XVERWEIS-Funktion, die verwendet wird, um nach einem Steuersatz auf der Grundlage des höchsten Einkommens zu suchen. Dies ist eine ungefähre Übereinstimmung. Die Formel lautet: =XVERWEIS(E2;C2:C7;B2:B7;1;1)

Hinweis: Die Spalte XARRAY lookup_array rechts neben der Spalte return_array, während SVERWEIS nur von links nach rechts aussehen kann.

———————————————————————————

Beispiel 5    verwendet eine geschachtelte XVERWEIS-Funktion, um eine vertikale und horizontale Übereinstimmung durchzuführen. Sie sucht zuerst in Spalte B nach Bruttogewinn, sucht dann in der obersten Zeile der Tabelle nach Qtr1 (Bereich C5:F5), und gibt schließlich den Wert an der Schnittmenge der beiden zurück. Dies ähnelt der gemeinsamen Verwendung der Funktionen INDEXund MATCH.

Tipp: Sie können XVERWEIS auch verwenden, um die Funktion WVERWEIS zu ersetzen.

Abbildung der XVERWEIS-Funktion, die verwendet wird, um horizontale Daten aus einer Tabelle durch Verschachteln von 2 XVERWEISEN abzurufen. Die Formel lautet: =XVERWEIS(D2;$B6:$B17;XVERWEIS($C3;$C5:$G5;$C6:$G17))

Hinweis: Die Formel in den Zellen D3:F3 ist: =XVERWEIS(D2;$B 6:$B 17;XVERWEIS($C 3.$C 5:$G 5.$C 6:$G 17)).

———————————————————————————

Beispiel 6    verwendet die Funktion SUMMEund zwei geschachtelte XVERWEIS-Funktionen, um alle Werte zwischen zwei Bereichen zu summieren. In diesem Fall möchten wir die Werte für „Trauben“, „Bananen“ einschließlich „Birnen“ addieren, die zwischen den beiden liegen.

Verwendung von XVERWEIS mit SUMME, um einen Wertebereich zu summieren, der zwischen zwei Auswahlmöglichkeiten liegt

Die Formel in Zelle E3 ist: =SUMME(XVERWEIS(B3;B6:B10;E6:E10):XVERWEIS(C3;B6:B10;E6:E10))

Wie funktioniert das? XVERWEIS gibt einen Bereich zurück. Wenn also berechnet wird, sieht die Formel so aus: =SUMME($E$7:$E$9). Sie können sehen, wie dies allein funktioniert, indem Sie eine Zelle mit einer XVERWEIS-Formel auswählen, die dieser Formel ähnelt, und dann Formeln > Formelüberwachung > Formel auswerten unddann Auswerten auswählen, um die Berechnung zu durcharbeiten.

Hinweis: Vielen Dank an Microsoft Excel MVP, Bill Jelen,für die Vorschläge für dieses Beispiel.

———————————————————————————

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers Community erhalten.

Siehe auch

XVERGLEICH (Funktion)

Excel-Funktionen (alphabetisch)

Excel-Funktionen (nach Kategorie)

Benötigen Sie weitere Hilfe?

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Office Insider werden

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×