Excel: SVERWEIS funktioniert nicht richtig
Hallo Zusammen,
ich habe große Datentabellen die ich per SVERWEIS kombinieren muss. Bei einigen Elementen klappt es und Excel schreibt den richtigen Wert rein, bei manchen klappt es nicht und es kommt #NV. Alle Zellen sind auch gleich formatiert und es sind auch keine Leerzeichen o.ä. enthalten.
Wenn ich dann bei den #NVs in der zweiten Tabelle nach dem Element suche, dann findet er es auch und sobald ich in die erste Tabelle zurückwechsle erscheint dann auch der richtige Treffer und das #NV ist verschwunden. Das ist aber natürlich nicht die Zielsetzung... Weiß jemand woran das liegen könnte bzw. wie ich das am besten behebe?
Vielen Dank und beste Grüße
3 Antworten
Falls es am SVERWEIS selbst liegt .... probier mal die Kombination aus INDEX() und VERGLEICH() stattdessen, um das auszuchließen.
Denke ich auch.
Im Zweifel mal einfach die Such-Zelle und die Zelle, die eigentlich gefunden werden soll direkt gleichsetzen mit .z..B
=A1= C23 in einer dritten Zelle.
Wenn dort dann FALSCH steht, stimmt etwas mit den Werten nicht, die sind dann nicht wirklich gleich, wenn dort WAHR steht, stimmt etwas mit der Formel nicht.
wenn excel beim sverweis rumspinnt hilft es oft beim bereichs_verweis (also das 4. kriterium der Funktion Sverweis) mal mit wahr/falsch rumzuspielen.
Hallo Jackie251,
dank Dir - das habe ich auch schon probiert aber das hilft leider gar nichts. Ich habe die Datenmenge als möglich Ursache im Verdacht. Aber komisch, dass es bei manchen Einträgen sofort funktioniert und bei anderen erst durch den Trigger der manuellen Suche. Ich dachte vielleicht gibt es die Möglichkeit die Berechnung zu forcieren, o.ä.
Besten Dank!
Damit "spielt" man nicht rum, sondern es ist genau felsgelegt was passiert wenn ich Bereich_Verweis auf Wahr oder Falsch setze:
Bereich_Verweis Optional. Ein Wahrheitswert, der angibt, ob SVERWEIS eine genaue Entsprechung oder eine ungefähre Entsprechung suchen soll: Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, wird eine genaue oder ungefähre Entsprechung zurückgegeben. Wird keine genaue Entsprechung gefunden, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist. Wichtig Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, müssen die Werte in der ersten Spalte von Matrix in aufsteigender Sortierreihenfolge geordnet sein, andernfalls gibt SVERWEIS möglicherweise nicht den richtigen Wert zurück.
Weitere Informationen finden Sie unter Sortieren von Daten in einem Bereich oder in einer Tabelle.
Wenn Bereich_Verweis FALSCH ist, müssen die Werte in der ersten Spalte von Matrix nicht sortiert sein.
Ist das Argument Bereich_Verweis mit FALSCH angegeben, kann SVERWEIS nur nach einer genauen Entsprechung suchen. Wenn in der ersten Spalte von Matrix mindestens zwei Werte vorhanden sind, die dem Suchkriterium entsprechen, wird der erste gefundene Wert verwendet. Wird keine genaue Entsprechung gefunden, wird der Fehlerwert #NV zurückgegeben.
hatte mich schon beim schreiben gefragt wann einer der hiesigen excelgötter mich mit einem Halbseitigen absatz für das "rumspielt" meines zweizeilers zurechtweist. Hat länger gedauert als ich dachte..
Andererseits hier läuft ja eh vieles anders, Zb beispiel wurde hier überraschender weise Sverweis als gangbare Formel akzeptiert. Normalerweise wird hier ja (fast) jedes Sverweis frage von den Index=> Vergleich Missionaren als schonmal grundsätzlich falsche Ausgangsbasis angeprangert.
Der Bereichsverweis wird häufig falsch verstanden. Wahr wird häufig als "Vergleich soll exakt sein" verstanden. Ein durchaus verständlicher Fehler, zumal die Fomelbeschreibung im Editor auch unzureichend ist, (da Excel 2007/10 die deutsche Übersetzung länger ist als im Hilfsfenster platz und daher einfach abgeschnitten wird). Ach die Tatsache das es bei manchen Werte geht und nur bei gewissen werten #NV zurückgegeben wird passt ins Bild.
Daher mein Hinweis, sich die wahr/falsch Option des Bereichsverweis nochmal anzusehen, denn dort liegt in meiner Praxis in gefühlten 2/3 der Fälle das Problem und vor allem ist dies extrem einfach und schnell zu überprüfen. Man verliert also nicht wirklich was, falls das nicht die Lösung gewesen ist.
Ohne weitere Kenntnisse über die Tabelle ist es ansonsten sehr schwer zu helfen, aber das erkennt man ja auch an deiner Antwort. DAs jemand die automatische Berechnung deaktiviert kommt in meinem umfeld pratisch nie vor - und es wäre wenn dann auch eher unwahrscheinlich das nur in gewissen bereichen einer langen Tabelle #nv auftaucht.
Also im grunde haben wir beide - aufgrund fehlender Fakten - einen möglichen Lösungsansatz geraten.
Normalerweise wird hier ja (fast) jedes Sverweis frage von den Index=> Vergleich Missionaren als schonmal grundsätzlich falsche Ausgangsbasis angeprangert.
War ich zu faul zu. Ausserdem weisst du es ja eh schon :-))
Dazu müsste man Deine Tabelle genau kennen.
Sind Deine Werte denn Zahlen?
Stehen die alle rechtsbündig auch ohne dass Du das festgelegt hast?
Sonst kann es nämlich sein, dass sie teilweise Text sind, auch wenn die Zelle als Zahl formatiert ist. Durch das 'reinklicken werden sie dann zu echten Zahlen.
In dem Fall hilft Folgendes:
Schreibe in eine leere Zelle eine 1.
Kopiere diese Zelle.
Markiere die Wertespalte komplett.
Rechtsklick auf diese Spalte/ Inhalte einfügen/ Inhalte einfügen: Vorgang: "Multiplizieren"/ OK.
Hilft Dir das?
Ohne deine Tabelle zu sehen wird es nicht möglich sein das genauer zu analysieren. Aber sei versichert der SVERWEIS verhält sich nicht in einer Zelle so und in einer anderen anders. Wenn dann liegt es an die Grunddaten. Hast du eventuell in den Optionen die automatische Berechnung ausgeschaltet? Datei --> Optionen --> Formel --> Berechnungsoptionen
Wenn SVERWEIS #NV zurückgibt wird es das INDEX(VERGLEICH()) auch tun.