Excel SVERWEIS umfassend erklärt

Excel Insights: Akten zum durchsuchen mit dem Excel SVERWEIS

Mit dem Excel SVERWEIS lassen sich Daten aus einer anderen Liste oder Tabelle suchen und übernehmen. Wenn Daten in irgendeiner Form erfasst, verarbeitet und/oder ausgewertet werden, haben wir immer das gleiche Problem. Die Daten sind in der Regel nie vollständig.

Das kann ganz verschiedene Gründe haben. Manchmal kommen die Daten einfach aus unterschiedlichen Quellen, weil unterschiedliche Software verwendet wird. Oft werden Daten aber auch absichtlich so strukturiert, dass nicht alle Mitarbeiter gleichermaßen Zugang haben. So arbeitet die Buchhaltung bei der Verbuchung der Gehälter z.B. nur mit der Personalnummer während die Personalabteilung jeder Personalnummer natürlich eine konkrete Person mit Namen etc. zuordnen kann. Nicht jeder soll wissen, was alle anderen verdienen.

Mit Hilfe der Funktion SVERWEIS können wir die Daten aus unterschiedlichen Quellen zusammenführen. Somit entsteht dann – um bei dem Beispiel zu bleiben – ein aussagefähigeres Gesamtbild aus Name und Gehalt.

Übersicht

  1. Excel SVERWEIS: Syntax und Funktionsweise
    1. Der Standard: SVERWEIS mit genauer Übereinstimmung
    2. Excel SVERWEIS mit ungenauer Übereinstimmung
    3. Werte in einer Range finden und Daten kategorisieren
  2. SVERWEIS nach links ausführen
  3. Excel SVERWEIS mit zwei Bedingungen
    1. SVERWEIS 2 Kriterien per Hilfsspalte verknüpfen
    2. Die Funktion WAHL: SVERWEIS mit 2 Bedingungen
  4. Alternativen zum SVERWEIS
    1. Ein Suchkriterium in einer Zeile suchen
    2. Suchkriterien in Spalte und in Zeile suchen
  5. Problembehandlung und Fehler
  6. Ressourcen

1. Excel SVERWEIS: Syntax und Funktionsweise

Meistens strukturieren wir Daten zeilenweise in Listen und Tabellen. Die erste Zeile enthält dabei in der Regel Spaltenbeschriftungen, dann folgt in jeder weiteren Zeile ein Datensatz mit den zugehörigen Werten in den Spalten. Um bei dieser Struktur einen konkreten Wert zurückzugeben, müssen wir zunächst anhand von Suchkriterien den richtigen Datensatz identifizieren, indem wir die Datensätze zeilenweise – also senkrecht – durchsuchen. Für diesen Datensatz wird dann der gewünschte Wert aus der entsprechenden Spalte ausgelesen. Genau das macht die Funktion SVERWEIS: einen senkrechten Verweis.

Die Syntax der Funktion SVERWEIS gestaltet sich wie folgt:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])
  • Suchkriterium
    Das Suchkriterium ist der Wert nach dem du die erste Spalte der Matrix durchsuchen willst. Der Wert kann eine Zahl, ein Text, ein Datum oder auch ein Zellbezug sein.
  • Matrix
    Die Matrix ist der Zellbereich, indem die relevanten Daten enthalten sind. Die Matrix muss links bei der nach dem Suchkriterium zu durchsuchenden Spalte beginnen. Nach rechts kann die Matrix beliebig viele Spalten umfassen, nur muss sichergestellt werden, dass sie auch die Spalte mit dem gesuchten Rückgabewert umfasst.
  • Spaltenindex
    Der Spaltenindex wird als ganze Zahl angegeben und beschreibt die Nummer der Spalte mit dem Rückgabewert, ausgehend von der Spalte mit den zu durchsuchenden Werten als erste Spalte.
  • [Bereich_Verweis]
    Als [Bereich_Verweis] kannst du den logischen Wert FALSCH bzw. 0 oder WAHR bzw. 1 angeben. Falsch bedeutet für diese Funktion, dass nur bei genauer Übereinstimmung (1:1) des Suchkriteriums mit einem Wert aus der zu durchsuchenden Spalte ein Rückgabewert ausgegeben wird, während WAHR auch bei ungefährer Übereinstimmung einen Rückgabewert liefert. Wenn du den Parameter nicht setzt, setzt Excel standardmäßig WAHR, was zu einer ungenauen Suche und damit in der Regel zu einem ungewünschten Ergebnis führt. Gewöhne dir also an, standardmäßig einfach 0 zu setzen.

