SVERWEIS (Funktion)

Tipp: Versuchen Sie, die neue XVERWEIS-Funktion zu verwenden, eine verbesserte Version von SVERWEIS, die in beliebiger Richtung funktioniert und standardmäßig genaue Übereinstimmungen zurückgibt, wodurch die Verwendung einfacher und bequemer ist als bei der Vorgängerfunktion.

Verwenden Sie SVERWEIS, wenn Sie Elemente in einer Tabelle oder einem Bereich zeilenweise suchen müssen. Suchen Sie z. B. nach einem Preis eines Kfz-Teils anhand der Artikelnummer oder einen Mitarbeiternamen anhand der Mitarbeiter-ID.

In ihrer einfachsten Form besagt die Funktion SVERWEIS Folgendes:

=SVERWEIS(was Sie nachschlagen möchten; wo Sie nachschlagen möchten; Spaltennummer im Bereich mit dem Rückgabewert; ungefähre oder genaue Entsprechung zurückgeben – angegeben als "1/WAHR" oder "0/FALSCH").

Ihr Browser unterstützt kein Video. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.

Tipp: Das Geheimnis von SVERWEIS besteht darin, die Daten so zu ordnen, dass sich das Suchkriterium (Obst) links des Rückgabewerts befindet, den Sie finden möchten (Menge).

Verwenden Sie die Funktion SVERWEIS zum Nachschlagen eines Werts in einer Tabelle.

Syntax

SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])

Beispiel:

=SVERWEIS(A2;A10:C20;2;WAHR)
=SVERWEIS("Eitner";B2:E7;2;FALSCH)
=SVERWEIS(A2;'Clientdetails'!A:F;3;FALSCH)

Argumentname	Beschreibung
Suchkriterium (erforderlich)	Der Wert, nach dem Sie suchen möchten. Der gesuchte Wert muss sich in der ersten Spalte des Zellbereichs befinden, den Sie im *Matrix-Argument angeben. Wenn die Matrix* z. B. die Zellen B2:D7 umfasst, muss sich das Suchkriterium in Spalte B befinden. Das *Suchkriterium* kann ein Wert oder ein Bezug auf eine Zelle sein.
Matrix (erforderlich)	Der Zellbereich, in dem SVERWEIS nach dem *Suchkriterium* und dem Rückgabewert sucht. Sie können einen benannten Bereich oder eine Tabelle verwenden, und Sie können Namen im Argument anstelle von Zellbezügen verwenden. Die erste Spalte im Zellbereich muss das *Suchkriterium* enthalten. Der Zellbereich muss auch den Rückgabewert enthalten, den Sie suchen. Informationen zum Markieren von Bereichen auf einem Arbeitsblatt.
Spaltenindex (erforderlich)	Die Nummer der Spalte (beginnend mit 1 für die Spalte ganz links in der *Matrix*), die den Rückgabewert enthält.
Bereich_Verweis (optional)	Ein Wahrheitswert, der angibt, ob SVERWEIS eine ungefähre Entsprechung oder eine genaue Entsprechung suchen soll: Ungefähre Übereinstimmung – 1/WAHR setzt voraus, dass die erste Spalte in der Tabelle entweder numerisch oder alphabetisch sortiert ist, und sucht dann nach dem am nächsten liegenden Wert. Wenn Sie keine Angabe machen, ist dies ist die Standardmethode. Beispiel: =SVERWEIS(90;A1:B100;2;WAHR) Genaue Übereinstimmung – 0/FALSCH sucht nach dem exakten Wert in der ersten Spalte. Beispiel: =SVERWEIS("Schmidt";A1:B100;2;FALSCH)

Erste Schritte

Es gibt vier Arten von Informationen, die Sie zum Erstellen der SVERWEIS-Syntax benötigen:

