XVERWEIS für Groß- und Kleinschreibung 6

Artikelbild-308-gross
"A" ist nicht gleich "a". Oder: Wie man Schreibweisen beim Einsatz von XVERWEIS berücksichtigen kann
 

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
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...

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!

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

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

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

Die IDENTISCH-Funktion auflösen

IDENTISCH liefert FALSCH- und WAHR-Werte

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

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

SVERWEIS und IDENTISCH reichen auch noch nicht


Das liegt daran, dass man beim SVERWEIS im zweiten Argument die komplette Matrix angeben muss, und nicht nur die erste Spalte. Denn die Rückgabematrix wird dann ja über den Spaltenindex ausgewählt.

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

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!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Eloar Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

6 Gedanken zu “XVERWEIS für Groß- und Kleinschreibung

  • Avatar-Foto
    Michael Koulen

    Hallo Herr Weiß
    Dies ist sicherlich ein spezieller Anwendung. Super das es auch dafür eine Losung gibt.

    Ebenfalls interessant erscheint mir der XVERWEIS oder SVERWEIS mit 2 oder mehreren Suchkriterien.
    =SVERWEIS(E5&F5;WAHL({1.2};A:A&B:B;C:C);2;0)

    Dies müsste eigentlich auch mit den XVERWEIS möglich sein.

    Mit den besten Grüssen

    • Avatar-Foto
      Martin Weiß

      Hallo Herr Koulen,

      ja, bei mehreren Suchkriterien hilft beim SVERWEIS die WAHL-Funktion. Beim XVERWEIS geht das sogar ohne WAHL, man kann einfach die Spalten für die Suchmatrix kombinieren. Beispiel:
      =XVERWEIS(E5&F5;A:A&B:B;C:C)

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Hess Reinhard

    Hallo Martin,
    ein kleiner Hinweis für alle, die ihr System auf Dezimalpunkt statt Dezimalkomma eingestellt haben:
    Der Index in der WAHL-Funktion muß dann folgendermaßen geschrieben werden: {1\2}
    Schöne Grüße
    Reinhard

  • Avatar-Foto
    Eloar

    Hallo Herr Weiß,

    vielen Dank für diese tolle Anwendung.

    Habe auch schon überlegt, ob eine Umwandlung in ASCII Sinnig wäre, jedoch müsste man dann mit einer Hilfsspalte die Bedeutung hinterlegen. Das würde das ganze etwas aufwändiger gestalten als überhaupt notwendig 😉

    Ich habe dazu noch eine (allgemeine) Frage:
    Und zwar möchte ich mir Jahresübergreifend Daten anzeigen lassen.

    Heißt:
    Ich habe je ein Dropdown um das Startjahr und den Startmonat auszuwählen.
    Wenn ich z.B. also 2022 und Februar auswähle, möchte ich (in mehreren Zellen darunter) folgendes Ergebnis haben:
    Februar
    Januar
    Dezember
    November
    Oktober
    September
    August
    Juli
    Juni
    Mai
    April
    März

    Das klappt bisher auch schon.
    Was jedoch nicht funktioniert, ist, dass ich die Daten Jahresübergreifend (Februar und Januar aus 2022, den Rest aus 2021) angezeigt bekomme.
    Sprich, die den Jahren und Monaten zugeordneten Produktionszahlen.

    Ich hoffe sehr dass jemand eine Idee hat.

    Liebe Grüße und schon einmal vielen Dank

    • Avatar-Foto
      Martin Weiß

      Hallo Eloar,

      so sollte es klappen: Die Quelle für das Monatsdropdown sollte eine Liste mit Datumswerten sein (im Screenshot in Spalte F). Wenn du sie mit dem Zahlenformat MMMM formatierst, werden sie dann nur als Monat angezeigt (das Jahr spielt dabei übrigens keine Rolle).
      Dann kannst du für die Monatsliste mit zwei Formeln arbeiten. Die erste bestimmt das Startdatum, alle weiteren berechnen über die MONATSENDE-Funktion den Vormonat. Auch hier die Ausgabe wieder mit Zahlenformat MMMM formatierten.

      Schöne Grüße,
      Martin