Wie SVERWEIS, nur richtig! 8

SVERWEIS berücksichtigt keine Groß- und Kleinschreibung. Die hier vorgestellte Alternative schon!
 

Vor ein paar Wochen erreichte mich eine Leseranfrage zu einem interessanten Excel-Problem, dessen Existenz mir bis dahin gar nicht bewusst war (danke an dieser Stelle an Wilfried B.!)

Es ging darum, zu einer Abkürzung den dazu passenden Langtext aus einer Referenztabelle auszugeben. Das hört sich erst einmal nach einer klassischen SVERWEIS-Aufgabe an. Und jetzt kommt das große Aber….

Bei den Abkürzungen kommt es auf die exakte Schreibweise an. Das heißt, es muss zwischen Groß- und Kleinschreibung unterschieden werden. Und wie ich dann feststellen musste, kann der SVERWEIS damit leider nicht dienen.

Welche Formellösung dann doch zum gewünschten Ergebnis führt, das zeige ich dir in diesem Artikel.

Und so geht’s:

Zwei vermeintliche Lösungen mit falschem Ergebnis

Folgende einfache Beispieltabelle soll der Veranschaulichung des Problems dienen. Die Referenztabelle liegt im Zellbereich A9:B14. In Zelle A5 soll die gesuchte Abkürzung eingegeben werden und Zelle B5 soll mit Hilfe eines SVERWEIS dann den passenden Klartext anzeigen:

SVERWEIS liefert nicht das gewünschte Ergebnis

Wie im Bild unschwer zu erkennen ist, liefert SVERWEIS nicht den korrekten Wert. Anstelle „Klartext B“ (mit einem großen B) wird der darüberliegende falsche „Klartext b“ (mit einem kleinen b) ausgegeben.

Auch das Dreamteam aus INDEX und VERGLEICH kommt ebenfalls zum falschen Ergebnis, und das, obwohl im VERGLEICH der Parameter 0 für exakte Übereinstimmung verwendet wurde:

Auch INDEX und VERGLEICH sind nicht besser

Was nun?

Die echte Lösung

Das oben genannte Dreamteam führt uns schon auf die richtige Spur. Es fehlt nur noch eine Kleinigkeit. Und dafür greifen wir auf eine Excel-Funktion zurück, die ein ziemliches Schattendasein führt: IDENTISCH

Diese Funktion prüft, ob zwei angegebene Werte identisch sind. Und zwar WIRKLICH identisch, also auch im Hinblick auf die Schreibweise. Während ein normaler Vergleich über das Gleichheitszeichen unsauber arbeitet, zeigt IDENTISCH das korrekte Ergebnis:

Die IDENTISCH-Funktion für exakte Übereinstimmung


Als Ergebnis wird der Wahrheitswert WAHR bzw. FALSCH zurückgeliefert.

Kombinieren wir also INDEX, VERGLEICH und IDENTISCH:
=INDEX(B9:B14;VERGLEICH(WAHR;IDENTISCH(A5;A9:A14);0))

Kombination INDEX, VERGLEICH und IDENTISCH

Wenn du jetzt aber nicht gerade mit Office 365/Microsoft 365 arbeitest, gibt es noch ein kleines Problem. Die IDENTISCH-Funktion kann normalerweise nur zwei einzelne Werte vergleichen, nicht jedoch wie in unserem Beispiel den Wert A5 mit dem Wertebereich A9:A14. Daher gibt es einen #WERT!-Fehler:

#WERT!-Fehler bei älteren Excel-Versionen

Du kannst das Problem aber einfach lösen, indem du das Ganze als Array-Formel eingibst. Das heißt, du schließt die Formel mit der Tastenkombination Strg+Umschalt+Enter ab, was sich danach an den umschließenden geschweiften Klammern zeigt:

Eingabe als Array-Formel

Damit erhältst du auch das korrekte Ergebnis. Und falls bei dir Office 365/Microsoft 365 im Einsatz ist, kannst du die Formel auch ganz normal und ohne diese Tastenkombination eingeben. Excel wird automatisch erkennen, dass es sich um ein Array handelt und auch hier das richtige Ergebnis liefern.

Normale Eingabe reicht unter Microsoft 365

Aber was genau macht diese Formel?

Wie ich oben geschrieben habe, liefert die IDENTISCH-Funktion den Wahrheitswert WAHR zurück, falls die beiden verglichenen Werte tatsächlich übereinstimmen.
Mit der VERGLEICH-Funktion vergleichen wir nun einfach den Wert WAHR mit dem von IDENTISCH ermittelten Ergebnis und erhalten bei einer Übereinstimmung die Positionsnummer innerhalb der Referenztabelle. Und die wird dann von der INDEX-Funktion verwendet, um den gewünschten Klartext auszugeben.

Die Beispieldatei zu diesem Artikel kannst du dir bei Bedarf hier herunterladen.

Falls du also zukünftig vor einer ähnlichen Aufgabe stehst und einen exakten Abgleich unter Berücksichtigung von Groß- und Kleinschreibung benötigst, dann weißt du ja jetzt, wie’s geht 🙂
 

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.



Schreibe einen Kommentar

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

8 Gedanken zu “Wie SVERWEIS, nur richtig!

  • Gerhard Duscha

    Lieber Martin, Du hast mir und vielen EXCEL-Freunden im letzten Jahr tolle Kniffe und neue Möglichkeiten eröffnet. Vielen Dank!

    Wir alle hoffen, dass Du auch in 2021 nicht müde wirst, uns EXCEL und besonders die 365-Funktionen auf die bewährte Methode schmackhaft zumachen.
    Ganz liebe Grüße und wie sagt man: bleib negativ. Gerd

  • Sascha

    Vielen Dank für diesen Tipp – wie auch für alle anderen. Ich freue mich immer wieder sehr über den Newsletter. Danke für die damit verbundene Arbeit!

  • ergo

    Vielen herzlichen Dank für Deine grosse Arbeit. Die sauber dokumentierten und lehrreichen Tipps möchte ich nicht missen.
    Schöne Feiertage und alles Gute zum Jahreswechsel.

  • Rudolf Perkams

    Auch ich schließe mich den Wünschen der vorigen Kommentatoren an.
    Du hast schon viele tolle Tipps geliefert – mit nachvollziehbarer Beschreibung. Natürlich ist nicht immer alles gleichermaßen für mich interessant, doch Deine Bandbreite der Hinweise imponiert.
    Wünschenswert wäre es für mich zum Beispiel, dass Microsoft in Excel die Funktionsfähigkeit der Kartendiagramme irgendwie erweitert bzw. die Verwendung eigener Karten ermöglicht. Die Ebene „Postleitzahlen“ ist mir noch zu grob, wenn es darum geht, Werte für Stadtteile bzw. Wohngebiete oder gar Wahlbezirke einer Stadt grafisch darzustellen.
    Alles Gute und bleib gesund
    Rudolf Perkams

  • Michael Schulte

    Hallo Martin,
    schönen Dank für den mehr als interessanten Tipp.
    Wünsche dir, deinen Angehörigen und allen „Excel – Verrückten“ eine besinnliche Weihnachtszeit und eine guten Rutsch ins neue Jahr.

    Schöne Grüße und bleibt alle gesund

    Michael

    • Martin Weiß Autor des Beitrags

      Vielen Dank an Euch alle hier für das tolle Feedback und die lieben Weihnachtsgrüße, die gebe ich gerne so zurück!

      😀 🎅 🎄