Den Wert (Suchkriterium), den Sie nachschlagen möchten. Er wird auch als "Nachschlagewert" bezeichnet.
Den Bereich, in dem sich der Nachschlagewert befindet. Denken Sie daran, dass der Nachschlagewert immer in der ersten Spalte des Bereichs stehen sollte, damit SVERWEIS ordnungsgemäß funktioniert. Wenn Ihr Nachschlagewert z. B. in Zelle C2 steht, sollte Ihr Bereich mit "C" beginnen.
Die Spaltennummer in dem Bereich, der den Rückgabewert enthält. Wenn Sie beispielsweise "B2:D11" als Bereich angeben, sollten Sie "B" als erste Spalte zählen, "C" als zweite Spalte usw.
Optional können Sie WAHR angeben, wenn Sie eine ungefähre Übereinstimmung, oder FALSCH angeben, wenn Sie eine genaue Übereinstimmung des Rückgabewerts haben möchten. Wenn Sie nichts angeben, lautet der Standardwert immer WAHR oder ist eine ungefähre Übereinstimmung.

Stellen Sie jetzt alle vorstehenden Informationen folgendermaßen zusammen:

=SVERWEIS(Suchkriterium; Bereich mit dem Suchkriterium; Spaltennummer im Bereich mit dem Rückgabewert; ungefähre Übereinstimmung (WAHR) oder genaue Übereinstimmung (FALSCH)).

Beispiele

Hier sind einige Beispiele für SVERWEIS:

Beispiel 1

=SVERWEIS (B3,B2:E7,2,FALSE)

SVERWEIS sucht in der ersten Spalte (Spalte B) im table_array B2:E7 nach „Fontana“ und gibt „Olivier“ aus der zweiten Spalte (Spalte C) des table_array zurück. False gibt eine genaue Übereinstimmung zurück.

Beispiel 2

=SVERWEIS (102,A2:C7,2,FALSE)

SVERWEIS sucht nach einer exakten Übereinstimmung (FALSE) des Nachnamens für 102 (lookup_value) in der zweiten Spalte (Spalte B) im Bereich A2:C7 und gibt „Fontana“ zurück.

Beispiel 3

