SVERWEIS

Sucht in der ersten Spalte einer Tabellenmatrix nach einem Wert und gibt in der gleichen Zeile einen Wert aus einer anderen Spalte in der Tabellenmatrix zurück.

Das S in SVERWEIS steht für senkrecht. Verwenden Sie SVERWEIS anstelle von WVERWEIS, wenn sich die Vergleichswerte in einer Spalte links der zu durchsuchenden Daten befinden.

Syntax

SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Suchkriterium    Der Wert, der in der ersten Spalte der Tabellenmatrix (Matrix: Wird verwendet, um einzelne Formeln zu erstellen, die mehrere Ergebnisse liefern oder die auf eine Gruppe von in Zeilen und Spalten angeordneten Argumenten angewendet werden. Ein Matrixbereich ist ein rechteckiger Bereich aus Zellen, die auf einer gemeinsamen Formel basieren; eine Matrixkonstante ist eine Gruppe von Konstanten, die als Argument verwendet wird.) gefunden werden soll. Bei Suchkriterium kann es sich um einen Wert oder einen Bezug handeln. Wenn der Wert kleiner als der kleinste Wert in der ersten Spalte der Matrix ist, gibt SVERWEIS einen #NV-Fehler zurück.

Matrix    Zwei oder mehr Datenspalten. Verwenden Sie einen Verweis auf einen Bereich oder einen Bereichsnamen. Die Werte in der ersten Spalte von Matrix sind die Werte, die von Suchkriterium durchsucht werden. Bei diesen Werten kann es sich um Text, Zahlen oder Wahrheitswerte handeln. Groß- und Kleinschreibung sind gleichwertig.

Spaltenindex    Die Spaltennummer in Matrix, aus der der entsprechende Wert zurückgegeben werden muss. Ein Spaltenindex von 1 gibt den Wert der ersten Spalte in Matrix zurück, ein Spaltenindex von 2 gibt den Wert der zweiten Spalte von Matrix zurück usw.

  • Wenn Spaltenindex kleiner 1 ist, gibt SVERWEIS den Fehler #WERT! zurück
  • Wenn Spaltenindex größer als die Anzahl der Spalten in Matrix ist, gibt SVERWEIS den Fehlerwert #BEZUG! zurück.

Bereich_Verweis    Ein Wahrheitswert, der angibt, ob SVERWEIS eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung suchen soll:

  • Wenn dieser Parameter WAHR ist oder weggelassen wird, wird eine ungefähre Entsprechung zurückgegeben. Wenn keine genaue Entsprechung gefunden wird, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist.

Die Werte in der ersten Spalte von Matrix müssen aufsteigend sortiert sein. Andernfalls gibt SVERWEIS möglicherweise nicht den korrekten Wert zurück. Sie können die Werte aufsteigend sortieren, indem Sie im Menü Daten auf Sortieren klicken und Aufsteigend auswählen. Unter Standardsortierreihenfolgen finden Sie weitere Informationen.

  • Wenn der Parameter FALSCH ist, sucht SVERWEIS eine genaue Entsprechung. In diesem Fall müssen die Werte in der ersten Spalte von Matrix nicht sortiert werden. Wenn zwei oder mehr Werte in der ersten Spalte von Matrix mit Suchkriterium übereinstimmen, wird der erste gefundene Wert verwendet. Wenn keine genaue Entsprechung gefunden wird, wird der Fehlerwert #NV zurückgegeben.

