SVERWEIS funktioniert nicht: 9 häufige Ursachen und ihre Lösung

Die Formel steht, die Schreibweise sieht richtig aus, und trotzdem erscheint #NV in der Zelle. Oder schlimmer: Es erscheint ein Wert, der falsch ist, ohne dass Excel etwas meldet. Beides hat eine überschaubare Zahl von Ursachen.

Dieser Beitrag geht sie in der Reihenfolge durch, in der sie in der Praxis vorkommen. Wie die Funktion grundsätzlich aufgebaut ist, lesen Sie im Beitrag über die SVERWEIS-Funktion.

Zuerst: Was zeigt die Zelle an?

Die Fehlermeldung grenzt die Ursache bereits ein. Diese Übersicht führt Sie zum passenden Abschnitt:

Ursache 1: Zahl auf der einen Seite, Text auf der anderen

Das ist der häufigste Fall. Beide Werte sehen gleich aus, für Excel sind sie es nicht. Die Zahl 10024 und der Text „10024″ sind zwei verschiedene Dinge, und SVERWEIS findet damit nichts.

Zwei Merkmale verraten den Fall. Zahlen stehen in Excel standardmäßig rechtsbündig, Text steht links. Und in der linken oberen Ecke der Zelle sitzt ein grünes Dreieck.

Prüfung: Schreiben Sie in eine freie Zelle =ISTZAHL(A4) und =ISTZAHL(E4). Steht einmal WAHR und einmal FALSCH, ist die Ursache gefunden. Die Ausrichtung allein ist kein Beweis, denn sie lässt sich von Hand ändern.

Lösung in der Formel: Wandeln Sie das Suchkriterium um.

=SVERWEIS(WERT(E4);$A$4:$C$9;3;FALSCH)

Liegt der Fall umgekehrt, steht also eine Zahl vor und die Liste enthält Text, hängen Sie mit E4&"" eine leere Zeichenfolge an das Suchkriterium.

Dauerhafte Lösung: Markieren Sie die betroffene Spalte, klicken Sie auf das gelbe Warnsymbol und wählen Sie In Zahl umwandeln. Danach braucht die Formel keine Umwandlung mehr.

Ursache 2: Ein Leerzeichen, das niemand sieht

In dieser Bestellliste schlägt genau eine Zeile fehl. Die Artikelnummer BB-1001 steht in der Preisliste, trotzdem meldet Zeile 5 einen Fehler.

In der Zelle steht BB-1001 mit einem Leerzeichen am Ende. Solche Reste entstehen beim Kopieren aus anderen Programmen, aus E-Mails oder bei Exporten aus einer Warenwirtschaft.

Prüfung: Zählen Sie die Zeichen. Schreiben Sie in eine freie Spalte =LÄNGE(C4) und ziehen Sie die Formel nach unten. Eine Artikelnummer hat hier sieben Zeichen, die fehlerhafte Zeile zeigt acht.

Lösung in der Formel: GLÄTTEN entfernt Leerzeichen am Anfang und am Ende sowie doppelte Leerzeichen zwischen Wörtern.

=SVERWEIS(GLÄTTEN($C4);Preisliste!$A$4:$D$13;2;FALSCH)

Steht das Leerzeichen in der Nachschlagetabelle statt im Suchbegriff, hilft GLÄTTEN in der Formel nicht weiter. Bereinigen Sie dann die Liste selbst, zum Beispiel über Start → Suchen und Auswählen → Ersetzen. Wie das geht, steht unter Suchen und Ersetzen in Excel.

Ein Sonderfall sind geschützte Leerzeichen aus Webseiten oder Word. GLÄTTEN erfasst sie nicht. Hier hilft =WECHSELN(C4;ZEICHEN(160);"").

Ursache 3: Der Suchbegriff steht nicht in der ersten Spalte

