#NV beim SVERWEIS: fünf Ursachen, eine Reihenfolge
#NV ist keine kaputte Formel, sondern eine erfolglose Suche, und mit vier kurzen Prüfungen weißt du in wenigen Minuten, an welcher der fünf üblichen Ursachen es liegt.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Formel ist meistens richtig, die Daten sind es nicht
Der Ausgangspunkt sieht fast immer gleich aus: zwei Listen aus zwei Systemen, verbunden über die Artikel- oder Kundennummer. Neunzig Prozent der Zeilen liefern ein Ergebnis, vierzig Zeilen zeigen #NV. Und beim Nachsehen steht der gesuchte Wert sichtbar in der Quelle, an genau der Stelle, an der er stehen soll. Genau dieser Widerspruch führt dazu, dass die Suche an der falschen Stelle beginnt, nämlich in der Formel.
Der übliche Reflex kostet mehr, als er spart. Wer die Meldung mit WENNFEHLER wegnimmt, bekommt eine Tabelle, in der fehlende Datensätze wie leere Felder aussehen und in jeder Summe schlicht fehlen. Wer die Werte stattdessen von Hand nachträgt, macht dieselbe Arbeit beim nächsten Export noch einmal, weil die Ursache in der Quelle sitzt und nicht in der Zelle.
Der gute Teil daran: Die Ursachen sind wenige, sie schließen einander weitgehend aus, und jede hat eine eigene kurze Prüfung. Wenn du diese Prüfungen in fester Reihenfolge durchgehst, brauchst du nicht zu raten und musst die Formel selbst in den meisten Fällen gar nicht anfassen.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
Der Wert steht sichtbar in der Quelle, ZÄHLENWENN findet ihn auch, der SVERWEIS liefert trotzdem #NV.
Ursache
ZÄHLENWENN wandelt beim Vergleich zwischen Zahl und Text stillschweigend um, SVERWEIS mit FALSCH vergleicht streng nach Datentyp. In einer der beiden Spalten stehen die Nummern als Text.
Lösung
Mit =ISTZAHL(A2) auf beiden Seiten prüfen und die Textspalte über Daten > Datentools > Text in Spalten neu einlesen, im Assistenten zweimal auf Weiter und dann auf Fertig stellen.
Symptom
Nur die Zeilen aus einem bestimmten Export laufen auf #NV, die von Hand erfassten stimmen alle.
Ursache
Am exportierten Wert hängt ein Leerzeichen oder das geschützte Leerzeichen mit dem Code 160, das typischerweise aus HTML-Quellen stammt.
Lösung
=LÄNGE(A2) zeigt die überzähligen Zeichen. Repariere die Quellspalte einmal mit =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);"")), statt jede einzelne Formel einzuwickeln.
Symptom
Oben in der Liste stimmt alles, ab einer bestimmten Zeile kommt nur noch #NV.
Ursache
Der Suchbereich wurde relativ angegeben und ist beim Kopieren nach unten mitgewandert, dadurch fehlt am unteren Ende ein Stück der Quelle.
Lösung
Den Bereich mit F4 auf $H$2:$K$50 festsetzen oder die Quelle mit Strg + T in eine Tabelle umwandeln und über den Tabellennamen verweisen, der beim Anfügen von Zeilen mitwächst.
Symptom
Mal kommt #NV, mal ein Ergebnis, das erkennbar nicht zum gesuchten Wert gehört.
Ursache
Das vierte Argument fehlt. Ohne Angabe sucht Excel näherungsweise und setzt dafür eine aufsteigend sortierte erste Spalte voraus, die im Alltag selten dauerhaft vorliegt.
Lösung
FALSCH oder 0 ans Ende der Formel setzen. Beim Nachschlagen einzelner Werte ist das der Normalfall und keine Feinheit für Fortgeschrittene.
Symptom
Die Formel zeigt #NV, obwohl die Zeile in der Quelle existiert und der Wert sauber aussieht.
Ursache
Der Suchbegriff steht nicht in der ersten Spalte der angegebenen Matrix, sondern weiter rechts. SVERWEIS sucht ausschließlich in der ersten Spalte.
Lösung
Die Matrix so wählen, dass die Suchspalte vorne liegt, oder auf INDEX mit VERGLEICH ausweichen: =INDEX(A:A;VERGLEICH(F2;C:C;0)) sucht in C und liefert aus A.
Vier Prüfungen in fester Reihenfolge
- 01 ZÄHLENWENN meldet 0: Der Wert fehlt in der Quelle oder ist anders geschrieben.
- 02 ZÄHLENWENN meldet einen Treffer: Dann liegt es am Format und nicht an den Daten.
- 03 LÄNGE zählt mehr Zeichen als sichtbar: An dem Wert hängt ein Leerzeichen.
- 04 ISTZAHL antwortet auf beiden Seiten verschieden: Die Nummer steht einmal als Text.
- 05 Alles sauber und trotzdem #NV: Dann ist der Suchbereich beim Kopieren verrutscht.
Was du nach dieser Seite in wenigen Minuten erledigst
Alle folgenden Prüfungen laufen in einer freien Spalte neben den Daten und lassen die eigentliche Formel unberührt. Du beantwortest damit der Reihe nach vier Fragen: Existiert der Wert überhaupt in der Quelle? Steht er dort genauso geschrieben? Hat er auf beiden Seiten denselben Datentyp? Und zeigt die Formel noch auf den Bereich, den sie treffen soll?
Die Gegenprobe mit ZÄHLENWENN
=ZÄHLENWENN(Artikel!A:A;A2) sagt dir, wie oft der Wert in der Suchspalte vorkommt. Eine 0 verweist auf die Datenquelle, ein Treffer bei gleichzeitigem #NV verweist auf das Format.
Zeichen zählen statt hinsehen
=LÄNGE(A2) macht angehängte Leerzeichen sichtbar, die auf dem Bildschirm nicht zu erkennen sind. Vier erwartete Zeichen und fünf gezählte sind die ganze Erklärung.
Zahl und Text unterscheiden
=ISTZAHL(A2) auf beiden Seiten beantwortet die Frage eindeutig, während die Ausrichtung und das grüne Dreieck in der Zellecke nur ein Hinweis sind. Die Reparatur läuft über Text in Spalten.
Den Suchbereich festnageln
Ein relativer Bereich wandert beim Kopieren nach unten und verliert am Ende Zeilen der Quelle. F4 setzt ihn absolut, Strg + T macht daraus eine Tabelle, die von selbst mitwächst.
WENNNV statt WENNFEHLER
WENNNV reagiert ausschließlich auf #NV und lässt #WERT! oder #BEZUG! stehen. Eine sprechende Ausgabe wie "Artikel unbekannt" sagt beim Prüfen mehr als ein leerer Text.
Die Quelle reparieren, nicht die Formel
Ein einziges =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);"")) in der Quellspalte ersetzt hundert Formeln, die jeweils in Reinigungsfunktionen gewickelt sind.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Prüf zuerst, ob der Wert überhaupt vorhanden ist
Bevor du an der Formel drehst, stell die Frage anders herum. =ZÄHLENWENN(Artikel!A:A;A2) sagt dir, wie oft der gesuchte Wert in der Suchspalte vorkommt. Kommt 0 zurück, ist die Formel unschuldig, dann fehlt der Datensatz in der Quelle oder er ist anders geschrieben, als du erwartest.
Kommt 1 oder mehr zurück und der SVERWEIS liefert trotzdem #NV, liegt es fast immer am Format. ZÄHLENWENN vergleicht großzügiger und wandelt zwischen Zahl und Text stillschweigend um, SVERWEIS mit FALSCH tut das nicht. Genau diese Asymmetrie ist der schnellste Hinweis darauf, dass in einer der beiden Spalten Zahlen als Text stehen.
Leerzeichen, die man nicht sieht
Exporte aus Warenwirtschaft, CRM oder Webformularen bringen gern ein Leerzeichen am Ende mit. Für SVERWEIS sind "4711 " und "4711" zwei verschiedene Dinge. =LÄNGE(A2) neben der Zelle zeigt das sofort: vier Zeichen erwartet, fünf gezählt.
GLÄTTEN entfernt führende, folgende und doppelte Leerzeichen. Was GLÄTTEN nicht erwischt, ist das geschützte Leerzeichen mit dem Code 160, das typischerweise aus HTML-Quellen stammt. Dagegen hilft WECHSELN, am besten in Kombination: =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);"")). Repariere damit einmal die Quellspalte, statt jede einzelne Formel in Reinigungsfunktionen zu wickeln.
Zahl oder Text, das ist hier die Frage
Der zweite Klassiker: In der einen Tabelle steht die Artikelnummer als Zahl, in der anderen als Text. Sichtbar wird das an der Ausrichtung, denn Zahlen stehen ohne Zutun rechts und Text links, oft auch am grünen Dreieck in der linken oberen Ecke der Zelle. Eindeutig beantwortet es =ISTZAHL(A2).
Die saubere Reparatur läuft über Daten > Datentools > Text in Spalten. Im Assistenten zweimal auf Weiter und dann auf Fertig stellen, das schreibt die markierte Spalte neu und wandelt echte Zahlen in Zahlen um. Umgekehrt gilt: Wenn die Nummern führende Nullen tragen und deshalb Text bleiben müssen, formatierst du beide Seiten als Text und passt die Gegenseite an, statt die Nullen wegzuwerfen.
Das fehlende FALSCH und der verrutschte Bereich
Ohne viertes Argument sucht SVERWEIS näherungsweise. Bei sortierten Zahlen liefert das stillschweigend falsche Treffer, bei unsortierten Daten auch mal #NV, obwohl der Wert vorhanden ist. Setz beim Nachschlagen auf exakte Übereinstimmung immer FALSCH oder 0 ans Ende. Das ist keine Feinheit für Fortgeschrittene, sondern der Normalfall.
Der zweite Teil betrifft den Suchbereich. =SVERWEIS(A2;H2:K50;3;FALSCH) wird beim Kopieren nach unten in der nächsten Zeile zu H3:K51, und am unteren Ende fehlt dann ein Stück der Quelle. Entweder du setzt den Bereich mit F4 auf $H$2:$K$50 fest, oder du wandelst die Quelle mit Strg + T in eine Tabelle um und verweist über den Tabellennamen, der beim Anfügen neuer Zeilen automatisch mitwächst.
WENNFEHLER erst ganz am Schluss
WENNFEHLER macht die Meldung weg, nicht das Problem. Wer =WENNFEHLER(SVERWEIS(...);"") schreibt, bevor er die Ursache kennt, baut eine Tabelle, in der fehlende Datensätze wie leere Felder aussehen und in Summen einfach fehlen. Der Tippfehler im Funktionsnamen sieht danach genauso harmlos aus wie eine echte Lücke in den Daten.
Wenn du bewusst nur den Fall "nicht gefunden" abfangen willst und alle anderen Fehler weiter sehen möchtest, nimm WENNNV statt WENNFEHLER. WENNNV reagiert ausschließlich auf #NV und lässt #WERT! oder #BEZUG! stehen. Sinnvoller als ein leerer Text ist dabei meist eine sprechende Ausgabe wie "Artikel unbekannt", damit beim Prüfen sofort klar ist, was gemeint ist.
Dazu passende Kurse
Fehlerfreie Verweise im Excel-Kurs trainieren kannst du bei cmt, dort arbeitest du mit Listen, die dieselben Tücken mitbringen wie deine eigenen Exporte.
Dieselben Probleme mit Leerzeichen und Textzahlen tauchen im Serienbrief und beim Import wieder auf, ein Blick auf die Office-Kurse für Anwender im Büro lohnt sich also auch dann, wenn du eigentlich nur mit Tabellen arbeitest.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Der passende Lernpfad
Wenn du nicht nur ein Thema abhaken, sondern eine Rolle ausfüllen willst, zeigt dir der Lernpfad die Kurse in der Reihenfolge, in der sie aufeinander aufbauen.
Excel für Berechnungen
Wenn du bei Excel-Formeln noch zögerst, verlierst du jeden Tag an Tempo, Sicherheit und Kontrolle. Andere Automatisieren längst, während du noch rechnest.
Dieser Lernpfad macht Schluss mit Rätselraten und Copy-&-Paste-Magie: saubere Logik, belastbare Ergebnisse, Formeln, die schlüssig sind. Jetzt einsteigen heißt: Fehler vermeiden, Zeit gewinnen und Excel endlich souverän beherrschen – dort, wo es wirklich zählt!
Der Einstieg
Wo du genau das übst
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Sehr gut vorbereitetes Seminar mit hilfreichen Informationen und anschaulicher Vermittlung. Vielen Dank!
Häufige Fragen
Warum findet ZÄHLENWENN den Wert, SVERWEIS aber nicht?
Kann ich #NV im Ausdruck unterdrücken, ohne die Formel zu ändern?
Hilft XVERWEIS gegen #NV?
Passt thematisch dazu
Wenn du unsicher bist, was Excel beim Nachschlagen überhaupt tut, hilft ein Blick auf die Grundmechanik aus Suchkriterium, Matrix und Spaltenindex .
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Excel-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Fehlersuche, die nicht beim Ausprobieren endet
Wie du Datenquellen so aufbereitest, dass Verweise gar nicht erst ins Leere laufen, gehört bei cmt zum festen Bestandteil der Excel-Kurse.