SVERWEIS

 

Wenn Sie den Inhalt einer Tabelle kennen, können Sie mit der Funktion SVERWEIS zeilenweise nach verbundenen Informationen suchen. So haben Sie zum Beispiel in einer Tabelle mit Obstdaten die Möglichkeit, für den Kauf einer Orange mit SVERWEIS nach dem Preis zu suchen.

SVERWEIS für BigQuery

Senkrechter Verweis. Gibt die Werte in einer Datenspalte an der Position zurück, an der eine Übereinstimmung in der Suchspalte gefunden wurde.

Verwendungsbeispiel

=SVERWEIS("Apfel";Tabellenname!Obst;Tabellenname!Preis)

Syntax

SVERWEIS(Suchschlüssel; Bereich; Index; ist_sortiert)

• Suchschlüssel: Wert, nach dem in der Suchspalte gesucht wird.
• Suchspalte: Die Datenspalte, die für die Suche berücksichtigt werden soll.
• Ergebnisspalte: Die Datenspalte, die für das Ergebnis berücksichtigt werden soll.
• ist_sortiert: [OPTIONAL] Gibt an, welche Art von Übereinstimmung mit dem Suchschlüssel ermittelt werden soll.
  • FALSE: Option für eine genaue Übereinstimmung (empfohlen).
  • TRUE: Option für eine ungefähre Übereinstimmung (Standardwert, wenn ist_sortiert nicht festgelegt ist).
    Tipp: Wenn Sie die ungefähre Übereinstimmung anwenden möchten, sollten Sie zuvor die Suchschlüsselspalte in aufsteigender Reihenfolge sortieren. Andernfalls besteht die Gefahr, dass der Rückgabewert nicht korrekt ist. Mögliche Ursachen für falsche Rückgabewerte

Tipp: Mehr Flexibilität bei Datenbankabfragen in BigQuery bietet XLOOKUP.

Syntax

=SVERWEIS(Suchschlüssel; Bereich; Index; [ist_sortiert])

Eingaben

1. Suchschlüssel: Der Wert, nach dem in der ersten Spalte des Bereichs gesucht werden soll.
2. Bereich: Der obere und untere Wert, der für die Suche berücksichtigt werden soll.
3. Index: Der Index der Spalte mit dem Rückgabewert des Bereichs. Der Index muss eine positive Ganzzahl sein.
4. ist_sortiert: Diese Eingabe ist optional. Wählen Sie eine Option aus:
   • FALSE = genaue Übereinstimmung. Diese Option wird empfohlen.
   • TRUE = ungefähre Übereinstimmung. Das ist die Standardeinstellung, wenn ist_sortiert nicht festgelegt ist.
     Wichtig: Wenn Sie die ungefähre Übereinstimmung anwenden möchten, sollten Sie zuvor die Suchschlüsselspalte in aufsteigender Reihenfolge sortieren. Andernfalls besteht die Gefahr, dass der Rückgabewert nicht korrekt ist. Mögliche Ursachen für falsche Rückgabewerte

Rückgabewert

Das ist der erste übereinstimmende Wert im ausgewählten Bereich.

Technische Details:

Beispiele:

=SVERWEIS(G9; B4:D8; 3; FALSE)

=SVERWEIS("Apfel"; B4:D8; 3; TRUE)

