Office-Blog

Die Funktion SVERWEIS ist wohl den meisten Excel-Anwendern bekannt: Sie durchsucht die erste Spalte eines Zellbereichs nach einem Wert und gibt aus einer der Folgespalten den korrespondierenden Wert zurück. Soll die Position der Folgespalte dynamisch gehalten werden, hilft die Funktion VERGLEICH.

Doch in »intelligenten« Tabellen stößt sie an Grenzen: Dann nämlich, wenn in den Spaltenüberschriften Zahlen stehen, z. B. Jahreszahlen.

Die VERWEIS-Funktion liefert einen Fehlerwert, wenn im Kopf einer »intelligenten« Tabelle Zahlen stehen

Wo liegt die Ursache für dieses Problem? Und wie lässt es sich lösen?

Zahl oder Text? Diese Feinheit in Spaltenüberschriften ist entscheidend

Schon beim Eingeben von Zahlen in die Überschriften einer intelligenten Tabelle fällt auf: Zahlen stehen am linken Zellrand, obwohl sie üblicherweise in Excel immer rechtsbündig ausgerichtet sind.

Das passiert auch, wenn ein Zellbereich nachträglich über Start > Als Tabelle formatieren (oder mit Strg + T) in eine »intelligente« Tabelle umgewandelt wird:

• Gerade noch rechtsbündig stehende Zahlen in der ersten Zeile werden plötzlich wie Text, also linksbündig ausgerichtet.
• Selbst das Umstellen des Zellenformats auf Zahl ändert nicht an der linksbündigen Ausrichtung.
• Viel gravierender: Wird per Formel versucht, nach passenden Zahlen zu suchen, liefert die Formel den Fehlerwert #NV. Die Änderung des Zellformats bringt also nicht den gewünschten Erfolg. Warum ist das so?

Das Umstellen auf das Format ZAHL führt nicht zum Erfolg

Eigentlich ganz einfach: Das Text-Format berücksichtigen

Excel interpretiert Eingaben in der ersten Zeile einer »intelligenten« Tabelle grundsätzlich als Text. Und zwar unabhängig vom gewählten Zellformat.

Suchen nun SVERWEIS, VERGLEICH oder andere Funktion nach einer Zahl, kann diese in den Texten der Spaltenüberschriften nicht gefunden werden.

Die Lösung: Es muss demzufolge nach Text gesucht werden.

Doch wie geht das?

Die Funktion TEXT und ein passendes Textformat sind Voraussetzung für die Lösung.

• TEXT konvertiert Zahlen in Text.
• Für eine beliebig lange Zahlenfolge wird das Textformat „#“ verwendet.
• Es wird also nicht die Zahl aus B10 mit den Einträgen der Spaltenüberschriften verglichen, sondern die in einen Text umgewandelte Zahl aus B10. Der korrekte Formelbestandteil dafür lautet TEXT(B10;“#“).

Problem gelöst: Jetzt klappt es mit der VERGLEICH-Funktion. Sie kommt so auch mit Zahlen in der Kopfzeile von »intelligenten« Tabellen klar.

Nach dem Umwandeln der gesuchten Zahl in ein Text-Format liefert VERGLEICH das korrekte Ergebnis

Tipp: So klappt es auch bei Zahlen mit Kommastellen

Sollten in der Kopfzeile keine ganzen Zahlen, sondern z. B. Dezimalzahlen mit zwei Nachkommastellen stehen, muss dies in der TEXT-Funktion berücksichtigt werden.

In dem Fall muss das Textformat mit „#,##“ angegeben werden.

Übrigens: Kurioserweise reicht hierbei vor dem Komma ein # für eine beliebig lange Zahlenfolge, während nach dem Komma die richtige Anzahl an # für die Nachkommstellen einzugeben ist.