Hinweise

  • Stellen Sie beim Suchen von Textwerten in der ersten Spalte von Matrix sicher, dass die Daten in der ersten Spalte von Matrix keine vor- oder nachstehenden Leerzeichen, uneinheitlichen (d. h. gerade (' oder ") und typografische (‘ oder “)) Anführungszeichen oder nicht druckbaren Zeichen enthält. In diesen Fällen gibt VLOOKUP möglicherweise einen falschen oder unerwarteten Wert zurück. Weitere Informationen zu Funktionen, die Sie zum Bereinigen von Textdaten verwenden können, finden Sie unter Text- und Datenfunktionen.
  • Stellen Sie beim Suchen nach Zahlen- oder Datumswerten sicher, dass die Daten in der ersten Spalte von Matrix nicht als Textwerte gespeichert sind. SVERWEIS kann in diesem Fall einen falschen oder unerwarteten Wert zurückgeben. Unter Konvertieren von Zahlen, die als Text gespeichert wurden finden Sie weitere Informationen.
  • Wenn Bereich_Verweis FALSCH ist und es sich bei Suchkriterium um Text handelt, können Sie die Platzhalterzeichen Fragezeichen (?) und Sternchen (*) in Suchkriterium verwenden. Ein Fragezeichen ersetzt ein Zeichen; ein Sternchen ersetzt eine beliebige Zeichenfolge. Wenn Sie nach einem Fragezeichen oder Sternchen suchen möchten, müssen Sie eine Tilde (~) vor das zu suchende Zeichen setzen.

Beispiel 1

Möglicherweise wird das Beispiel verständlicher, wenn Sie es in ein leeres Arbeitsblatt kopieren.

AnzeigenSo kopieren Sie ein Beispiel

  • Erstellen Sie eine leere Arbeitsmappe oder ein leeres Arbeitsblatt.
  • Wählen Sie das Beispiel im Hilfethema aus.

 Hinweis   Markieren Sie dabei nicht die Zeilen- oder Spaltenüberschriften.

Auswählen eines Beispiels aus der Hilfe

Auswählen eines Beispiels aus der Hilfe
  • Drücken Sie STRG+C.
  • Markieren Sie im Arbeitsblatt die Zelle A1, und drücken Sie dann STRG+V.
  • Um zwischen der Anzeige der Ergebnisse und der Anzeige der Formeln, die diese Ergebnisse zurückgeben, zu wechseln, drücken Sie STRG+` (Gravis), oder klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Formeln anzeigen.

In diesem Beispiel wird die Spalte Druck in einer Tabelle mit atmosphärischen Eigenschaften durchsucht, um die entsprechenden Werte in den Spalten Viskosität und Temperatur zu ermitteln. (Die Werte beziehen sich auf Luft bei 0 °C auf Normalnull oder 1 Atmosphäre.)

 
1
2
3
4
5
6
7
8
9
10
A B C
Druck Viskosität Temperatur 
0,457 3,55 500
0,525 3,25 400
0,616 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
1,09 1,95 50
1,29 1,71 0
Formel Beschreibung (Ergebnis)
=SVERWEIS(1;A2:C10;2) Sucht nach einer ungefähren Übereinstimmung mit dem Wert 1 in Spalte A, findet den größten Wert, der in Spalte A kleiner oder gleich 1 ist (0,946), und gibt dann den Wert aus Spalte B in der gleichen Zeile zurück (2,17).
=SVERWEIS(1;A2:C10;3;WAHR) Sucht nach einer ungefähren Übereinstimmung mit dem Wert 1 in Spalte A, findet den größten Wert, der in Spalte A kleiner oder gleich 1 ist (0,946), und gibt dann den Wert aus Spalte C in der gleichen Zeile zurück (100).
=SVERWEIS(0,7;A2:C10;3;FALSCH) Sucht nach einer genauen Übereinstimmung mit dem Wert 0,7 in Spalte A. Da Spalte A keine genaue Übereinstimmung enthält, wird ein Fehler zurückgegeben (#NV).
=SVERWEIS(0,1;A2:C10;2;WAHR) Sucht nach einer ungefähren Übereinstimmung mit dem Wert 0,1 in Spalte A. Da 0,1 kleiner als der kleinste Wert in Spalte A ist, wird ein Fehler zurückgegeben (#NV).
=SVERWEIS(2;A2:C10;2;WAHR) Sucht nach einer ungefähren Übereinstimmung mit dem Wert 2 in Spalte A, findet den größten Wert, der in Spalte A kleiner oder gleich 2 ist (1,29), und gibt dann den Wert aus Spalte B in der gleichen Zeile zurück (1,71).

Beispiel 2

Möglicherweise wird das Beispiel verständlicher, wenn Sie es in ein leeres Arbeitsblatt kopieren.

AnzeigenSo kopieren Sie ein Beispiel

  • Erstellen Sie eine leere Arbeitsmappe oder ein leeres Arbeitsblatt.
  • Wählen Sie das Beispiel im Hilfethema aus.

 Hinweis   Markieren Sie dabei nicht die Zeilen- oder Spaltenüberschriften.

Auswählen eines Beispiels aus der Hilfe

Auswählen eines Beispiels aus der Hilfe
  • Drücken Sie STRG+C.
  • Markieren Sie im Arbeitsblatt die Zelle A1, und drücken Sie dann STRG+V.
  • Um zwischen der Anzeige der Ergebnisse und der Anzeige der Formeln, die diese Ergebnisse zurückgeben, zu wechseln, drücken Sie STRG+` (Gravis), oder klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Formeln anzeigen.

In diesem Beispiel wird die Spalte Artikel-Nr in einer Tabelle mit Babyartikeln durchsucht. Dann werden anhand der Werte in den Spalten Kosten und Gewinnspanne Preise berechnet und Bedingungen überprüft.

 
1
2
3
4
5
6
A B C D
Artikel-Nr Artikel Kosten Gewinnspanne
ST-340 Kinderwagen 145,67 € 30%
BI-567 Lätzchen 3,56 € 40%
DI-328 Windeln 21,45 € 35%
WI-989 Tücher 5,12 € 40%
AS-469 Nasensauger 2,56 € 45%
Formel Beschreibung (Ergebnis)
=SVERWEIS("DI-328"; A2:D6; 3; FALSCH) * (1 + SVERWEIS("DI-328"; A2:D6; 4; FALSCH)) Berechnet den Verkaufspreis für Windeln, indem die Kosten und der Prozentwert für die Gewinnspanne addiert werden. (28,96 €)
=(SVERWEIS("WI-989"; A2:D6; 3; FALSCH) * (1 + SVERWEIS("WI-989"; A2:D6; 4; FALSCH))) * (1 - 20%) Berechnet den Angebotspreis für Tücher, indem ein bestimmter Rabatt vom Verkaufspreis abgezogen wird. (5,73 €)
= WENN(SVERWEIS(A2; A2:D6; 3; FALSCH) >= 20; "Die Gewinnspanne beträgt " & 100 * SVERWEIS(A2; A2:D6; 4; FALSCH) &"%"; "Kosten liegen unter 20,00 €") Wenn die Kosten für einen Artikel größer oder gleich 20,00 € sind, wird der Text Die Gewinnspanne beträgt angezeigt, andernfalls Die Kosten liegen unter 20,00 €. (Gewinnspanne beträgt 30%)
= WENN(SVERWEIS(A3; A2:D6; 3; FALSCH) >= 20; "Die Gewinnspanne beträgt: " & 100 * SVERWEIS(A3; A2:D6; 4; FALSCH) &"%"; "Kosten (Euro) " & SVERWEIS(A3; A2:D6; 3; FALSCH)) Wenn die Kosten für einen Artikel größer oder gleich 20,00 € sind, wird der Text Die Gewinnspanne beträgt angezeigt, andernfalls die Kosten in Euro. (Kosten 3,56 €)

Beispiel 3

Möglicherweise wird das Beispiel verständlicher, wenn Sie es in ein leeres Arbeitsblatt kopieren.

AnzeigenSo kopieren Sie ein Beispiel

  • Erstellen Sie eine leere Arbeitsmappe oder ein leeres Arbeitsblatt.
  • Wählen Sie das Beispiel im Hilfethema aus.

 Hinweis   Markieren Sie dabei nicht die Zeilen- oder Spaltenüberschriften.

Auswählen eines Beispiels aus der Hilfe

Auswählen eines Beispiels aus der Hilfe
  • Drücken Sie STRG+C.
  • Markieren Sie im Arbeitsblatt die Zelle A1, und drücken Sie dann STRG+V.
  • Um zwischen der Anzeige der Ergebnisse und der Anzeige der Formeln, die diese Ergebnisse zurückgeben, zu wechseln, drücken Sie STRG+` (Gravis), oder klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Formeln anzeigen.

In diesem Beispiel wird die Spalte Personal-Nr in einer Tabelle mit Personaldaten durchsucht und mit Werten in anderen Spalten verglichen, um Altersangaben zu berechnen und Fehlerbedingungen zu überprüfen.

 
1
2
3
4
5
6
7
A B C D E
Personal-Nr Nachname Vorname Position Geburtsdatum
1 Davolio Nancy Vertriebsmitarbeiterin 08. Dez. 1968
2 Fuller Andrew Stellvertr. Geschäftsführer Vertrieb 19. Feb. 1952
3 Leverling Janet Vertriebsmitarbeiterin 30. Aug. 1963
4 Peacock Margaret Vertriebsmitarbeiterin 19. Sep. 1958
5 Buchanan Steven Vertriebsmanager 04. Mär. 1955
6 Suyama Michael Vertriebsmitarbeiter 02. Jul. 1963
Formel Beschreibung (Ergebnis)
=GANZZAHL(BRTEILJAHRE(DATUM(2004;6;30); SVERWEIS(5;A2:E7;5; FALSCH); 1)) Ermittelt für das Geschäftsjahr 2004 das Alter des Mitarbeiters mit der Personalnummer 5. Mit der BRTEILJAHRE-Funktion wird das Geburtsdatum vom Enddatum des Geschäftsjahrs abgezogen, und das Ergebnis wird mithilfe der GANZZAHL-Funktion als Ganzzahl angezeigt. (49)
=WENN(ISTNV(SVERWEIS(5;A2:E7;2;FALSCH)) = WAHR; "Mitarbeiter nicht gefunden"; SVERWEIS(5;A2:E7;2;FALSCH))

Wenn ein Mitarbeiter mit der Personalnummer 5 vorhanden ist, wird der Nachname des Mitarbeiters angezeigt, andernfalls die Meldung „Mitarbeiter nicht gefunden“. (Buchanan)

Die ISTNV-Funktion gibt den Wert WAHR zurück, wenn SVERWEIS den Fehlerwert #NA zurückgibt.

=WENN(ISTNV(SVERWEIS(15;A3:E8;2;FALSCH)) = WAHR; "Mitarbeiter nicht gefunden"; SVERWEIS(15;A3:E8;2;FALSCH))

Wenn ein Mitarbeiter mit der Personalnummer 15 vorhanden ist, wird der Nachname des Mitarbeiters angezeigt, andernfalls die Meldung „Mitarbeiter nicht gefunden“. (Mitarbeiter nicht gefunden)

Die ISTNV-Funktion gibt den Wert WAHR zurück, wenn SVERWEIS den Fehlerwert #NA zurückgibt.

=SVERWEIS(4;A2:E7;3;FALSCH) & " " & SVERWEIS(4;A2:E7;2;FALSCH) & " ist " & SVERWEIS(4;A2:E7;4;FALSCH) & "." Verkettet für den Mitarbeiter mit der Personalnummer 4 die Werte aus drei Zellen zu einem vollständigen Satz. (Margaret Peacock ist Vertriebsmitarbeiterin.)

 Hinweis   In der ersten Formel im obenstehenden Bespiel wird die Funktion BRTEILJAHRE verwendet. Wenn diese Funktion nicht verfügbar ist und der Fehler #NAME? zurückgegeben wird, installieren und laden Sie das Add-In Analyse-Funktionen.

AnzeigenWie wird's gemacht?

  1. Klicken Sie im Menü Extras auf Add-Ins.
  2. Wählen Sie in der Liste Verfügbare Add-Ins das Feld Analyse-Funktionen aus, und klicken Sie dann auf OK.
  3. Falls erforderlich, befolgen Sie die Anweisungen im Setup-Programm.
 
 
Zutreffend für:
Excel 2003