SVERWEIS funktioniert nicht?

SVERWEIS Probleme tauchen häufiger auf als einem lieb ist.

Wofür wird der SVERWEIS überhaupt genutzt?

Der SVERWEIS wird normalerweise dazu genutzt, Senkrecht – deshalb ja (S)VERWEIS –  aus einer größeren Datenmengen eine Übereinstimmung innerhalb einer bestimmten Spalte zu finden und passend zu dieser Übereinstimmung aus der selben Zeile aber einer anderen Spalte den Wert der dort steht wieder zu geben.

Hier ein Beispiel: Sie haben eine Tabelle in der Sie in Spalte A die Kundennamen haben und in Spalte B die Umsätze dieser Kunden. Mit Hilfe des SVERWEIS können Sie jetzt in einer Zelle einen Kundennamen eingeben und der SVERWEIS zeigt Ihnen den passenden Umsatz an. Das können Sie auch in einer Hilfsspalte nutzen um für Viele Kunden in einer Tabelle die passenden Umsätze anzeigen zu lassen. Es gibt noch sehr viele andere Einsatzzwecke, vor allem in Kombination mit anderen Formeln.

Wie ist der SVERWEIS aufgebaut und wie funktioniert er überhaupt.

Die Formel sieht wie folgt aus:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex;Bereich_Verweis)

Für das Suchkriterium kann ein Wert eingegeben werden oder es wird eine Zelle angegeben in die dann ein Werte eingetragen werden kann ohne das immer die Formel geändert werden muss. z.B.: E2 für die Zelle der Spalte D und der Zeile 2. wenn ein Text als Wert in der Formel fest eingegeben sein soll wäre das mit Anführungszeichen möglich z.B: “Heinz“

Die Matrix beschreibt den Bereich in dem die Formel die erste Spalte durchsuchen soll und in der gefundenen Zeile den Wert aus einer anderen Spalte weiter rechts zurück geben soll. Die Matrix wird wie folgt angegeben. B2:C8 So durchläuft die Formel die Spalte B von Zeile 2 bis Zeile 8 und sucht nach dem Suchbegriff. Wenn dieser gefunden wurde, durchläuft die Formel die Zeile nach rechts.

Der Spaltenindex wird als Zahl eingegeben und bestimmt aus welcher Spalte innerhalb der Matrix der Wert zurückgegeben werden soll. Da wir den Umsatz sehen möchten, tragen wir hier eine 2 ein. Wie Sie sehen bezieht sich die Zahl nur auf die Spalte. Innerhalb der Matrix und nicht vom Tabellenblatt.

Der Wert „Bereich_Verweis“ gibt an wie genau das Suchkriterium übereinstimmen muss.

Wenn wie in unserem Beispiel der Wert „FALSCH“ eingetragen wird, wird die 1. Spalte der Matrix von oben nach unten durchlaufen und bei der ersten 100% Übereinstimmung mit dem Suchkriterium die Zeile nach rechts durchlaufen um den gewünschten Wert aus der gewünschte. Spalte (Spaltenindex) zurückzugeben.

Wird der Wert „WAHR“ eingetragen oder nichts, wird nach einer 100% Übereinstimmung gesucht oder dem nächst kleineren Wert. Dazu muss zwingend die Spalte aufsteigend sortiert sein.

SVERWEIS liefert den falschen Wert

Wenn der SVERWEIS bereits einen Wert aus der Spalte liefert es sich aber um einen falschen Wert handelt, liegt es sehr häufig daran, dass das Suchkriterium mehrfach in der zu durchsuchenden Spalte steht. Der SVERWEIS nimmt dann die erste gefundene Übereinstimmung und aus dieser Zeile den Wert aus der gewünschten Spalte. In unserem Beispiel ist die Formel: =SVERWEIS(F6;B7:C16;2;FALSCH) hier nochmal mit eigenen Worten beschrieben:

=SVERWEIS(der Wert der verglichen werden soll ; der Bereich der durchsucht werden soll; Aus welcher Spalte (Zahl) des durchsuchten Bereichs soll der Wert zurückgegeben werden; Wie genau soll der Wert der verglichen wird übereinstimmen? Genau=FALSCH so genau wie möglich = WAHR)

21 - SVERWEIS funktioniert nicht - falscher Wert - Excelwerk_de

Eventuell ist in der Formel aber auch mit dem Wert „WAHR“ gearbeitet worden. Dann können oft falsche Ergebnisse auftauchen die auch schwer nach zu vollziehen sind, wie in unserem Beispiel hier. Die 2020 erscheint häufiger in der Spalte B, aber es wird nicht der Wert aus der ersten übereinstimmenden Zeile genommen sondern erst viel weiter unten. 

21 - SVERWEIS funktioniert nicht - falscher Wert 2 - Excelwerk_de

SVERWEIS zeigt als Ergebnis #NV

Der SVERWEIS zeigt kein Ergebnis, sondern nur #NV. Der SVERWEIS nutzt immer nur die erste Spalte aus dem angegebenen Bereich für den Vergleich. Oft hat man eine größere Tabelle mit vielen Spalten. Der Wert der gesucht und verglichen werden soll, steht z.B.: in Spalte C. In der Formel für den SVERWEIS gibt man aber die Gesamte Tabelle an wie in unserem Beispiel B3:D12 hier noch die komplette Formel: =SVERWEIS(G2;B3:D12;3;FALSCH) Jetzt versucht Excel in der Spalte D den Wert aus der Zelle G2 zu finden, der natürlich hier nicht vorkommt.

21 - SVERWEIS funktioniert nicht - Wert NV - Excelwerk_de

Sehr häufig werden Daten aus einen ERP System wie SAP exportiert oder kommen aus anderen Quellen und sind als Text formatiert. Obwohl die Formel richtig ist und eigentlich ein Ergebnis erscheinen müsste kommt nur ein #NV. Das liegt daran, das die Spalte die durchsucht wird nicht das selbe Format hat wie der Wert der gesucht werden soll.

21 - SVERWEIS funktioniert nicht - Wert NV2 - Excelwerk_de

Alternative zum SVERWEIS ist der INDEX+VERGLEICH

Leider hat der SVERWEIS viele mögliche Fehlerquellen und es ist aufwendig immer nach zu vollziehen woran es genau liegt. Weiterhin kann in der Formel ja nur nach Werten gesucht werden wenn die Spalte in der gesucht wird links von den Werten steht die man ausgegeben haben möchte. Eine viel flexiblere und weniger fehleranfällige Formel ist eine Kombination aus 2 Formeln.

Zum einen die Formel VERGLEICH() mit der der gewünschte Wert gesucht wird und man als Ergebnis die Zellennummer zurück bekommt in der der Wert steht. Das funktioniert auch in waagerechter Form. Zum andern die Formel INDEX() mit der man den Wert aus einer bestimmten Zelle zurück gibt. =INDEX(Matrix;Zeile;Spalte) für die Zeile oder die Spalte werden normalerweise Zahlen eingesetzt. Diese können ersetzt werden durch die Formel =VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp) In der Kombination kann das dann so aussehen:  =INDEX(Matrix;VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp);Spalte)
21 - SVERWEIS funktioniert nicht - Alternative - Excelwerk_de

Weiterführende Informationen zu diesem Thema

Kostenlose Beispieldatei zum download

Kommentar verfassen

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