Eingaben	Beschreibung
Suchschlüssel	Das ist der Wert, der in der ersten Spalte von Bereich gesucht wird. Für ein korrektes Ergebnis muss der Suchschlüssel in der ersten Spalte von Bereich enthalten sein. Der Zellbezug wird ebenfalls unterstützt. Beispiel: Wenn sich der Wert von Suchschlüssel in B3 befindet, muss der Bereich mit Spalte B beginnen.
Bereich	Das ist der Bereich für die Suche, wobei Folgendes gilt: Die Funktion sucht in der ersten Spalte des Bereichs nach dem angegebenen Suchschlüssel. SVERWEIS gibt den Wert aus der durch Index festgelegten Spalte zurück. Sie können auch einen benannten Bereich verwenden. Damit ein korrekter Wert zurückgegeben wird, muss der Suchschlüssel in der ersten Spalte von Bereich enthalten sein. Beispiel: Wenn sich der Wert von Suchschlüssel in B3 befindet, muss der Bereich mit Spalte B beginnen.
Index	Wird auch als „Spaltennummer“ bezeichnet. Das ist der Index der Spalte in Bereich, die den Rückgabewert enthält. Der Wert für „Index“ muss mindestens 1 sein. Der maximale Wert für „Index“ ist die Gesamtzahl der Spalten in Bereich. In einem eingerichteten Bereich prüft SVERWEIS nur die Spalte für den Suchschlüssel, wenn „Index“ den Wert 1 hat, oder die Spalten, die weiter rechts stehen. Tipp: Für die Verwendung von SVERWEIS ist es hilfreich, sich die Spalten von Bereich von links nach rechts durchnummeriert und mit 1 beginnend vorzustellen.
ist_sortiert	Diese Eingabe ist optional. Dafür sind die Optionen TRUE und FALSE verfügbar. Wenn für ist_sortiert die Option TRUE festgelegt ist, gilt für SVERWEIS die ungefähre Übereinstimmung. Wichtig: Wenn Sie die ungefähre Übereinstimmung anwenden möchten, sollten Sie zuvor die Suchschlüsselspalte in aufsteigender Reihenfolge sortieren. Andernfalls besteht die Gefahr, dass der Rückgabewert nicht korrekt ist. Mögliche Ursachen für falsche Rückgabewerte Wenn für ist_sortiert der Wert FALSE festgelegt ist, gilt für SVERWEIS die genaue Übereinstimmung. Wenn ist_sortiert nicht angegeben ist, gilt standardmäßig der Wert TRUE. Dringende Empfehlung: Verwenden Sie für ist_sortiert die Option FALSE, da das eine konsistente Vorgehensweise garantiert, unabhängig davon, ob die Suchschlüsselspalte sortiert ist. Zur besseren Lesbarkeit sollte immer ist_sortiert festgelegt werden, auch wenn die Eingabe optional ist.

 

Ergebnis

Beschreibung

Rückgabewert

Das ist der Wert, den SVERWEIS gemäß Ihren Eingaben zurückgibt. Für jede SVERWEIS-Funktion ist immer nur ein Rückgabewert vorhanden. Sind mehrere übereinstimmende Suchschlüsselwerte vorhanden, wird der Wert in der Rückgabewertspalte zurückgegeben, dessen zugeordneter Suchschlüssel als Erstes mit dem Wert in der Suchschlüsselspalte übereinstimmt. Wenn #N/A zurückgegeben wird, wurde kein Wert gefunden. Wird ein unerwarteter Wert oder ein Fehler wie #N/A oder #VALUE! zurückgegeben, sollten Sie das Problem beheben. Wenn Sie #N/A durch einen anderen Wert ersetzen möchten, finden Sie hier Informationen zur Verwendung von WENNNV() für SVERWEIS().

Einfache Beispiele für SVERWEIS:

SVERWEIS für verschiedene Suchschlüssel

Im Folgenden wird mit SVERWEIS der Preis für eine Orange und einen Apfel ermittelt.

Ausprobieren

Erklärung:

Bei der Verwendung von SVERWEIS können Sie verschiedene Suchschlüssel wie „Apfel“ und „Orange“ nutzen.

Damit ein korrekter Wert zurückgegeben wird, müssen sich diese Suchschlüssel in der ersten Spalte von Bereich befinden. Wenn Sie keinen Wert für die Suchschlüssel eingeben möchten, können Sie auch einen Zellbezug verwenden, z. B. „G9“.