1.1 Der Standard: SVERWEIS mit genauer Übereinstimmung

Excel Insights: Screenshot mit Tabelle und Erklärung zu Excel SVERWEIS Beispiel
Excel SVERWEIS Beispiel

Die verwendete Formel lautet:

=SVERWEIS($A$10;$A$2:$C$8;2;0)

Mit dieser Formel suchst du das Suchkriterium aus der Zelle A10 (hier die Zahl 103) in der ersten Spalte der Matrix (hier die Spalte A). Über den Spaltenindex 2 legst du fest, dass du den zugehörigen Wert aus der zweiten Spalte der Matrix (hier die Spalte B) als Rückgabewert erhälst. Mit dem logischen Wert 0 definierst du, dass du nur bei einer genauen Übereinstimmung (hier also der ganzen Zahl 103) einen Rückgabewert erhälst.

Mit dem Spaltenindex 2 kannst du also den Namen und mit dem Spaltenindex 3 den Vornamen zum Suchkriterium auslesen.

Tipp: Gewöhne dir am besten direkt an, die Matrix immer als absoluten Zellbezug mit dem Dollar-Zeichen $ zu erfassen. Dadurch kannst du die Formel einfach für alle Zeilen nach unten kopieren, ohne die Matrix ebenfalls nach unten zu verschieben.


1.2 Excel SVERWEIS mit ungenauer Übereinstimmung

coming soon


1.3 Werte in einer Range finden und Daten kategorisieren

Eine weitere großteils unbekannte Anwendungsmöglichkeit für den SVERWEIS möchte ich dir hier auch noch zeigen. Du kannst ein Suchkriterium über zwei Spalten suchen. Die Werte in den beiden zu durchsuchenden Spalten bilden quasi eine Art Range bzw. Staffelung für die Werte. So kannst du sehr schnell verschiedene Daten kategorisieren.

Excel Insights: Excel SVERWEIS mit eine Staffelung nach Umsatz
Excel SVERWEIS mit einer Range zum kategorisieren

Die verwendete Formel lautet:

=SVERWEIS(E3;A3:C6;3;1)

Das Suchkriterium aus der Zelle E3 wird hier automatisch ohne irgendwelche weiteren Angaben in den beiden zu durchsuchenden Spalten A und B gesucht. Da du ja in der jeweiligen Range alle Werte finden möchtest, musst du als logischen Wert die 1 für eine ungenaue Übereinstimmung setzen. Der Spaltenindex ist hier 3 und bezeichnet damit die Spalte C, die erste Spalte nach den beiden zu durchsuchenden Spalten.

Wichtig: Die Staffelung muss so aufgebaut werden, dass oben links die kleinste Zahl und unten rechts die größte Zahl steht und die Staffelungen somit aufeinander aufbauen.


Der SVERWEIS alleine kann aufgrund seiner Syntax nur Werte aus Spalten auslesen, die rechts von der zu durchsuchenden Spalte liegen. Wenn du aber keinen Einfluss auf die Reihenfolge der Spalten in der Datenquelle hast oder wenn die Reihenfolge aus einem anderen Grund genauso gewünscht ist, kommst du mit dem SVERWEIS alleine nicht weiter. Natürlich könntest du das Problem auch einfach mit der Kombination der Funktionen INDEX und VERGLEICH lösen, aber hier geht es ja um den SVERWEIS.

Hier möchte ich dir am Beispiel von oben zeigen, wie du durch einen Trick mit Hilfe der Funktion WAHL die eigentliche Syntax umgehen und somit doch den SVERWEIS nach links ausführen kannst. Es handelt sich hierbei nur um eine Abwandlung der Suche mit zwei Bedingungen.

Wir wollen nun über die Suche nach dem (Nach)Namen die Personalnummer ausgeben lassen. Das Suchkriterium wird wieder in der Zelle A10 eingetragen.

Excel Insights: Screenshot mit Tabelle und Erklärung zu Excel SVERWEIS nach links suchen lassen
Durch einen Trick mit der Funktion WAHL den SVERWEIS nach links suchen lassen

