In diesem Thema werden die häufigsten SVERWEIS-Gründe für ein fehlerhaftes Ergebnis der Funktion beschrieben, und es werden Vorschläge für die Verwendung von INDEX und MATCH bereitgestellt.
Tipp: Lesen Sie auch die Kurzübersichtskarte: SVERWEIS – Tipps zur Problembehandlung. Dort werden die häufigsten Gründe für #N/A-Fehler in einer praktischen PDF-Datei dargestellt. Sie können die PDF-Datei mit anderen Personen teilen oder für Referenzzwecke ausdrucken.
Problem: Der Nachschlagewert befindet sich nicht in der ersten Spalte des Arguments Matrix.
Eine Einschränkung von SVERWEIS besteht darin, dass die Funktion nur in der äußerst linken Spalte der Matrix nach Werten suchen kann. Wenn Sie also einen Wert suchen, der sich nicht in der ersten Spalte der Matrix befindet, wird Ihnen ein #N/A-Fehler angezeigt.
In der folgenden Tabelle soll die Anzahl der verkauften Einheiten an Grünkohl abgerufen werden.
Der #N/A-Fehler führt dazu, dass das Suchkriterium „Grünkohl“ in der zweiten Spalte (Produce) des Matrix-Arguments A2:C10 angezeigt wird. In diesem Fall sucht Excel danach in Spalte A statt in Spalte B.
Lösung: Sie können versuchen, dieses Problem zu beheben, indem Sie SVERWEIS so anpassen, dass die Funktion auf die richtige Spalte verweist. Wenn dies nicht möglich ist, versuchen Sie, die Spalten zu verschieben. Dies kann sehr unpraktisch sein, wenn es um komplexe Tabellen geht, in denen Zellwerte die Ergebnisse anderer Berechnungen sind. Möglicherweise gibt es auch andere logische Gründe, warum Sie Spalten nicht so einfach verschieben können. Die Lösung besteht darin, eine Kombination aus den INDEX- und VERGLEICH-Funktionen zu verwenden. Hiermit kann ein Wert in einer Spalte unabhängig von deren Position in der Nachschlagetabelle gesucht werden. Informationen dazu finden Sie im nächsten Abschnitt.
Erwägen Sie stattdessen die Verwendung von INDEX/VERGLEICH.
INDEX und VERGLEICH sind gute Alternativen in vielen Fällen, in denen SVERWEIS Ihren Anforderungen nicht gerecht wird. Der größte Vorteil von INDEX/VERGLEICH besteht darin, dass Sie einen Wert in einer Spalte an einer beliebigen Position in der Nachschlagetabelle suchen können. INDEX gibt einen Wert aus einer angegebenen Tabelle/einem angegebenen Bereich entsprechend seiner Position zurück. VERGLEICH gibt die relative Position eines Werts in einer Tabelle/einem Bereich zurück. Verwenden Sie INDEX und VERGLEICH in einer Formel zusammen, um einen Wert in einer Tabelle/einer Matrix nachzuschlagen, indem Sie die relative Position des Werts in der Tabelle/der Matrix angeben.
Die Verwendung von INDEX/VERGLEICH anstelle von SVERWEIS bietet zahlreiche Vorteile:
-
Bei der Verwendung von INDEX und VERGLEICH muss sich der Rückgabewert nicht in derselben Spalte wie der Nachschlagespalte befinden. Dies unterscheidet sich von SVERWEIS, wo sich der Rückgabewert im angegebenen Bereich befinden muss. Warum ist das wichtig? Bei SVERWEIS müssen Sie die Nummer der Spalten kennen, die den Rückgabewert enthält. Dies mag zwar im ersten Moment nicht nach einer großen Herausforderung klingen, kann bei einer umfangreichen Tabelle, in der Sie die Anzahl der Spalten zählen müssen, jedoch mühsam werden. Wenn Sie in der Tabelle eine Spalte hinzufügen/entfernen, müssen Sie darüber hinaus die Zählung erneut durchführen und das Argument Spaltenindex aktualisieren. Mit INDEX und VERGLEICH entfällt die Zählung, da sich die Nachschlagespalte von der Spalte unterscheidet, in der sich der Rückgabewert befindet.
-
Mithilfe von INDEX und VERGLEICH können Sie entweder eine Zeile oder eine Spalte oder sogar beides in jeder beliebigen Matrix angeben. Dies bedeutet, dass Sie Werte sowohl vertikal als auch horizontal nachschlagen können.
-
INDEX und VERGLEICH können zum Nachschlagen von Werten in jeder beliebigen Spalte verwendet werden. Im Gegensatz zur Funktion SVERWEIS, bei der Sie nur einen Wert in der ersten Spalte einer Tabelle nachschlagen können, funktionieren INDEX und VERGLEICH auch dann, wenn sich der Nachschlagewert in der ersten oder letzten Spalte oder irgendwo dazwischen befindet.
-
INDEX und VERGLEICH bieten die Flexibilität zur Erstellung eines dynamischen Verweises auf die Spalte, die den Rückgabewert enthält.Dies bedeutet, dass Sie Ihrer Tabelle Spalten hinzufügen können, ohne die Funktionalität von INDEX und VERGLEICH zu beeinträchtigen. Andererseits funktioniert SVERWEIS nicht mehr, wenn Sie der Tabelle eine Spalte hinzufügen müssen, da die Funktion einen statischen Verweis auf die Tabelle erstellt.
-
INDEX und VERGLEICH bieten mehr Flexibilität bei Übereinstimmungen.INDEX und VERGLEICH können als Nachschlagewert eine exakte Übereinstimmung oder einen größeren oder kleineren Wert finden. SVERWEIS sucht nur nach der nächstliegenden Übereinstimmung für einen Wert (standardmäßig) oder nach einem exakten Wert. SVERWEIS geht darüber hinaus standardmäßig davon aus, dass die erste Spalte der Matrix alphabetisch sortiert ist, und angenommen, Ihre Tabelle wurde nicht in dieser Weise eingerichtet, gibt SVERWEIS die erste nächstliegende Übereinstimmung zurück, wobei dies möglicherweise nicht die Daten sind, nach denen Sie suchen.
Syntax
Zum Erstellen einer Syntax für INDEX/VERGLEICH müssen Sie das Matrix/Verweis-Argument der INDEX-Funktion verwenden und die Syntax von VERGLEICH darin schachteln. Dadurch ergibt sich folgendes Format:
=INDEX(Matrix oder Verweis; VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])
Wir wollen nun INDEX/VERGLEICH verwenden, um SVERWEIS im obigen Beispiel zu ersetzen. Die Syntax sieht folgendermaßen aus:
=INDEX(C2:C10;VERGLEICH(B13;B2:B10;0))
Einfach ausgedrückt bedeutet dies:
=INDEX(ermittle einen Wert aus C2:C10 aus, und zwar per VERGLEICH(Grünkohl, ein Wert, der sich irgendwo in der Matrix B2:B10 befindet, wobei der Rückgabewert der erste Wert ist, der Grünkohl entspricht))
Die Formel sucht nach dem ersten Wert in C2:C10, der Grünkohl entspricht (in B7) und gibt den Wert in C7 (100) zurück, welches der erste Wert ist, der mit "Grünkohl" übereinstimmt.
Problem: Die exakte Übereinstimmung wird nicht gefunden.
Wenn das Argument Bereich_Verweis FALSCH ist und SVERWEIS keine exakte Übereinstimmung in Ihren Daten finden kann, wird der #N/A-Fehler zurückgegeben.
Lösung: Wenn Sie sicher sind, dass die entsprechenden Daten in Ihrer Tabelle vorhanden sind, aber von SVERWEIS nicht entdeckt werden, vergewissern Sie sich, dass die Zellen, auf die verwiesen wird, nicht über verborgene Leerzeichen oder nicht druckbare Zeichen verfügen. Stellen Sie darüber hinaus sicher, dass alle Zellen denselben Datentyp aufweisen. So müssen Zellen mit Zahlen beispielsweise als Zahl und nicht als Text formatiert werden.
Überlegen Sie außerdem, die Funktionen SÄUBERN oder GLÄTTEN zu verwenden, um die Daten in den Zellen zu bereinigen.
Problem: Der Nachschlagewert ist kleiner als der kleinste Wert in der Matrix.
Wenn das Argument Bereich_Verweis auf WAHR festgelegt wurde und der Nachschlagewert kleiner als der kleinste Wert in der Matrix ist, wird der #N/A-Fehler angezeigt. Mit WAHR wird nach einer ungefähren Übereinstimmung in der Matrix gesucht, und es wird der am nächsten liegende Wert zurückgegeben, der kleiner als der Nachschlagewert ist.
Im folgenden Beispiel beträgt der Nachschlagewert 100, es gibt jedoch keine Werte im Bereich B2:C10, die kleiner als 100 sind, daher wird der Fehler zurückgegeben.
Lösung:
-
Korrigieren Sie bei Bedarf den Nachschlagewert.
-
Wenn Sie den Nachschlagewert nicht ändern können und mehr Flexibilität bei übereinstimmenden Werten benötigen, sollten Sie INDEX/VERGLEICH anstelle von SVERWEIS verwenden. Weitere Informationen finden Sie im Abschnitt weiter oben in diesem Artikel. Mit INDEX/VERGLEICH können Sie Werte nachschlagen, die größer, kleiner oder gleich dem Nachschlagewert sind. Weitere Informationen zur Verwendung von INDEX/VERGLEICH anstelle von SVERWEIS finden Sie im vorstehenden Abschnitt dieses Artikels.
Problem: Die Nachschlagespalte ist nicht in aufsteigender Reihenfolge sortiert.
Wenn das Argument Bereich_Verweis auf WAHR festgelegt wurde und eine der Nachschlagespalten nicht in aufsteigender Reihenfolge (A-Z) sortiert ist, wird der #N/A-Fehler angezeigt.
Lösung:
-
Ändern Sie die Funktion SVERWEIS so, dass sie nach einer exakten Übereinstimmung sucht. Legen Sie hierfür das Argument Bereich_Verweis auf FALSCH fest. Für FALSCH ist keine Sortierung notwendig.
-
Verwenden Sie die Funktionen INDEX/VERGLEICH zum Nachschlagen eines Werts in einer unsortierten Tabelle.
Problem: Bei dem Wert handelt es sich um eine große Gleitkommazahl.
Wenn sich in den Zellen Zeitwerte oder große Dezimalzahlen befinden, gibt Excel den #N/A-Fehler aufgrund der Gleitkommagenauigkeit zurück. Gleitkommazahlen sind die Zahlen, die nach dem Dezimalkomma folgen. (Beachten Sie, dass Excel Zeitwerte als Gleitkommazahlen speichert.) Excel kann keine Zahlen mit sehr langen Nachkommastellen speichern. Damit die Funktion ordnungsgemäß funktioniert, müssen die Gleitkommazahlen auf fünf Dezimalstellen gerundet werden.
Lösung: Kürzen Sie die Zahlen, indem Sie sie mit der Funktion RUNDEN auf fünf Dezimalstellen runden.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in den Communities erhalten.
