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 🙂
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.
Danke für den Tipp. Das war mir bisher noch nicht aufgefallen. Gut zu wissen.
Hallo Gerhard,
ja, das war für mich auch neu. Man lernt halt nie aus 🙂
Schöne Grüße,
Martin
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
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!
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.
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
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
Vielen Dank an Euch alle hier für das tolle Feedback und die lieben Weihnachtsgrüße, die gebe ich gerne so zurück!
? ? ?
Guten Tag,
großes Lob für die superklasse Website.
Ich meine alle Dinge mit Index und Vergleich etc. gelesen zu haben und komme trotzdem nicht zur Lösung meines Problems.
Ich möchte einen Wert in zwei Spalten A und B suchen, wird der Wert gefunden, soll der Wert aus der Spalte D der gleichen Zeile ausgegeben werden.
Kann mir jemand dabei Helfen?
MfG
DvB
Hallo Dominik,
dankeschön für dein Feedback.
Du kannst einfach die INDEX-Funktion mit Hilfe der WENNFEHLER-Funktion zweimal ausführen. Also sinngemäß etwa so:
=WENNFEHLER(INDEX(durchsuche Spalte A);INDEX(durchsuche Spalte B))
Wenn die erste INDEX-Funktion auf einen Fehler läuft, also nichts findet, wird die zweite INDEX-Funktion ausgeführt.
Schöne Grüße,
Martin
hallo,
ab XL2010 geht auch ohne Matrix-Eingabe
=INDEX(B9:B14;AGGREGAT(15;6;ZEILE(1:6)/IDENTISCH(A5;A9:A14);1))
Gruß
Udo
Hallo Udo,
vielen Dank für diese Lösung. Ein sehr kreativer Einsatz der so selten verwendeten AGGREGAT-Funktion. Bin begeistert!
Schöne Grüße,
Martin
Hallo Martin,
Mit Begeisterung lese ich schon seit langem immer wieder deinen Blog. Vielen Dank für meiner Ansicht nach sehr gute didaktische Aufbereitung von teils komplexen Themen.
Heute bin ich auf ein Problem gestoßen, welches mir so noch nicht untergekommen ist. Vielleicht hast du ja eine Antwort darauf und kannst es erklären?!
Ich habe zwei Listen. Eine mit 39k+ Zeilen (Liste A), eine mit 86k+ Zeilen (Liste B). Ich möchte die Werte aus der einen Liste in der anderen Liste wiederfinden und verwende den SVERWEIS dazu. Suche ich jetzt mit dem Wert von Liste A in Liste B, erhalte ich 536 Treffer. Suche ich aber mit dem Wert von Liste B in Liste A, erhalte ich nur 533 Treffer. Bei zwei anderen Listen ist es noch eklatanter (1285 Treffer zu 3990 Treffer). Wie kann das sein?
Die Klassiker wie falsches Format, keine exakte Suchübereinstimmung, doppelte Werte, usw. kannst du alle ausschließen. Ich weiß auch, dass man auch andere Formeln dafür verwenden könnte. Mir geht es aber um diese Differenz, die mich erstmal glaube lässt, dass SVERWEIS ein mir unbekanntes Problem hat. Zu anderen, bekannten Problemen hast du ja schon diverse Blogeinträge geschrieben (wie z.B. dieser hier oder die Probleme mit rechts/links, Mehrfachwerte, usw.). Vielleicht kannst du ja zu meinem Problem auch mal was schreiben, wenn du es nachvollziehen kannst.
Hallo Horst,
est einmal vielen Dank für das tolle Feedback zu meinem Blog – und Entschuldigung für die späte Reaktion von mir.
Wie ich deinen Ausführungen entnehme, hast du die offensichtlichen potentiellen SVERWEIS-Fallen schon ausgeschlossen. Meine Frage wäre daher:
Vielleicht ist das Ergebnis ja kein Fehler, sondern entspricht der Realität? Es kann ja durchaus sein, dass der SVERWEIS aus beiden Tabellen heraus unterschiedliche Ergebnisse liefert. Nämlich dann, wenn einfach nicht alle Werte in beiden Listen enthalten sind. Die absoluten Trefferanzahlen 533 und 536 bedeuten ja nicht zwingend, dass es sich immer um die gleichen Treffer handeln muss. Hast du dir die beiden Trefferlisten schon mal näher angesehen? Möglicherweise sind die vermeintlich fehlenden Werte ja tatsächlich nicht in der anderen Liste enthalten.
Schöne Grüße,
Martin