Zu Hauptinhalt springen

XVERWEIS (Funktion)

Verwenden Sie die XVERWEIS-Funktion, wenn Sie Einträge in einer Tabelle oder einem Bereich nach Zeile suchen müssen. Suchen Sie z. B. nach dem Preis eines Kfz-Teils anhand der Artikelnummer oder einen Mitarbeiternamen anhand der 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. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.

Hinweis: 28. August 2019: XVERWEIS ist derzeit ein Beta-Feature und zu diesem Zeitpunkt nur für einen Teil der Office Insider verfügbar. Wir werden dieses Feature in den nächsten Monaten weiter optimieren. Wenn die XVERWEIS-Funktion fertig ist, werden wir sie für alle Office Insider und Office 365-Abonnenten veröffentlichen.

Die XVERWEIS-Funktion sucht nach einem Bereich oder Array und gibt ein Element zurück, das zur ersten gefundenen Übereinstimmung passt. Wenn es keine Übereinstimmung gibt, kann XVERWEIS die beste (ungefähre) Übereinstimmung zurückgeben. 

= XLOOKUP (Kriterium; Suchmatrix; return_array; [match_mode]; [search_mode]; [if_not_found]) 

Argument

Beschreibung

Suchkriterium

Erforderlich

Das Suchkriterium

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

[match_mode]

Optional

Geben Sie den Übereinstimmungstyp an:

0: genaue Übereinstimmung. Wenn keine gefunden wurde, geben Sie #N. v. zurück. 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 innehaben.

[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.

[if_not_found]

Optional

Wenn keine gültige Übereinstimmung gefunden wird, geben Sie den von Ihnen bereitgestellten [if_not_found]-Text zurück.

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

Beispiele

Beispiel 1

Dieses Beispiel beruht auf dem obigen Video und verwendet eine einfache XLOOKUP, um einen Landesnamen zu finden, und geben Sie dann die Landesvorwahl des Telefons zurück. Er umfasst nur die Argumente „Suchkriterium“ (Zelle F2), „Suchmatrix“ (Bereich B2:B11) und „Rückgabematrix“ (Bereich D2:D11). Es enthält nicht das match_mode-Argument, da XLOOKUP standardmäßig eine exakte Übereinstimmung aufweist.

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 unterscheidet sich von SVERWEIS insofern, als dass er separate Such- und Rückgabematrizen verwendet, wohingegen SVERWEIS ein einzelnes Tabellenarray, gefolgt von einer Spaltenindexnummer, verwendet. Die äquivalente SVERWEIS-Formel wäre in diesem Fall: =SVERWEIS(F2,B2:D11,3,FALSCH)

Beispiel 2

In diesem Beispiel suchen wir Mitarbeiterdaten anhand einer Mitarbeiter-ID. Im Gegensatz zu SVERWEIS kann XLOOKUP ein Array mit mehreren Elementen zurückgeben, wodurch eine einzelne Formel sowohl den Mitarbeiternamen als auch die Abteilung zurückgeben kann (Zellen C5: D14).

Beispiel für die XLOOKUP-Funktion, die verwendet wird, um einen Mitarbeiternamen und eine Abteilung basierend auf dem Mitarbeiter IDt zurückzugeben. Die Formel lautet: = XLOOKUP (B2; B5: B14; C5: D14; 0; 1)

Beispiel 3

In diesem Beispiel wird das if_not_found -Argument dem obigen Beispiel hinzugefügt.

Beispiel für die XLOOKUP-Funktion, die zum Zurückgeben eines Mitarbeiter namens und einer Abteilung basierend auf der Mitarbeiter-ID mit dem Argument if_not_found verwendet wird. Die Formel lautet = XLOOKUP (B2; B5: B14; C5: D14; 0; 1; "Mitarbeiter nicht gefunden")

Beispiel 4

Im folgenden Beispiel wird in Spalte C nach dem in Zelle E2 eingegebenen persönlichen Einkommen gesucht, und es wird ein übereinstimmender Steuersatz in Spalte B gefunden. Das Argument „Vergleichsmodus“ ist auf „1“ festgelegt, was bedeutet, dass die Funktion nach einer exakten Übereinstimmung sucht, und wenn sie keine findet, das nächstgrößere Element zurückgibt.

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: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Hinweis: Im Gegensatz zu SVERWEIS befindet sich die Spalte „Suchmatrix“ rechts neben der Spalte „Rückgabematrix“, wobei SVERWEIS nur von links nach rechts suchen kann.

Beispiel 5

Im nächsten Schritt wenden wir eine geschachtelte XVERWEIS-Funktion an, um sowohl einen vertikalen als auch horizontalen Vergleich durchzuführen. In diesem Fall sucht sie zuerst nach Bruttogewinn in Spalte B und anschließend in der obersten Zeile der Tabelle nach Qrtl1 (Bereich C5:F5). Der Wert wird an der Schnittstelle der beiden Werte zurückgegeben. Dies ähnelt der gleichzeitigen Verwendung der Funktionen INDEX und VERGLEICH. 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 zwei XVERWEISEN abzurufen. Die Formel lautet: = XLOOKUP (D2; $B 6: $B 17, XLOOKUP ($C 3; $C 5: $G 5; $C 6: $G 17))

Die Formel in den Zellen D3:F3 lautet: =XVERWEIS(D2,$B6:$B17,XVERWEIS($C3,$C5:$G5,$C6:$G17)).

Beispiel 6

In diesem Beispiel werden die SUM-Funktion und zwei ineinander verschachtelte XVERWEIS-Funktionen verwendet, um alle Werte zwischen zwei Bereichen zu summieren. In diesem Fall möchten wir die Werte für Trauben, Bananen und Birnen addieren, die zwischen den beiden liegen.

Verwenden von XLOOKUP mit Sum, um einen Bereich von Werten zu summieren, die zwischen zwei Auswahlen liegen

Die Formel in Zelle E3 lautet: = Summe (XLOOKUP (B3; B6: B10; E6: E10): XLOOKUP (C3; B6: B10; E6: E10))

Wie funktioniert das? XLOOKUP gibt einen Bereich zurück, sodass die Formel bei der Berechnung wie folgt aussieht: = Summe ($E $7: $E $9). Sie können selbst sehen, wie dies funktioniert, indem Sie eine Zelle mit einer XVERWEIS-Formel auswählen, die dieser ähnlich ist. Wechseln Sie dann zu Formeln > Formelüberwachung > Formelauswertung und drücken Sie auf die Schaltfläche Auswerten, um die Berechnung durchzuführen.

Hinweis: Vielen Dank an Microsoft Excel MVP Bill Jelen für dieses vorgeschlagene Beispiel.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

Siehe auch

XVERGLEICH (Funktion)

Excel-Funktionen (alphabetisch)

Excel-Funktionen (nach Kategorie)

Hinweis:  Diese Seite wurde automatisiert übersetzt und kann Grammatikfehler oder Ungenauigkeiten enthalten. Unser Ziel ist es, Ihnen hilfreiche Inhalte bereitzustellen. Lassen Sie uns bitte wissen, ob diese Informationen hilfreich für Sie waren. Hier finden Sie den englischen Artikel als Referenz.

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.

×