Eine bei vielen Anwendern oft verschmähte oder wenig verstandene Funktion führt völlig zu Unrecht ein ziemliches Schattendasein in der Excel-Welt: INDEX.
Dabei bietet diese unscheinbare Funktion das Potential, selbst die universelle SVERWEIS-Funktion blass aussehen zu lassen, wenn man ihr noch einen Partner zur Seite stellt.
Das neue Dream-Team heißt daher:
- INDEX
- VERGLEICH
Und so geht’s:
Als Beispiel dient mir heute eine Tabelle mit den DAX-Werten des vergangenen Monats:
Beispieltabelle: DAX-30
Die Index-Funktion gibt aus einer Tabelle (Matrix) einen bestimmten Wert zurück, den man über eine Zeilen- und Spaltennummer auswählt:
=INDEX(Matrix; Zeile; Spalte)
Im folgenden Beispiel liefert somit die Funktion den Wert in der 10. Zeile und 3. Spalte meiner DAX-Tabelle:
Einfache Anwendung
So weit noch ziemlich unspektakulär. Seine wahre Stärke spielt die INDEX-Funktion jedoch in Kombination mit der VERGLEICH-Funktion aus. Denn hier kann man die Grenzen von SVERWEIS überwinden.
VERGLEICH
Wie wir oben gesehen haben, liefert INDEX einen bestimmten Wert zurück. Die Funktion VERGLEICH hingegen liefert stattdessen eine Position.
=VERGLEICH(Suchkriterium; Matrix; Vergleichstyp)
Bleiben wir bei meiner DAX-Tabelle. Wenn ich z.B. den höchsten Tageshöchstwert ermittlt habe, dann zeigt mir die
VERGLEICH-Funktion, in welcher Zeile sich dieser Wert befindet:
Position bestimmen mit VERGLEICH
Zur Info: Der angegebene Vergleichstyp 0 besagt, dass ich nach einer exakten Übereinstimmung suche.
Damit weiß ich zwar, dass sich der absolute Höchstwert in Zeile 7 befindet. Ich möchte mir aber stattdessen gerne das Datum anzeigen lassen. Da sich die Datumsspalte aber links von meiner Tageshoch-Spalte befindet, hilft mir die SVERWEIS-Funktion nicht weiter. Denn die kann nur Werte ermitteln, die sich rechts von der Suchspalte befinden.
Jetzt schlägt die große Stunde für unser Dream-Team INDEX und
VERGLEICH:
INDEX im Team mit VERGLEICH
Was passiert hier?
Wie wir oben gelernt haben, gibt der zweite Parameter in der INDEX-Funktion die Zeilennummer an. Diese gebe ich aber nicht fix an, sondern ermittle sie mit Hilfe der VERGLEICH-Funktion aus dem vorangegangenen Beispiel. Als dritten Parameter für die Spaltennummer gebe ich die 1 an, da sich das Datum ja in der ersten Spalte befindet. Und erhalte damit das gewünschte Ergebnis, nämlich den 02.12.2013.
Wie man sieht, lässt sich mit der Kombination aus VERGLEICH und INDEX im Gegensatz zum SVERWEIS nicht nur nach rechts, sondern auch nach links suchen!
Das könnte dich auch interessieren:
Melde Dich zum kostenlosen Newsletter an und verpasse keinen Excel-Tipp mehr!
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.
Ein Problem, das meist auf Umwegen gelöst wird:
"Prüfe, ob ein bestimmter Wert in einem Bereich vorkommt!"
Die Kombination der Funktionen ODER und IDENTISCH ist die Lösung.
Ein Beispiel:
IDENTISCH(C175;A175:A180)
gibt nur das
Ergebnis der jeweiligen Zeile zurück. Für ein korrektes Ergebnis müssten Sie sie für alle Zeilen des Referenzbereichs kopieren.
Bauen wir aber die ODER-Funktion um IDENTISCH herum, reicht ein einziges WAHR in der Matrix, um WAHR auch auszugeben:
ODER(IDENTISCH(C175;A175:A180))
Stimmt schon,
Zum Beweis verpacken wir das Ganze noch in ein elegantes WENN:
=WENN(ODER(IDENTISCH(C175;A175:A180));"Ü.";"keine Ü.")
Tipp getestet unter Excel 2007, 2010, 2013, 2016/19