=WENN(SVERWEIS(103;A1:E7,2,FALSE)=“Sousa“,“Gefunden",“Nicht gefunden“)

WENN überprüft wird, ob SVERWEIS „Sousa“ als Nachnamen des Mitarbeiters zurückgibt, der 103 (lookup_value) in A1:E7 (table_array) entspricht. Da der Nachname für 103 „Leal“ lautet, ist die IF-Bedingung FALSE, und „Nicht gefunden“ wird angezeigt.

Beispiel 4

=GANZZAHL(BRTEILJAHRE(DATUM(2014,6,30),SVERWEIS(105,A2:E7,5,FALSE),1))

SVERWEIS sucht nach dem Geburtsdatum des Mitarbeiters, das 109 (lookup_value) im Bereich A2:E7 (table_array) entspricht, und gibt den 04.03.1955 zurück. Anschließend subtrahiert BRTEILJAHRE dieses Geburtsdatum vom 30.6.2014 und gibt einen Wert zurück, der dann von GANZZAHL in die ganze Zahl 59 konvertiert wird.

Beispiel 5

WENN(ISTNV(SVERWEIS(105;A2:E7,2,FLASE))=TRUE,“Mitarbeiter nicht gefunden",SVERWEIS(105,A2:E7,2,FALSE))

WENN überprüft, ob SVERWEIS einen Wert für den Nachnamen aus Spalte B für 105 zurückgibt (lookup_value). Wenn SVERWEIS einen Nachnamen findet, zeigt WENN den Nachnamen an, andernfalls gibt WENN „Mitarbeiter nicht gefunden“ zurück. ISTNV stellt sicher, dass, wenn SVERWEIS #N/V zurückgibt, der Fehler durch „Mitarbeiter nicht gefunden“ ersetzt wird, anstatt #N/V.

In diesem Beispiel lautet der Rückgabewert „Burke“, der dem Nachnamen gemäß 105 entspricht.

Sie können SVERWEIS verwenden, um mehrere Tabellen zu einer Tabelle zu kombinieren, solange eine der Tabellen Felder enthält, die allen anderen Tabellen gemeinsam sind. Dies kann besonders nützlich sein, wenn Sie eine Arbeitsmappe für Personen freigeben müssen, die über ältere Excel-Versionen verfügen, die Datenfeatures mit mehreren Tabellen als Datenquellen nicht unterstützen – indem Sie die Quellen in einer Tabelle zusammenfassen und die Datenquelle des Datenfeatures in die neue Tabelle ändern, kann das Datenfeature in älteren Excel-Versionen verwendet werden (vorausgesetzt, das Datenfeature selbst wird von der älteren Version unterstützt).

Ein Arbeitsblatt mit Spalten, die SVERWEIS zum Abrufen von Daten aus anderen Tabellen verwenden

Hier enthalten die Spalten A-F und H Werte oder Formeln, die nur Werte auf dem Arbeitsblatt verwenden, und der Rest der Spalten verwendet SVERWEIS und die Werte von Spalte A (Clientcode) sowie Spalte B (Anwalt), um Daten aus anderen Tabellen abzurufen.

Kopieren Sie die Tabelle mit den gemeinsamen Feldern in ein neues Arbeitsblatt, und geben Sie ihr einen Namen.
Klicken Sie auf Daten > Datentools > Beziehungen, um das Dialogfeld "Beziehungen verwalten" zu öffnen.
Beachten Sie für jede aufgelistete Beziehung Folgendes:
- Das Feld, das die Tabellen verknüpft (im Dialogfeld in Klammern aufgeführt). Dies ist das Suchkriterium für Ihre SVERWEIS-Formel.
- Der Name der verwandten Nachschlagetabelle. Dies ist die Matrix in Ihrer SVERWEIS-Formel.
- Das Feld (Spalte) in der verwandten Nachschlagetabelle, das die gewünschten Daten in der neuen Spalte enthält. Diese Informationen werden im Dialogfeld "Beziehungen verwalten" nicht angezeigt. Sie müssen sich die verwandte Nachschlagetabelle ansehen, um zu ermitteln, welches Feld Sie abrufen möchten. Notieren Sie sich die Spaltennummer (A=1) – dies ist der Spaltenindex in Ihrer Formel.
Um der neuen Tabelle ein Feld hinzuzufügen, geben Sie Ihre SVERWEIS-Formel in die erste leere Spalte ein, und verwenden Sie dabei die Informationen, die Sie in Schritt 3 gesammelt haben.

In unserem Beispiel verwendet Spalte G "Anwalt" (Suchkriterium), um die Rechnungssatz-Daten aus der vierten Spalte (Spaltenindex = 4) aus der Tabelle des Anwälte-Arbeitsblatts, "Tabelle_Anwälte" (Matrix) mit der Formel =SVERWEIS([@Anwalt];Tabelle_Anwälte;4;FALSCH) abzurufen.

Die Formel kann auch einen Zellbezug und einen Bereichsbezug verwenden. In unserem Beispiel wäre dies =SVERWEIS(A2;'Anwälte'!A:D;4;FALSCH).
Fahren Sie mit dem Hinzufügen von Feldern fort, bis alle benötigten Felder vorhanden sind. Wenn Sie versuchen, eine Arbeitsmappe vorzubereiten, die Datenfeatures enthält, die mehrere Tabellen verwenden, ändern Sie die Datenquelle des Daten-Features in die neue Tabelle.

Problem	Ursache
Falscher Wert zurückgegeben	Wenn *Bereich_Verweis* als WAHR oder gar nicht angegeben ist, muss die erste Spalte alphabetisch oder numerisch sortiert sein. Ist die erste Spalte nicht sortiert, kann es sich beim Rückgabewert um einen unerwarteten Wert handeln. Sortieren Sie entweder die erste Spalte, oder verwenden Sie FALSCH für eine genaue Entsprechung.
#NV in der Zelle	Wenn *Bereich_Verweis* als WAHR angegeben ist und der Wert im *Suchkriterium* kleiner als der kleinste Wert in der ersten Spalte der *Matrix* ist, wird der Fehlerwert "#NV" zurückgegeben. Wenn *Bereich_Verweis* FALSCH ist, zeigt der Fehlerwert "#N/A" an, dass kein exakter Wert gefunden wurde. Weitere Informationen zum Auflösen von #NV-Fehlern in SVERWEIS finden Sie unter Korrigieren eines #NV-Fehlers in der SVERWEIS-Funktion.
#BEZUG! in der Zelle	Wenn der *Spaltenindex* größer als die Anzahl der Spalten in der *Matrix* ist, wird der Fehlerwert #BEZUG! zurückgegeben. Weitere Informationen zum Auflösen von #BEZUG!- Fehlern in SVERWEIS finden Sie unter Korrigieren eines #BEZUG!-Fehlers.
#WERT! in der Zelle	Wenn die *Matrix* kleiner als 1 ist, wird der Fehlerwert #WERT! zurückgegeben. Weitere Informationen zum Auflösen von #WERT!- Fehlern in SVERWEIS finden Sie unter Korrigieren eines #WERT!-Fehlers in der SVERWEIS-Funktion..
#NAME? in der Zelle	Der Fehlerwert #NAME? bedeutet normalerweise, dass für die Angabe der Formel die Anführungszeichen fehlen. Um den Namen einer Person zu suchen, achten Sie darauf, dass Sie in der Formel den Namen in Anführungszeichen verwenden. Geben Sie z. B. den Namen in der Form "Eitner" in =SVERWEIS("Eitner";B2:E7;2;FALSCH). Weitere Informationen finden Sie unter Korrigieren eines Fehlerwerts vom Typ #NAME!..
#ÜBERLAUF! in der Zelle	Diese spezielle #ÜBERLAUF!-Fehler bedeutet normalerweise, dass sich Ihre Formel auf eine implizite Schnittmenge für den Nachschlagewert verlässt und eine ganze Spalte als Referenz verwendet. Beispiel: =SVERWEIS(A:A;A:C;2;FALSCH). Sie können das Problem beheben, indem Sie den Nachschlageverweis mit dem @-Operator wie folgt verankern: =SVERWEIS(@A:A;A:C;2;FALSCH). Alternativ können Sie die herkömmliche SVERWEIS-Methode verwenden und auf eine einzelne Zelle statt auf eine ganze Spalte verweisen: =SVERWEIS(A2;A:C;2;FALSCH).

Aktion	Grund
Verwenden Sie absolute Bezüge für Bereich_Verweis.	Bei Verwendung absoluter Bezüge können Sie eine Formel nach unten ausfüllen, sodass immer auf genau den gleichen Nachschlagebereich verwiesen wird. Informationen zur Verwendung von absoluten Zellbezügen.
Speichern Sie keine Zahlen oder Datumswerten als Text.	Bei der Suche nach Zahlen oder Datumswerten müssen Sie sicherstellen, dass die Daten in der ersten Spalte der *Matrix* nicht als Textwerte gespeichert sind. Andernfalls gibt SVERWEIS möglicherweise einen falschen oder unerwarteten Wert zurück.
Sortieren Sie die erste Spalte.	Sortieren Sie die erste Spalte der *Matrix, bevor Sie SVERWEIS verwenden, wenn Bereich_Verweis* WAHR ist.
Verwenden Sie Platzhalterzeichen.	Wenn *Bereich_Verweis* FALSCH und *Suchkriterium* Text ist, können Sie die Platzhalterzeichen – Fragezeichen (?) und Sternchen () – in Suchkriterium* verwenden. Ein Fragezeichen ersetzt ein einzelnes Zeichen. Ein Sternchen ersetzt eine beliebige Zeichenfolge. Wenn Sie nach einem Fragezeichen oder Sternchen suchen möchten, müssen Sie eine Tilde (~) vor dem zu suchenden Zeichen eingeben. Mit =SVERWEIS("Eitne?";B2:E7;2;FALSCH) wird z. B. nach allen Vorkommen von Eitner gesucht, wobei der letzte Buchstabe unterschiedlich sein kann.
Stellen Sie sicher, dass Ihre Daten keine fehlerhaften Zeichen enthalten.	Bei der Suche nach Textwerten in der ersten Spalte müssen Sie sicherstellen, dass die Daten in der ersten Spalte keine führenden oder abschließenden Leerzeichen enthalten, dass gerade ( ' bzw. " ) und typografische ( ‘ bzw. “) Anführungszeichen nicht inkonsistent verwendet wurden und dass keine nicht druckbaren Zeichen vorhanden sind. In diesen Fällen gibt SVERWEIS möglicherweise einen unerwarteten Wert zurück. Um genaue Ergebnisse zu erhalten, können Sie die Funktionen SÄUBERN und GLÄTTEN verwenden, um alle schließenden Leerzeichen nach Tabellenwerten in einer Zelle zu entfernen.