Suchschlüssel ist „Orange“	=SVERWEIS("Orange"; B4:D8; 3; FALSE) Rückgabewert = 1,01 $
Suchschlüssel ist „Apfel“	=SVERWEIS("Apfel"; B4:D8; 3; FALSE) Rückgabewert = 1,50 $
Suchschlüssel mit Zellbezug zu „Apfel“ in G9	=SVERWEIS(G9; B4:D8; 3; FALSE) Rückgabewert = 1,50 $

SVERWEIS für unterschiedliche Spaltenindexe

Im Folgenden wird mit SVERWEIS die Menge der Orangen in der zweiten Indexspalte ermittelt.

Ausprobieren

Erklärung:

Für die Verwendung von SVERWEIS ist es hilfreich, sich die Spalten von Bereich von links nach rechts durchnummeriert und mit 1 beginnend vorzustellen. Wenn Sie die Zielinformationen ermitteln möchten, müssen Sie den zugehörigen Spaltenindex angeben. Beispiel: Spalte 2 für die Menge.

Index = 2 Die Menge der Orangen soll in der zweiten Spalte von Bereich ermittelt werden.

=SVERWEIS(G3; B4:D8; 2; FALSE) Rückgabewert = 5

Genaue oder ungefähre Übereinstimmung für SVERWEIS

• Wenn Sie die exakte ID ermitteln möchten, legen Sie für SVERWEIS die genaue Übereinstimmung fest.
• Wenn Sie die ID ungefähr ermitteln möchten, geben Sie für SVERWEIS die ungefähre Übereinstimmung an.

Ausprobieren

Erklärung:

Verwenden Sie die ungefähre Übereinstimmung oder für ist_sortiert die Option TRUE, wenn Sie nach der bestmöglichen und nicht nach der genauen Übereinstimmung suchen.

Wenn Sie nach der ID = 102 suchen, die in der Tabelle nicht vorhanden ist, wird bei Festlegung der ungefähren Übereinstimmung der Wert davor zurückgegeben, also ID = 101. Das liegt daran, dass der Wert 101 in der Suchschlüsselspalte der am nächsten liegende Wert ist, der kleiner als 102 ist.

Wenn die ungefähre Übereinstimmung festgelegt ist, wird in der Spalte mit dem Suchschlüssel so lange abwärts gesucht, bis ein Wert gefunden wird, der größer als der angegebene Suchschlüssel ist. Dann stoppt die Suche bei der Zeile vor dem größeren Wert und gibt den Wert aus der Rückgabewertspalte in dieser Zeile zurück. Das bedeutet, dass höchstwahrscheinlich ein falscher Rückgabewert ausgegeben wird, wenn die Suchschlüsselspalte nicht in aufsteigender Reihenfolge sortiert ist.

Wichtig: Wenn Sie die ungefähre Übereinstimmung anwenden möchten, sollten Sie zuvor die Suchschlüsselspalte in aufsteigender Reihenfolge sortieren. Andernfalls besteht die Gefahr, dass der Rückgabewert nicht korrekt ist.

Wenn nach einer genauen Übereinstimmung gesucht werden soll, muss für ist_sortiert der Wert FALSE festgelegt werden. Angenommen, der Name der Obstsorte für ID = 103 lautet „Banane“. Wenn dafür keine genaue Übereinstimmung vorhanden ist, wird der Fehler #N/A ausgegeben. Wir empfehlen die Anwendung der genauen Übereinstimmung, da ihre Ergebnisse eindeutig sind.

Genaue Übereinstimmung	=SVERWEIS(G6; A4:D8; 2; FALSE) Rückgabewert = „Apfel“
Ungefähre Übereinstimmung	=SVERWEIS(G3; A4:D8; 2; TRUE) ODER =SVERWEIS(G3; A4:D8; 2) Rückgabewert = „Banane“

Gängige Anwendungen von SVERWEIS

Fehlerwert aus SVERWEIS ersetzen

Wenn der Suchschlüssel nicht vorhanden ist, haben Sie die Möglichkeit, den von SVERWEIS zurückgegebenen Fehlerwert zu ersetzen. So können Sie, wenn Sie z. B. „#N/A“ nicht verwenden möchten, diesen Wert mit der Funktion WENNNV() ersetzen. Weitere Informationen zu WENNNV()