SVERWEIS sucht ausschließlich in der ersten Spalte der Matrix. Beginnt der Bereich eine Spalte zu weit rechts, sucht Excel in den Bezeichnungen statt in den Artikelnummern und findet nichts.

=SVERWEIS($C4;Preisliste!$B$4:$D$13;2;FALSCH)

Typisch für diesen Fehler ist, dass nicht einzelne, sondern nahezu alle Zeilen #NV melden. Einzelne Treffer sind trotzdem möglich, wenn ein Suchbegriff zufällig auch in der verschobenen Spalte vorkommt.

Lösung: Ziehen Sie die Matrix nach links, bis sie bei der Suchspalte beginnt, hier also Preisliste!$A$4:$D$13. Liegt die gesuchte Angabe links vom Suchbegriff, hilft SVERWEIS nicht weiter. Dann brauchen Sie INDEX mit VERGLEICH oder den XVERWEIS.

Ursache 4: Der Spaltenindex passt nicht zur Matrix

Die Matrix ist vier Spalten breit, die Formel verlangt Spalte 5. Excel meldet #BEZUG!, weil es diese Spalte nicht gibt.

=SVERWEIS($C4;Preisliste!$A$4:$D$13;5;FALSCH)

Gefährlicher als ein zu großer Index ist ein gültiger, aber falscher. Steht dort 3 statt 4, liefert Excel die Kategorie statt des Preises, und zwar ohne Fehlermeldung. Zählen Sie deshalb im Zweifel nach, und zwar innerhalb der Matrix: Die erste Spalte der Matrix ist Spalte 1, egal welcher Buchstabe darüber steht.

Ein Index kleiner als 1 führt zu #WERT!. Diese Meldung erscheint auch, wenn im dritten Argument versehentlich Text steht.

Eine zweite Falle: Fügen Sie innerhalb der Matrix eine Spalte ein, bleibt der Index in der Formel unverändert. Das Ergebnis ist ab sofort falsch, ohne dass Excel warnt. Prüfen Sie Ihre SVERWEIS-Formeln nach jeder Änderung an der Tabellenstruktur.

Ursache 5: Das vierte Argument fehlt

Dieser Fehler erzeugt keine Fehlermeldung und bleibt deshalb oft unentdeckt. In der Bestellliste steht in Zeile 6 die Artikelnummer BB-3005. Diesen Artikel gibt es in der Preisliste nicht.

=SVERWEIS($C4;Preisliste!$A$4:$D$13;2)

Trotzdem steht dort ein Ergebnis: Hängemappen für 18,50 Euro. Fehlt das vierte Argument, rechnet Excel mit WAHR und sucht den nächstkleineren Wert. In dieser aufsteigend sortierten Preisliste ist das BB-3003. Ist die Liste nicht sortiert, kann bei WAHR ein beliebiger Wert herauskommen.

In der Rechnung stünde damit eine Position, die so nicht bestellt wurde.

Lösung: Schreiben Sie FALSCH als viertes Argument, wann immer Sie einen genauen Treffer brauchen.

=SVERWEIS($C4;Preisliste!$A$4:$D$13;2;FALSCH)

Jetzt zeigt Zeile 6 #NV. Die fehlende Artikelnummer ist damit sichtbar und lässt sich klären. Prüfen Sie bestehende Dateien gezielt auf Formeln ohne viertes Argument.

Ursache 6: Der Suchbereich verrutscht beim Kopieren

Typisch für diesen Fehler ist, dass die oberen Zeilen stimmen und die unteren #NV melden.

Klicken Sie eine fehlerhafte Zelle an und schauen Sie in die Bearbeitungsleiste. Steht dort der erste Bereich statt des zweiten, ist er mitgewandert:

=SVERWEIS($C9;Preisliste!A9:D18;4;FALSCH)   falsch, der Bereich wandert mit
=SVERWEIS($C9;Preisliste!$A$4:$D$13;4;FALSCH)   richtig, der Bereich steht fest