Die verwendete Formel lautet:

=SVERWEIS(A10;WAHL({1.2};B2:B8;A2:A8);2;0)

Im Vergleich zu den anderen Beispielen ist die Syntax identisch, nur wurde die Matrix nicht wie gewöhnlich einfach als Zellbereich angegeben, sondern durch den folgenden Ausdruck ersetzt:

 WAHL({1.2};B2:B8;A2:A8)

Die Funktion Wahl erzeugt so angewendet eine Tabelle mit zwei Spalten, deren Werte aus unseren beiden Zellbereichen bestehen. Allgemeine Informationen zur Funktion WAHL findest du in der Office Hilfe. Über den Ausdruck {1.2} wird zunächst eine Matrix mit zwei Spalten erzeugt und dann der Zellbereich B2:B8 als erste Spalte und der Zellbereich A2:A8 als zweite Spalte definiert. Eine andere Möglichkeit zur Erzeugung der Matrix wäre die Schreibweise:

 WAHL({2.1};A2:A8;B2:B8)  

Der Ausdruck {2.1} erzeugt zunächst wieder eine Matrix mit zwei Spalten und definiert dann Zellbereich A2:A8 als zweite Spalte und den Zellbereich B2:B8 als erste Spalte. Im Ergebnis handelt es sich um die gleiche Matrix.

In dieser Matrix ist die ersten Spalte nun also der zu durchsuchende Zellbereich B2:B8, die Spalte mit den Namen, während die zweite Spalte der Zellbereich A2:A8 ist, die Spalte Personalnummer mit den Rückgabewerte.

Damit liegt die Spalte mit den Rückgabewerten wie von der Syntax des SVERWEIS gefordert technisch gesehen wieder rechts von der zu durchsuchenden Spalte. Über die Angabe des Spaltenindex 2 wird die zweite Spalte der durch WAHL erzeugten Matrix ausgelesen.

