Gastbeitrag von Dr. Klaus Kis

Index

Wenn ein Lookup-Wert nicht in der ersten Spalte eines Bereichs liegt, muss man eine Kombination aus den Funktionen INDEX und VERGLEICH verwenden.

=INDEX(array;row_num;[col_num])

INDEX liefert einen Wert aus einem Zellbereich (array) zurück, wenn man die “Koordinaten” angibt (also die Zeilen- und Spaltennummern, row_num und col_num)

Bsp:

=INDEX(A1:E7;2;3) liefert CW zurück

(nimm denjenigen Wert des Bereiches A1 bis E7, der in der 2. Zeile und 3. Spalte liegt = CW)

Um die Telefonnummer von SCMC zu ermitteln, müsste die Formel lauten:

=INDEX(A1:E7;5;4)

VERGLEICH

Wenn ich aber nicht weiss, in welcher Zeile sich der Eintrag von SCMC befindet, muss man die Funktion VERGLEICH zu Hilfe nehmen.

=VERGLEICH(lookup_value;lookup_array;[match_type])

VERGLEICH erwartet einen Lookup-Wert (lookup_value) und einen ein-dimensionalen Zellbereich (eine Spalte, oder eine Zeile) (lookup_array). Der match_type ist optional, sollte aber auf 0 gesetzt werden, damit der nach dem exakten lookup_value gesucht wird.

Bsp:

=VERGLEICH(“SCMC”;C1:C7;0) gibt 5 zurück (SCMC wird in der 5. Zeile gefunden; die 0 in der Klammer bedeutet, dass “SCMC” genauso gesucht wird wie angegeben)

Bsp:

=VERGLEICH(“SCMC”;A5:E5;0) gibt 3 zurück (SCMC wird in der 3. Spalte gefunden; die 0 in der Klammer bedeutet, dass “SCMC” genauso gesucht wird wie angegeben)

Kombination von INDEX und VERGLEICH

Hier nochmal die Beispielsformeln von oben:

1. =INDEX(A1:E7;5;4)
2. =VERGLEICH(“SCMC”;C1:C7;0)

Die Kombination von 1. und 2. liefert die Formel:

=INDEX(A1:E7;VERGLEICH(“SCMC”;C1:C7;0);4)

und gibt als Ergebnis 3486 zurück

Es wird im Bereich A1 bis E7 der Wert aus Spalte 4 derjenigen Zeile zurückgegeben in der SCMC steht.

Erste Verallgemeinerung

Die Formel wird etwas komplizierter, wenn sich die Spaltenreihenfolge ändern könnte, wenn man also nicht genau wüsste, in welcher Spalte “Telefonnummer” zu finden ist.

Um das Problem zu lösen, verwendet man wieder VERGLEICH nach folgender Formel:

=VERGLEICH(“Telefonnummer”;A1:E1;0)

Sie liefert als Ergebnis 4 zurück, denn der Begriff Telefonnummer steht in der 4. Spalte der Tabelle.

ACHTUNG: Es ist die 4. Spalte des Tabellenbereichs und nicht die 4. Spalte des Worksheets. Wenn der Tabellenbereich nicht A1:E1, sondern C4:G4 wäre das Ergebnis immer noch 4)!

Die Kombination der INDEX- und VERGLEICH-Formeln von oben:

1. =INDEX(A1:E7;5;4)
2. =VERGLEICH(“SCMC”;C1:C7;0)
3. =VERGLEICH(“Telefonnummer”;A1:E1;0)

liefert dann die Formel:

=INDEX(A1:E7;VERGLEICH(“SCMC”;C1:C7;0);VERGLEICH(“Telefonnummer”;A1:E1;0))

Das Ergebnis dieser komplexen Formel ist nach wie vor 3486.

Zweite Verallgemeinerung

Die Spalten C1 bis C7 oder die Zeile A1 bis E1 könnten sich in der Grösse ändern, je nachdem wie gross die Tabelle gemacht wird. Da diese Spalte und Zeile direkt etwas mit dem gesamten Tabellenbereich zu tun haben ist es sinnvoll, dem gesamten Tabellenbereich einen Namen zu geben, und die Spalten- und Zeilenbereiche darauf zu beziehen.