Die Dollarzeichen halten den Bereich an seiner Stelle.

Lösung: Markieren Sie in der Bearbeitungsleiste den Bereich und drücken Sie F4. Excel setzt die Dollarzeichen. Kopieren Sie die Formel anschließend erneut nach unten.

Ursache 7: Die Matrix reicht nicht weit genug

Die Preisliste wächst. Ein neuer Artikel kommt in Zeile 14 dazu, die Formel sucht aber weiterhin nur bis Zeile 13.

In der Bestellliste meldet die betroffene Zeile #NV, obwohl der Artikel in der Preisliste steht.

=SVERWEIS($C7;Preisliste!$A$4:$D$13;2;FALSCH)

Lösung: Wandeln Sie die Nachschlagetabelle in eine formatierte Tabelle um. Markieren Sie eine Zelle darin und drücken Sie Strg + T. Fügen Sie danach unten eine Zeile an, wächst die Tabelle mit.

Verlassen Sie sich dabei nicht auf den festen Bereich in der Formel. Vergeben Sie stattdessen unter Tabellenentwurf einen Namen, etwa Preise, und verweisen Sie in der Formel darauf:

=SVERWEIS($C7;Preise;2;FALSCH)

Der Name steht für den Datenbereich der Tabelle ohne Kopfzeile. Er wächst mit jeder neuen Zeile mit, unabhängig davon, wie die Formel entstanden ist.

Wenn Sie bei einem normalen Bereich bleiben möchten, geben Sie ganze Spalten an: Preisliste!$A:$D. Der Spaltenindex bleibt dabei gleich. Das kostet bei sehr großen Dateien etwas Rechenzeit, ist bei Listen dieser Größe aber unkritisch.

Ursache 8: Excel zeigt die Formel statt des Ergebnisses

In der Zelle steht der Formeltext, nicht das Ergebnis.

Dafür gibt es drei Gründe. Der häufigste: Die Zelle war vorher als Text formatiert, dann behandelt Excel auch die Formel als Text und rechnet sie nicht. Setzen Sie das Format über Start → Zahl auf Standard, klicken Sie danach in die Zelle, drücken Sie F2 und dann Enter. Erst dieser Schritt bringt Excel dazu, den Inhalt neu zu lesen.

Der zweite Grund ist ein Apostroph am Anfang der Eingabe, also '=SVERWEIS(.... Es macht den Inhalt zu Text und wird in der Zelle nicht angezeigt, nur in der Bearbeitungsleiste. Ein Wechsel des Zahlenformats hilft hier nicht, Sie müssen das Zeichen löschen.

Der dritte Grund betrifft das ganze Blatt: Auf der Registerkarte Formeln ist die Schaltfläche Formeln anzeigen aktiv. Dann zeigen alle Zellen des Blattes ihre Formel statt des Ergebnisses. Gerechnet wird dabei weiterhin, es ist nur eine andere Ansicht. Schalten Sie die Schaltfläche wieder aus.

Ursache 9: Der Suchbegriff kommt doppelt vor

Diese Ursache erzeugt weder eine Fehlermeldung noch eine Auffälligkeit. In der Preisliste steht BB-3001 zweimal, einmal regulär und einmal als Restposten zu einem anderen Preis.

Bei der genauen Suche mit FALSCH gibt SVERWEIS den ersten Treffer von oben aus. In der Bestellliste erscheint deshalb 2,89 Euro, der Restposten für 1,99 Euro bleibt unberücksichtigt.

Prüfung: Zählen Sie, wie oft der Suchbegriff vorkommt. =ZÄHLENWENN(Preisliste!$A$4:$A$14;C4) liefert bei einem sauberen Datenbestand überall die 1. Wie die Funktion arbeitet, steht unter ZÄHLENWENN.

