17 Gründe, warum Ihr XVERWEIS nicht funktioniert
In diesem Tutorial zeigen wir Ihnen, wie Sie XVERWEIS-Formeln in Excel debuggen können. Wenn Ihre Excel-Version XVERWEIS nicht unterstützt, lesen Sie, wie Sie stattdessen SVERWEIS verwenden können.
Die meisten Fehler in unseren XVERWEIS-Formeln hängen mit den Eigenschaften und Kriterien dieser Funktion zusammen. Die Syntax der XVERWEIS-Funktion zeigt uns nur die notwendigen Argumente, um sie auszuführen, aber sie informiert uns nicht vollständig über die Eigenschaften und Kriterien, die notwendig sind, damit sie korrekt funktioniert.
Deshalb werden wir in diesem Tutorial nicht nur lernen, wie man die Fehler der XVERWEIS-Funktion diagnostiziert, sondern auch mehr über ihre Eigenschaften und Kriterien erfahren.
Der Fehler #NV
Wenn die XVERWEIS-Funktion keine Übereinstimmung findet, gibt sie die Fehlermeldung #NV zurück. Lassen Sie uns das Problem diagnostizieren.
1. #NV – Keine exakte Übereinstimmung
Standardmäßig sucht die XVERWEIS-Funktion nach einer exakten Übereinstimmung. Wenn das Element nicht im Nachschlage-Array enthalten ist, gibt sie den Fehler #NV zurück.
2. #NV – Keine ungefähre Übereinstimmung
Wenn der Vergleichsmodus (d.h. das 5. Argument) auf -1 eingestellt ist, sucht die XVERWEIS-Funktion zuerst nach einer exakten Übereinstimmung, aber wenn es keine gibt, sucht sie den größten Wert aus dem Nachschlage-Array, der kleiner als der Suchwert ist. Wenn es also keine exakte Übereinstimmung gibt und alle Werte aus dem Nachschlage-Array größer als der Suchwert sind, gibt die XVERWEIS-Funktion den Fehler #NV zurück.
Anstelle von größter Wert <= Nachschlagewert kann auch nach dem Gegenteil, kleinster Wert >= Nachschlagewert, gesucht werden, wenn der Vergleichsmodus auf 1 eingestellt wird. Die letztgenannte Bedingung findet den kleinsten Wert, der größer oder gleich dem Suchwert ist, und wenn alle Werte kleiner als der Suchwert sind, wird der #NV-Fehler zurückgegeben.
Wenn der Wert tatsächlich nicht vorhanden ist, funktioniert die Formel ordnungsgemäß. Wir empfehlen, eine Fehlerbehandlung hinzuzufügen, so dass ein anderer Wert anstelle des #NV-Fehlers ausgegeben wird, wenn der Wert nicht gefunden wird.
=XVERWEIS(G3;B3:B7;C3:C7; "Kein Treffer!";1)
Wenn der Suchwert jedoch existiert und die XVERWEIS-Funktion ihn nicht finden kann, gibt es einige mögliche Gründe.
3. #NV – Als Text gespeicherte Zahlen (und andere Datentypfehler)
Eine der wichtigsten Bedingungen für XVERWEIS besteht darin, dass die Datentypen des Suchwertes und des Nachschlage-Arrays übereinstimmen müssen. Wenn dies nicht der Fall ist, kann die XVERWEIS-Funktion keine Übereinstimmung finden.
Das häufigste Beispiel hierfür sind als Text gespeicherte Zahlen.
Eine Möglichkeit, dieses Problem zu lösen, besteht darin, das Tool Text in Spalten in Excel zu verwenden, um als Text gespeicherte Zahlen in echte Zahlen umzuwandeln.
Gehen Sie wie folgt vor:
- Markieren Sie die Zellen und gehen Sie auf Daten > Datentools > Text in Spalten
- Wählen Sie im Popup-Fenster die Option Getrennt und klicken Sie auf Weiter.
- Im nächsten Schritt wählen Sie Tabstopp und klicken auf Weiter.
- Im letzten Schritt wählen Sie den gewünschten Datentyp (z. B. Datum) und das Format aus und klicken auf Fertig stellen.
- Der Bereich wird in den eingestellten Datentyp (z. B. Datum) umgewandelt.
4. #NV – Zusätzliche Leerzeichen
Textsuchen sind fehleranfällig aufgrund von zusätzlichen Leerzeichen. In diesem Beispiel enthält der Suchwert „Abo 2“ zwei Leerzeichen und stimmt daher nicht mit „Abo 2“ (ein Leerzeichen) aus dem Nachschlagebereich überein.
Eine Möglichkeit, dieses Problem zu lösen, ist die Verwendung der GLÄTTEN-Funktion, um die zusätzlichen Leerzeichen zu entfernen. Wir können sie sowohl auf den Suchwert als auch auf das gesamte Nachschlage-Array anwenden.
=XVERWEIS(GLÄTTEN(E3);GLÄTTEN(B3:B7);C3:C7)
Hinweis: Die GLÄTTEN-Funktion entfernt zusätzliche Leerzeichen zwischen den Wörtern, bis sie durch ein Leerzeichen voneinander getrennt sind, und alle Leerzeichen vor dem ersten und nach dem letzten Wort eines Textes oder eines Satzes werden entfernt.
5. #NV – Nachschlage-Array nicht sortiert
Wenn das 6. Argument (d.h. Suchmodus) der XLOOKUP-Funktion entweder auf 2 oder -2 eingestellt ist, verwendet diese die binäre Suchmethode, um den Wert nachzuschlagen, und diese Methode erfordert einen sortierten Datensatz (d.h., 2 – aufsteigende Reihenfolge, -2 – absteigende Reihenfolge). Wenn das Lookup-Array nicht sortiert ist, gibt die XVERWEIS-Funktion entweder einen falschen Wert oder den Fehler #NV zurück.
Um das obige Problem zu lösen, müssen wir die Daten entweder manuell oder durch Formeln sortieren.
Manuell sortieren
- Markieren Sie die gesamten Daten und gehen Sie dann auf die Registerkarte Daten > Sortieren und Filtern und klicken Sie auf Sortieren.
- Ein Pop-up-Fenster wird angezeigt. Wählen Sie die Spalte des Nachschlage-Arrays aus und stellen Sie die gewünschte Reihenfolge ein (z. B. Nach Alter (absteigend) für Datumsangaben). Klicken Sie auf OK.
- Die Daten werden auf der Grundlage des Nachschlage-Arrays sortiert (z. B. B3:B7). XVERWEIS wird nun neu berechnet und zeigt das richtige Ergebnis an.
Sortieren mit den Funktionen SORTIEREN und SORTIERENNACH
Wir können auch die Funktionen SORTIEREN und SORTIERENNACH verwenden, um das Nachschlage-Array (z. B. B3:B7) bzw. das Rückgabe-Array (z. B. C3:C7) zu sortieren.
=XVERWEIS(E3;SORTIEREN(B3:B7);SORTIERENNACH(C3:C7;B3:B7);;-1;2)
Hinweis: Standardmäßig sortieren die Funktionen SORTIEREN und SORTIERENNACH ein Array in aufsteigender Reihenfolge. Der Hauptunterschied zwischen den beiden besteht darin, dass die SORTIEREN-Funktion immer alle Spalten innerhalb eines Arrays zurückgibt, während SORTIERENNACH eine bestimmte Spalte (z.B. C3:C7) aus einem Array zurückgeben kann.
Der Fehler #WERT!
Wenn eine Eingabe die Kriterien für die Durchführung von XVERWEIS nicht erfüllt, funktioniert die Funktion nicht und gibt stattdessen den Fehler #WERT! zurück.
6. #WERT! – Nicht einheitliche Zeilenanzahl
Eine Anforderung der XVERWEIS-Funktion (für ein typische vertikale Suche) besteht darin, dass die Zeilenanzahlen des Nachschlage-Arrays und des Rückgabe-Arrays identisch sein müssen.
7. #WERT! – Horizontal gegen Vertikal
Das Rückgabe-Array kann 2-dimensional sein und mehrere Spalten (oder Zeilen für eine horizontale Suche) zurückgeben. Die Anzahl der Zeilen (oder Spalten) muss jedoch übereinstimmen.
Diese Eigenschaft ermöglicht es der XVERWEIS-Funktion, mehr als eine Spalte (oder Zeile für eine horizontale Suche) zurückzugeben, aber die Konsequenz ist es, dass wir ein horizontales 1D-Rückgabe-Array nicht mit einem vertikalen 1D-Nachschlage-Array koppeln können, im Gegensatz zur INDEX-VERGLEICH-Formel, wo wir entgegengesetzte Orientierungen machen können.
=INDEX(E2:I2;MATCH(D5;B3:B7;0))
Wenn Sie dies tun, akzeptiert XVERWEIS das Rückgabe-Array als 2D-Eingabe mit einer Zeilenanzahl, die nicht mit der Zeilenanzahl des Nachschlage-Arrays übereinstimmt. Daher gibt es den #WERT!-Fehler.
=XVERWEIS(D5;B3:B7;E2:I2)
Um dieses Problem zu lösen, können wir die MTRANS-Funktion verwenden, um die Richtung eines der 1D-Arrays zu transponieren:
=XVERWEIS(D5;B3:B7;MTRANS(E2:I2))
Hinweis: Die Funktion MTRANS vertauscht die relativen Zeilen- und Spaltenkoordinaten einer Zelle in einer Liste. In E2:I2 ist F2 Zeile 1, Spalte 2 relativ zum Bereich. Die transponierten Koordinaten sind also Zeile 2, Spalte 1. G2 befindet sich in Zeile 1, Spalte 3 und wird in Zeile 3, Spalte 1 transponiert und so weiter.
8. #WERT! – Wertebereich
Das 5. Argument (Vergleichsmodus) und das 6. Argument (Suchmodus) der XVERWEIS-Funktion müssen gültige Eingaben haben. Ist dies nicht der Fall, gibt XVERWEIS den Fehler #WERT! zurück.
Hinweis: Der Vergleichsmodus kann nur 0, -1,1 und 2 annehmen, während der Suchmodus nur 1,-1,2 und-2 akzeptieren kann.
Der Fehler #NAME?
Der Fehler #NAME? wird durch Folgendes ausgelöst:
- Falsche Schreibweise des Funktionsnamens
- Falsche Schreibweise eines Bezugs (Arbeitsmappen-/Blattbezug und benannte Bereiche).
- Ein nicht vorhandener Benannter Bereich
- Text, der nicht in doppelten Anführungszeichen steht.
9. #NAME? – Tippfehler bei Funktionsnamen
Wenn der Name einer Funktion einen Tippfehler enthält, gibt Excel die Fehlermeldung #NAME? zurück.
10. #NAME? – Benannter Bereich existiert nicht
Der Fehler #NAME? kann auch durch einen undefinierten benannten Bereich in der Formel verursacht werden. Entweder existiert der benannte Bereich nicht wirklich oder es liegt ein Tippfehler im Namen vor.
Hinweis: Es gibt zwei benannte Bereiche in dem obigen Blatt: Abos und Preise. Der Tippfehler im benannten Bereich Preise führt zu einem benannten Bereich, der nicht existiert, und jeder Text, der nicht in Anführungszeichen eingeschlossen ist, wird als benannter Bereich betrachtet, was ebenfalls zu dem Fehler #NAME? führen kann.
11. #NAME? – Tippfehler bei Arbeitsmappen- bzw. Blattbezug
Wenn Arbeitsmappen- bzw. Blattnamen Leerzeichen und Sonderzeichen außer Unterstrichen enthalten, müssen wir den Arbeitsmappen- bzw. Blattbezug in einfache Anführungszeichen setzen. Wenn dies nicht der Fall ist, kann Excel den Arbeitsmappen- bzw. Blattbezug nicht erkennen und gibt den Fehler #NAME? zurück.
Der Fehler #ÜBERLAUF!
Es gibt auch eine Reihe von Kriterien, wenn ein Array zurückgegeben wird, und wenn diese nicht erfüllt sind, wird der Fehler ÜBERLAUF! anstelle des Arrays zurückgegeben.
12. #ÜBERLAUF! – Überlauf blockiert
Eine dynamische Array-Formel überschreibt keine Werte, die in ihrem Überlaufbereich liegen. Stattdessen wird die Array-Ausgabe blockiert, und der Fehler ÜBERLAUF! wird zurückgegeben.
13. #ÜBRLAUF! – Tabelle vs. dynamische Arrays
Wir können keine XVERWEIS-Formeln mir Arrays in Tabellen verwenden, da diese keine dynamischen Array-Formeln unterstützen.
Stattdessen müssen Sie eine XVERWEIS-Funktion ohne Array verwenden, bei der das Suchkriterium ein einzelner Wert und kein Array von Werten ist.
14. #ÜBERLAUF! – Bereich außerhalb der Grenzen
Wenn die Größe des Ausgabe-Arrays aus einer XVERWEIS-Formel die Blattgrenzen (Zeile und Spalte) überschreitet, wird stattdessen #ÜBERLAUF! zurückgegeben. (z.B. F3:F < ganzer Spaltenbereich)
Andere Probleme
Es gibt XVERWEIS-Probleme, die keine Fehler auslösen, weil sie keines der Kriterien verletzen.
15. Falscher Bereich
Auch wenn die Zeilengrößen des Nachschlage-Arrays und des Rückgabe-Arrays gleich sind, liefert XVERWEIS ein falsches Ergebnis, wenn ihre Positionen nicht übereinstimmen.
16. 1D/2D-Array von Nachschlage-Werten gegen 2D-Rückgabe-Array
Das Suchkriterium in XVERWEIS kann ein 1D- oder 2D-Array sein, wodurch die XVERWEIS-Funktion in ein dynamisches Array umgewandelt wird, das in benachbarte Zellen überläuft.
Man könnte erwarten, dass eine Kombination aus einem 1D-Suchwert-Array (z. B. F3:F4) und einem 2D-Rückgabe-Array (z. B. C3:D7) eine 2D-Ausgabe ergibt, bei der die Zeilenanzahl und die Spaltenanzahl vom Suchwert-Array bzw. vom 2D-Rückgabe-Array abhängen, aber das ist nicht der Fall. Die Zeilenanzahl basiert immer noch auf dem 1D-Suchwert-Array, aber die Spaltenanzahl ist 1, was bedeutet, dass nur die erste Spalte des Rückgabe-Arrays zurückgegeben wird.
17. Kopieren von XVERWEIS mit relativen Bezügen
Wenn wir eine XVERWEIS-Formel mit relativen Bezügen ziehen, dann werden diese auch relativ zu ihrer Position angepasst, was zu falschen Ergebnissen führen kann.
Wenn wir unsere XVERWEIS-Formel in nachfolgende Zellen kopieren oder ziehen wollen, müssen wir die Bezüge für das Suchwert-Array und das Rückgabe-Array in absolute Zellbezüge umwandeln. Dies können wir tun, indem wir das Dollar-Symbol vor dem Spaltenbuchstaben und der Zeilennummer einfügen oder indem wir F4 drücken, während sich der Mauszeiger in dem Bezug innerhalb der Formel befindet.