Ausprobieren

Ursprünglich gibt SVERWEIS #N/A zurück, da der Suchschlüssel „Stift“ in der Spalte „Obst“ nicht vorhanden ist. WENNNV() ersetzt den Fehler #N/A durch die zweite Eingabe in der Funktion. In unserem Fall ist das „NICHT GEFUNDEN“.

=WENNNV(SVERWEIS(G3; B4:D8; 3; FALSE);"NICHT GEFUNDEN") Rückgabewert = „NICHT GEFUNDEN“

Tipp: Wenn Sie andere Fehlerwerte wie #REF! ersetzen möchten, finden Sie hier weitere Informationen zu WENNFEHLER().

SVERWEIS mit mehreren Kriterien

SVERWEIS kann nicht direkt auf mehrere Kriterien angewendet werden. Dazu müssen Sie stattdessen eine neue Hilfsspalte erstellen, um SVERWEIS direkt auf mehrere Kriterien anzuwenden und mehrere vorhandene Spalten zu kombinieren.

Ausprobieren

1. Sie können eine Hilfsspalte durch Kombination von Vor- und Nachname mit „&“ erstellen.	=C4&D4 und ziehen Sie den Namen von B4 nach B8, um die Hilfsspalte einzublenden.
2. Verwenden Sie den Zellbezug B7 (JohnLee) als Suchschlüssel.	=SVERWEIS(B7; B4:E8; 4; FALSE) Rückgabewert = „Support“

SVERWEIS mit Platzhalter- oder Teilübereinstimmungen

Für SVERWEIS können Sie auch Platzhalter oder Teilübereinstimmungen verwenden. Es stehen folgende Platzhalterzeichen zur Verfügung:

• Ein Fragezeichen „?“ entspricht einem einzelnen Zeichen.
• Ein Sternchen „*“ entspricht einer beliebigen Zeichenfolge.

Um Platzhalter in SVERWEIS verwenden zu können, müssen Sie die genaue Übereinstimmung in Form von ist_sortiert = FALSE festlegen.

Ausprobieren

Mit „St*“ werden unabhängig von der Anzahl der Zeichen alle Begriffe ermittelt, die mit „St“ beginnen, wie „Steve“, „St1“, „Stock“ oder „Steeeeve“.

=SVERWEIS("St*"; B4:D8; 3; FALSE) Rückgabewert = „Marketing“

Fehlerbehebung und Best Practices:

Falscher Rückgabewert

• Ein unerwarteter Wert wird zurückgegeben: Wenn Sie für ist_sortiert die Option TRUE festgelegt haben, die erste Spalte im Bereich aber nicht numerisch oder alphabetisch in aufsteigender Reihenfolge sortiert ist, ändern Sie „ist_sortiert“ in FALSE.

• SVERWEIS gibt die erste Übereinstimmung zurück: SVERWEIS gibt nur die erste Übereinstimmung zurück. Wenn mehrere übereinstimmende Suchschlüssel vorhanden sind, wird eventuell ein unerwarteter Wert zurückgegeben.
• Nicht bereinigte Daten: Manchmal sind Werte mit unterschiedlichen vorangehenden und nachgestellten Leerzeichen inhaltlich identisch, werden aber von SVERWEIS als verschieden behandelt. Beispiele für unterschiedliche Werte für SVERWEIS:
  • Apfel
  • „Apfel “
  • „Apfel“

Für korrekte Ergebnisse sollten deshalb überflüssige Leerzeichen entfernt werden, bevor SVERWEIS angewendet wird.

#N/A

