XVERWEIS für Groß- und Kleinschreibung 6

Manche vermeintlichen Gewissheiten stellen sich irgendwann als falsch heraus. Dazu gehört beispielsweise auch die irrige Annahme, dass Verweisfunktionen wie XVERWEIS (oder SVERWEIS) schon immer die richtigen Ergebnisse liefern werden.

In vielen Fällen trifft das zwar zu – korrekte Anwendung mal vorausgesetzt. Wenn aber bei der Suche nach Groß- und Kleinschreibung unterschieden werden muss, dann sieht es ziemlich düster aus. Die Schreibweise wird nämlich sowohl vom SVERWEIS als auch vom neuen XVERWEIS einfach ignoriert. Und damit kann es eben zu falschen Ergebnissen kommen.

Wie man doch noch zu einer richtigen Lösung kommen kann, wenn es auf korrekte Schreibweisen ankommt, zeigt dieser Artikel.


Beispieldatei herunterladen

Problem und Lösung für den XVERWEIS

In meinem kleinen Beispiel ist jedem Buchstaben eine Zahl zugeordnet: a=1, b=2, …, A=101, B=102 und so weiter
Nun soll in Zelle A3 ein Buchstabe eingegeben und in der Zelle daneben der dazugehörige Wert über einen XVERWEIS ermittelt werden:

XVERWEIS. Noch sieht alles gut aus…

Soweit so gut. Gibt man jetzt aber den Großbuchstaben A ein, sieht s nicht mehr so gut aus. XVERWEIS liefert den Wert für den Kleinbuchstaben a:

XVERWEIS. Stimmt leider doch nicht!

Offensichtlich sind für die Funktion ein „a“ und ein „A“ identisch und daher wird der erste Treffer ausgegeben. Wie löst man das Problem nun?

Sinnigerweise gibt es eine Excel-Funktion namens IDENTISCH. Diese prüft, ob zwei Werte wirklich exakt übereinstimmen und liefert dann entweder WAHR oder FALSCH:

Exakt geht es mit IDENTISCH

Und das führt auch zu unserer Lösung. Die Kombination von XVERWEIS und IDENTISCH liefern das korrekte Ergebnis. Allerdings sieht die Formel auf den ersten Blick etwas ungewöhnlich aus:
=XVERWEIS(WAHR;IDENTISCH(A3;E3:E16);F3:F16)

Korrekte Lösung mit XVERWEIS und IDENTISCH

Für das erste Argument wird nicht etwa der Buchstabe in Zelle A3 übergeben, sondern ein logisches WAHR. Es soll also nach dem Wert WAHR gesucht werden.

Im zweiten Argument für die Suchmatrix kommt die IDENTISCH-Funktion zum Einsatz. Erst hier wird auf Zelle A3 verwiesen und dieser Wert wird dann mit der gesamten ersten Spalte in der Referenztabelle vergleichen. Als Ergebnis wird eine Reihe von logischen FALSCH und WAHR geliefert. Man kann das gut erkennen, wenn man die IDENTISCH-Funktion in der Bearbeitungszeile komplett markiert und dann temporär über die Funktionstaste F9 berechnen lässt:

Die IDENTISCH-Funktion auflösen

IDENTISCH liefert FALSCH- und WAHR-Werte

Erst an der 8. Position wird ein WAHR geliefert, das heißt, eine exakte Übereinstimmung. Und damit wird aus der Rückgabematrix in Spalte F der 8. Wert zurückgeliefert, nämlich der korrekte Wert 101 für den Großbuchstaben „A“.

Wichtig: die temporäre Berechnung mit F9 sollte mit der ESC-Taste wieder abgebrochen werden. Ansonsten werden die FALSCH/WAHR-Wert fest in die XVERWEIS-Formel übernommen.

So sieht also die Lösung für den XVERWEIS aus. Nicht direkt naheliegend, aber auch nicht so schwer nachvollziehbar.

Es geht auch mit dem SVERWEIS

Was macht man aber, wenn man mit einer Excel-Version arbeitet, die keinen XVERWEIS kennt? Die gute Nachricht: Es geht auch mit dem SVERWEIS. Allerdings wird es hier nochmal etwas komplizierter.

Zunächst der Beweis, dass auch SVERWEIS nicht sauber arbeitet und ein falsches Ergebnis liefert:

SVERWEIS alleine funktioniert auch nicht

Der Versuch, analog zur Lösung beim XVERWEIS auf gleiche Weise die IDENTISCH-Funktion zu nutzen, scheitert leider kläglich, wie man im folgenden Bild sieht:

SVERWEIS und IDENTISCH reichen auch noch nicht

Wir brauchen also noch eine weitere Funktion, und das ist WAHL. Diese relativ unbekannte Funktion liefert aus eine Reihe von Werten den durch einen Index bestimmten Werten zurück:
=WAHL(Index;Wert1;Wert2;Wert3....)
Im folgenden Beispiel wäre das Ergebnis der Buchstabe „c“, da dies der 3. Buchstabe in der Werteliste ist:
=WAHL(3;"a";"b";"c";"d";"e")
Wenn du diese Funktion näher kennenlernen möchtest, empfehle ich dir diesen Artikel: Das Ende der Verschachtelungen: Du hast die WAHL

Und jetzt wird es etwas verzwickt, denn wir verwenden in der WAHL-Funktion jetzt eine sogenannte Matrix-Konstante, die in geschweifte Klammern gesetzt wird. In Kombination mit dem SVERWEIS bedeutet das, dass zwei Spalten zu einer zusammengefasst und dann ausgewertet werden.
=SVERWEIS(WAHR;WAHL({1.2};IDENTISCH(A3;E3:E16);F3:F16);2;FALSCH)
Wichtig:
Diese Formel muss zwingend als Array-Formel eingegeben werden, das heißt, man muss die Eingabe mit Strg+Umschalt+Enter abschließen.

Einen anderen Anwendungsfall für eine Kombination aus SVERWEIS und WAHL habe ich vor längerer Zeit im Artikel zum „Super-SVERWEIS“ beschrieben. Dort findest du auch weitere Infos zu den Matrixkonstanten, deren Wiederholung ich mir daher an dieser Stelle spare.

Das Ende vom Lied ist, dass man mit dieser Kombination auch unser heutiges Problem lösen kann und jetzt auch die Schreibweise des Suchbegriffs berücksichtigt wird:

Erst SVERWEIS, IDENTISCH und WAHL bringen die Lösung

Wem das Konstrukt aus Matrix-Konstante und Array-Formel zu abstrakt ist, der kann alternativ auch auf eine Kombination aus INDEX und VERGLEICH zurückgreifen, die ohne Array-Formel auskommt und die ich in diesem Artikel beschrieben habe:
Wie SVERWEIS, nur richtig!
 
Und wieder einmal sieht man, dass in Excel viele Wege zu einer Lösung führen können. Vielleicht kennst du ja noch eine weitere Variante? Dann lass es uns unten in den Kommentaren wissen!

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…