Allgemeingültig formuliert, kannst du also also wie folgt mit der Funktion Excel SVERWEIS nach links suchen:

 =SVERWEIS(Suchkriterium;WAHL({1.2};zu durchsuchende Spalte;Spalte mit Rückgabewerten;Spaltenindex;[Bereich_Verweis]) 

Abschließend möchte ich dazu noch sagen, dass ich diese Anwendung des Excel SVERWEIS zugegebenermaßen lange nicht mehr gesehen habe. Ich habe sie zwar noch einmal spaßeshalber zur Verwirrung der Wirtschaftsprüfer benutzt, verwende für die Suche von Rückgabewerten, die in einer Spalte links von der zu durchsuchenden Spalte stehen, aber eigentlich ausschließlich die Kombination aus INDEX und VERGLEICH.

Dennoch habe ich mich entschieden, diesen Anwendungfall hier aufzunehmen, da er m.E. sehr beim Verständnis des nun folgenden Anwendungsfalls des SVERWEIS mit zwei Bedingungen unter zur Hilfenahme der Funktion WAHL hilft.


3. SVERWEIS mit zwei oder mehreren Bedingungen

Nicht selten ist ein Suchkriterium zur Identifikation des richtigen Datensatzes aber gar nicht ausreichend.

So gibt es in dem Eingangsbeispiel den Namen „Möller“ zweimal. Der SVERWEIS würde immer einen Rückgabewert aus dem Datensatz von Tim Möller mit der Personalnummer 100 liefern, da es von oben nach unten betrachtet der erste Datensatz ist, indem das Suchkriterium „Möller“ mit einem Wert aus der zu durchsuchenden Spalte Name übereinstimmt. Eine eindeutige Identifikation des Datensatzes nur nach dem Namen als Suchkriterium ist also nicht möglich.

In diesem Fall muss in zwei, drei oder auch mehr Spalten gesucht werden um den richtigen Datensatz zu filtern. Die Syntax des Excel SVERWEIS sieht das nicht vor, man muss sich also zu helfen wissen.


3.1 SVERWEIS 2 Kriterien per Hilfsspalte verknüpfen

Die erste Möglichkeit, wie du den SVERWEIS mit zwei Bedingungen anwenden kannst, ist mit Hilfe einer Hilfsspalte. In dieser Hilfsspalte (hier Spalte D) verknüpfst du für jeden Datensatz (Zeile) aus allen zu durchsuchenden Spalten die Werte zu einer einzelnen neuen zu durchsuchenden Spalte.

In den der Zelle D2 kannst du die Werte aus der Spalte Name und Vorname entweder mit der Funktion VERKETTEN oder mit dem Und-Zeichen & verknüpfen:

=B2&C2
=VERKETTEN(B2;C2)

Somit erhälst du eine neue Spalte, deren Werte jetzt zwei Informationen enthalten und kannst so mit dem SVERWEIS mit zwei Suchkriterien arbeiten. Dabei ist es egal, ob du die Werte einfach aneinander reihst, Komma und Leerzeichen einbaust oder wie auch immer. Wichtig ist nur, dass du das Suchkriterium identisch zur zu durchsuchenden Spalte aufbaust.

So wird in der Zelle A12 aus den zwei Bedingungen Name in Zelle A10 und Vorname in Zelle B10 das neue Suchkriterium auf die gleiche Art und Weise zusammengebaut:

=A10&B10
=VERKETTEN(A10;B10)

So hast du aus zwei Suchkriterien und zwei zu durchsuchenden Spalten ein Suchkriterium und eine zu durchsuchende Spalte gemacht. Die Syntax des SVERWEIS funktioniert also wieder. Die verwendete Formel lautet:

=SVERWEIS(A12;D2:F8;2;0)

Diese Vorgehensweise funktioniert natürlich auch beim SVERWEIS mit mehr als 2 Spalten bzw. Kriterien.


3.2 Die Funktion WAHL: SVERWEIS mit 2 Bedingungen

Die andere Möglichkeit mit dem SVERWEIS über mehrere Spalten zu suchen ist die Verwendung der Funktion WAHL. Ich habe sie vor vielen Jahren bei meinem ersten Arbeitgeber gelernt. Der SVERWEIS nach links ist eine Abwandlung hiervon.

SVERWEIS 2 Kriterien mit der WAHL Funktion suchen: Spaltenindex 2

Die verwendete Formel lautet:

=SVERWEIS(A12;WAHL({1.2};B2:B8&C2:C8;D2:D8);2;0)

WICHTIG: Es handelt sich um eine sogenannte Matrixformel. Anstatt wie bei einer normalen Formel bzw. Funktion einfach die Eingabetaste zu drücken, beendest du die Eingabe einer Matrixformel mit der Tastenkombination Strg + Shift + Eingabe. Gib die Formel so ein, wie sie hier oben steht, also ohne die beiden äußeren geschweiften Klammern { }. Sie werden von Excel
automatisch erzeugt wenn du Tastenkombination benutzt.

Ansonsten handelt es sich um die normale Syntax für den SVERWEIS. Der Parameter Matrix wird hier wie im obigen Beispiel SVERWEIS nach links wieder durch die Funktion WAHL ersetzt.

=WAHL({1.2};B2:B8&C2:C8;D2:D8)

Über den Ausdruck {1.2} erzeugt WAHL zunächst eine Matrix mit zwei Spalten. Die erste Spalte wird aus den beiden mit & verknüpften Zellbereichen B2:B8 und C2:C8 gebildet. Diese neu definierte Spalte entspricht also exakt der Hilfsspalte aus dem letzten Beispiel. Die zweite Spalte ist der Zellbereich D2:D8 mit den Rückgabewerten.

Wenn in der Hilfsspalte aus den mit & verknüpften Zellbereichen das Suchkriterium gefunden wird, führt der Spaltenindex 2 dazu, dass der Rückgabewert aus der zweiten Spalte ausgegeben wird.

Natürlich kann man sich auch direkt eine größere Matrix bauen, um z.B. auch das Land zurückgeben zu können.


SVERWEIS 2 Kriterien mit der WAHL Funktion suchen: Spaltenindex 2 und 3

Die Formel dazu lautet:

=SVERWEIS(A10&B10;WAHL({1.2.3};B2:B8&C2:C8;D2:D8;E2:E8);3;0)

Mit Hilfe der Funktion Wahl werden über den Ausdruck {1.2.3} also drei Spalten erzeugt.

  • zu durchsuchende Hilfsspalte
    B2:B8&C2:C8
  • Spalte mit Rückgabewert 1
    D2:D8
    Spaltenindex 2
  • Spalte mit Rückgabewert 2
    E2:E8
    Spaltenindex 3

Wahlweise kann über den Spaltenindex jetzt die zweite oder auch die dritte Spalte als Spalte für den Rückgabewert bestimmt werden.


4. Alternativen zum SVERWEIS

Obwohl der Excel SVERWEIS im Arbeitsalltag eine nicht wegzudenkende und auch eine der am meisten genutzte Funktion in Excel ist, gibt es auch ein paar Fälle, bei denen du mit dem SVERWEIS zum Vervollständigen von Listen und Tabellen nicht weiter kommen wirst. Hier möchte ich dir zwei Fälle zeigen, bei denen der SVERWEIS an seine Grenzen kommt.


4.1 Ein Suchkriterium in einer Zeile suchen

Wenn die Quelle, aus der du Informationen übernehmen möchtest, die Informationen nicht zeilen- sondern spaltenweise organisiert, bringt das Durchsuchen einer Spalte mit dem senkrechten Verweis kein Ergebnis. In diesem Fall musst du in eine Zeile horizontal nach dem Suchkriterium durchsuchen und dann den Rückgabewert aus der zugehörigen Spalte auslesen. Für den waagerechten Verweis bietet Excel die Funktion WVERWEIS. Sie funktioniert analog zu dem hier beschriebenen SVERWEIS.


4.2 Suchkriterien in Spalte und in Zeile suchen

Wenn du sowohl eine Zeile als auch eine Spalte nach jeweils einem Suchkriterium durchsuchen musst, um den Rückgabewert zu ermitteln, bietet sich die Kombination der beiden Funktionen INDEX und VERGLEICH an die auch noch weit flexibler ist.


5. Problembehandlung und Fehler

Wenn der Rückgabewert des Excel SVERWEIS einfach anders ist, als du erwartest, ist die Wahrscheinlichkeit hoch, dass du einen der typischen Fehler gemacht hast. Stell dir die folgenden Fragen:

  • Gibt es das Suchkriterium überhaupt in der zu durchsuchenden Spalte? #NV Fehler
    Wenn es das Suchkriterium in der zu durchsuchenden Spalte gar nicht gibt, wirst du immer den #NV Fehler als Rückgabewert erhalten.
  • Stimmt das Format des Suchkriteriums mit dem Format der zu durchsuchenden Spalte überein?
    Du kannst nicht Äpfel mit Birnen vergleichen.
  • Ist dein Suchkriterium ein Text, den du nicht als Zellbezug sondern direkt in der Formel eingegeben hast?
    Dann denk daran, den Text in Anführungszeichen „Suchkriterium_Text“ zu setzen wenn du ihn direkt in der Formel eingibst.
  • Hast du die SVERWEIS Funktion für mehrere Zeilen nach unten kopiert?
    Wenn du die Matrix nicht als absoluten Zellbezug mit den Dollar-Zeichen $ erfasst hast, hat sich die Matrix als relativer Zellbezug durch das Kopieren der Funktion auch nach unten verschoben. Die Zeile, die das Suchkriterium enthält, liegt dann vielleicht nicht mehr in der Matrix.
  • Ist die Matrix auf einem anderen Tabellenblatt oder in einer anderen Arbeitsmappe?
    Gerade hier schleichen sich schnell Fehler ein. Überprüfe die korrekte Schreibweise der Zellbezüge.
  • Hast du den Spaltenindex und damit die Spalte für den Rückgabewert richtig gesetzt?
    Zähle die Spalten noch einmal von links ausgehend von der ersten, der zu durchsuchenden Spalte durch.
  • Liegt die Spalte auf die der Spaltenindex verweist innerhalb der Matrix? #BEZUG! Fehler
    Die Spalte mit den Rückgabewerten muss in der Matrix liegen. Erweiter die Matrix ggfs. entsprechend.
  • Hast du FLASCH oder WAHR bzw. 0 oder 1 richtig gesetzt?
    Überprüfe das logische Argument.

6. Ressourcen

Hier findest du noch ein paar Artikel mit weiterführenden Informationen, die Beispieldatei zum Download und externe Links:

1 Kommentar zu „Excel SVERWEIS umfassend erklärt“

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.


Die im Rahmen der Kommentarfunktion übermittelten Daten werden gespeichert. Weitere Informationen zur Datenverarbeitung findest du in der Datenschutzerklärung.