• Wenn die ungefähre Übereinstimmung oder ist_sortiert = TRUE festgelegt ist und der Suchschlüssel in SVERWEIS kleiner als der kleinste Wert in der ersten Spalte ist, wird von SVERWEIS #N/A zurückgegeben.
• Es wird die genaue Übereinstimmung oder ist_sortiert = FALSE verwendet und in der ersten Spalte keine genaue Übereinstimmung mit dem Suchschlüssel in SVERWEIS festgestellt. Wenn dann die Ausgabe #N/A nicht verwendet werden soll, nachdem der Suchschlüssel in der ersten Spalte nicht gefunden wurde, steht dafür die Funktion WENNNV() zur Verfügung.

#REF!

Sie haben eventuell irrtümlich einen Index mit einer Zahl angegeben, die größer als die Gesamtzahl der Spalten von Bereich ist. Beachten Sie zur Vermeidung dieses Fehlers Folgendes:

• Grundlage für die Berechnung der Gesamtzahl der Spalten sind die Spalten im ausgewählten Bereich, nicht in der gesamten Tabelle.
• Die Zählung beginnt bei 1 und nicht bei 0.

#VALUE!

Wenn #VALUE ausgegeben wird, liegt das möglicherweise an folgenden Eingaben:

• Sie haben den Text oder den Spaltennamen für Index falsch eingegeben.
• Sie haben für Index eine Zahl kleiner als 1 eingegeben. Der Wert für Index muss mindestens 1 und kleiner als die Gesamtzahl der Spalten von Bereich sein. Mit SVERWEIS kann nur in der Suchschlüsselspalte gesucht werden, wenn Index = 1, oder in Spalten, die weiter rechts stehen.

Wichtig: Für Index sind nur Zahlen zulässig.

#NAME?

• Möglicherweise haben Sie im Suchschlüssel ein Anführungszeichen vergessen, wenn Suchschlüssel Textdaten enthält.

Best Practices

 

Zu erledigen	Begründung
Absolute Verweise für Bereich verwenden	Sie sollten Folgendes anwenden: Absoluten Verweis für Bereich in SVERWEIS SVERWEIS(G3; $B$3:$D$7; 3; FALSE) Folgendes sollte nicht angewendet werden: SVERWEIS(G3; B3:D7; 3; FALSE) Das verhindert unerwartete Änderungen für Bereich, wenn er kopiert oder nach unten gezogen wird.
Sortieren Sie die erste Spalte in aufsteigender Reihenfolge, wenn Sie die ungefähre Übereinstimmung anwenden, zum Beispiel mit ist_sortiert = TRUE.	Wenn Sie die ungefähre Übereinstimmung anwenden oder ist_sortiert = TRUE ist, sollten Sie die erste Spalte in aufsteigender Reihenfolge sortieren. Andernfalls ist der Rückgabewert sehr wahrscheinlich nicht korrekt. Weitere Informationen zum Sortieren von Daten
Daten vor der Verwendung von SVERWEIS bereinigen	Vor der Verwendung von SVERWEIS sollten Sie Ihre Daten bereinigen. Nicht bereinigte Daten können dazu führen, dass SVERWEIS einen unerwarteten Wert zurückgibt. Im Folgenden finden Sie einige gängige Formen nicht bereinigter Daten: Vorangehende Leerzeichen: „ Apfel“ Nachgestellte Leerzeichen: „Apfel “ Fehlende Zeichen oder Leerzeichen: „<kein Wert>“ und „<Leerzeichen>“ sind nicht identisch Mit der Funktion Daten Datenbereinigung Leerzeichen entfernen können Sie vorangehende und nachgestellte Leerzeichen entfernen.
Zahlen- oder Datumswerte nicht als Text speichern	Achten Sie darauf, dass Datums- oder Zahlenwerte in der ersten Spalte des SVERWEIS-Bereichs, etwa in der Suchschlüsselspalte, nicht als Textwerte gespeichert sind. Es besteht sonst die Gefahr, dass der Rückgabewert nicht korrekt ist. Wählen Sie oben in Google Tabellen die Suchschlüsselspalte aus. Tippen Sie auf das Menü Format Zahl. Wählen Sie je nach Datentyp eine Option aus: Datum Zahl