Lösung: Machen Sie die doppelten Einträge zuerst sichtbar. Markieren Sie die Spalte mit den Artikelnummern und wählen Sie Start → Bedingte Formatierung → Regeln zum Hervorheben von Zellen → Doppelte Werte. Excel färbt alle Nummern ein, die mehrfach vorkommen.

Erst danach entscheiden Sie, was mit den Treffern geschieht. Daten → Duplikate entfernen löscht Zeilen und sollte hier nicht der erste Griff sein: Die beiden Zeilen zu BB-3001 haben verschiedene Preise und sind damit keine identischen Duplikate. Sollen beide erhalten bleiben, brauchen Sie ein zweites Merkmal als Suchkriterium, etwa den Zustand des Artikels.

Was noch dahinterstecken kann

• Verbundene Zellen in der Suchspalte. Der Wert gehört technisch zur oberen linken Zelle, die übrigen sind leer.
• Unterschiedliche Schreibweise, etwa BB 1001 gegen BB-1001. Groß- und Kleinschreibung spielt dagegen keine Rolle, SVERWEIS unterscheidet sie nicht.
• Das Quellblatt wurde gelöscht. Dann steht in der Formel #BEZUG! an der Stelle des Blattnamens. Ein bloßes Umbenennen schadet dagegen nicht, Excel schreibt den neuen Namen selbst in alle Formeln.
• Die Berechnung steht auf manuell. Prüfen Sie das unter Formeln → Berechnungsoptionen. Mit F9 rechnet Excel sofort neu.
• Datumswerte, von denen einer eine Zahl und der andere Text ist. Das ist Ursache 1 in einer anderen Verkleidung.

Prüfliste für den Ernstfall

Wenn Sie nicht weiterkommen, gehen Sie diese fünf Schritte der Reihe nach durch:

1. Suchen Sie den Wert mit Strg + F in der Nachschlagetabelle. Findet Excel ihn nicht, liegt es an den Daten, nicht an der Formel.
2. Vergleichen Sie beide Zellen mit =A1=B1. Kommt FALSCH heraus, obwohl beide gleich aussehen, ist es Ursache 1 oder 2.
3. Prüfen Sie die Länge beider Werte mit =LÄNGE().
4. Klicken Sie in die Formel und drücken Sie F9 für den markierten Teil. Excel zeigt das Zwischenergebnis dieses Arguments an. Mit Esc verlassen Sie die Ansicht, ohne etwas zu verändern.
5. Bauen Sie die Formel in einer leeren Zelle neu auf und markieren Sie die Matrix mit der Maus, statt sie zu tippen.

Übungsdatei zum Nachvollziehen

Die Tabellen dieses Beitrags stecken in derselben Arbeitsmappe wie im Grundlagenbeitrag. Auf dem Blatt Fehlersuche ist der Fall mit den Textzahlen vorbereitet.

SVERWEIS Übungsdatei herunterladen

Was in der Übungsdatei steckt

• Bestellungen und Preisliste für die Grundlagen
• Zahlungen für den Abgleich zweier Listen
• Kundenpreise für die Suche mit zwei Kriterien
• Rabatte für die Umsatzstaffel
• Fehlersuche mit dem Klassiker Zahl gegen Text

Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.

Manche Aufgaben lassen sich mit SVERWEIS gar nicht lösen, etwa die Suche nach links oder die Ausgabe mehrerer Treffer. Welche Funktion dann zuständig ist, steht im letzten Abschnitt des Grundlagenbeitrags zu SVERWEIS.

Weitere Anleitungen zu Verweisfunktionen

• SVERWEIS Schritt für Schritt: der Aufbau der Funktion mit vier Aufgaben aus dem Büroalltag.
• XVERWEIS: sucht auch nach links und meldet fehlende Treffer im Klartext.
• WENNFEHLER: wann Sie einen Fehler abfangen dürfen und wann er sichtbar bleiben sollte.
• SVERWEIS mit zwei Suchkriterien: wenn ein Suchbegriff nicht ausreicht, um eine Zeile eindeutig zu finden.