Wenn die Tabelle von oben “” hiesse, dann wäre die obige Formel ganz einfach

=INDEX(; VERGLEICH(“SCMC”;C1:C7;0); VERGLEICH(“Telefonnummer”;A1:E1;0))

Der Bereich A1 bis E1 wäre demnach die 1. Zeile von

Der Bereich C1 bis C7 wäre die 3. Spalte von

Um die erste Zeile bzw. dritte Spalte von _MeineTabelle zu ermitteln kommt jetzt noch die Funktion BEREICH.VERSCHIEBEN hinzu.

=BEREICH.VERSCHIEBEN(reference;rows;cols;[height];[width])

BEREICH.VERSCHIEBEN verschiebt einen Zellbereich (reference) um die angegebene Zahl von Zeilen (rows) und Spalten (cols) und ändert die Grösse auf die angegeben Anzahl von Zeilen (height) und Spalten (width).

Die erste Zeile kann man folgendermassen aus der Tabelle _MeineTabelle ermitteln:

Erste Zeile = BEREICH.VERSCHIEBEN(_MeineTabelle; 0;0;1;)

Diese Formel bedeutet, dass die Tabelle _MeineTabelle um 0 Zeilen und 0 Spalten verschoben wird und auf eine Zeile verkleinert wird. Da keine Angabe für [width] gemacht wurde, behält Excel die ursprüngliche Anzahl der Spalten der Tabelle bei.

Aus der obigen Tabelle wird nun

=INDEX(_MeineTabelle;VERGLEICH(“SCMC”;C1:C7;0); MATCH(“Telefonnummer”;VERGLEICH(_MeineTabelle; 0;0;1;);0))

Die dritte Spalte wird so ermittelt:

Dritte Spalte = BEREICH.VERSCHIEBEN(_MeineTabelle;0;2;;1)

Diese Formel bedeutet, dass die Tabelle _MeineTabelle um 0 Zeilen und 2 Spalten verschoben wird. Das wäre der blaue Bereich im Beispiel unten.

Gleichzeitig wird die Tabelle auf eine Spalte verkürzt, da der letzte Parameter [width] auf 1 gesetzt ist. Da keine Angabe für [height] gemacht wurde, behält Excel die ursprüngliche Anzahl der Zeilen der Tabelle bei.

Die Formel verändert sich zu

=INDEX(_MeineTabelle; VERGLEICH(“SCMC”; BEREICH.VERSCHIEBEN(_MeineTabelle;0;2;;1);0); VERGLEICH(“Telefonnummer”; BEREICH.VERSCHIEBEN(_MeineTabelle; 0;0;1;);0))

Diese Formel liefert immer noch 3486 – die Telefonnummer von SCMC – zurück und ist nur noch abhängig vom Tabellenbereich _MeineTabelle, vom Lookup-Wert “SCMC” und der Beschriftung “Telefonnummer”.

Sie kann ganz einfach abgeändert werden, wenn man beispielsweise nach der E-Mail-Adresse sucht. Dazu muss man nur “Telefonnummer” durch “E-Mail-Adresse” ersetzen

=INDEX(_MeineTabelle; VERGLEICH(“SCMC”; BEREICH.VERSCHIEBEN(_MeineTabelle;0;2;;1);0); VERGLEICH(“E-Mail-Adresse”; BEREICH.VERSCHIEBEN(_MeineTabelle; 0;0;1;);0))

Das Ergebnis ist “michael.sp@bank.com”

Dr. Klaus Kis

Der Autor lebt in Zürich und ist Publication and Software Developer bei der Bank J. Safra Sarasin. Er hat ein PhD in Chemie von der Technischen Universität München und ein MBA von der Universität St. Gallen. Er entwickelt seit Jahren in Excel, Word, Access und anderen Microsoft Office Produkten Applikationen auf Basis VBA.