Excel-TippMit der Funktion INDEX() Tabellenbereiche durchsuchen

Mit den Funktionen SVERWEIS() und WVERWEIS() können Sie einen Tabellenbereich oder eine Matrix sowohl vertikal als auch horizontal nach bestimmten Kriterien durchsuchen und sich so einen bestimmten Inhalt aus dem Tabellenbereich zurückgeben lassen. Hierbei funktioniert die Suche aber immer nur in eine Richtung: horizontal oder vertikal. Je nachdem, welche von den beiden Funktionen Sie gerade einsetzen.

Manchmal kommt es jedoch vor, dass Sie einen Tabellenbereich sowohl horizontal als auch vertikal nach einem bestimmten Wert durchsuchen müssen. Hierfür können Sie die Funktion INDEX() in Excel verwenden.

In der folgenden Abbildung sehen Sie eine Matrix, in der Umsatzwerte von Geschäftsfilialen pro Kalendermonat dargestellt werden. Die Monate sind hierbei horizontal (S2 bis S13), während die Filialen vertikal angeordnet sind (Z2 bis Z13).

Aus Vereinfachungsgründen werden als Zeilen- und Spaltenbeschriftung (gelbe Markierung) lediglich die jeweilige Zeilen- (Z2 = Zeile 2) und Spaltennummer (S2 = Spalte 2) verwendet. Es soll nun im ersten Beispiel der Wert der grün markierten Zelle mit der Formel INDEX() herausgelesen werden. Er befindet sich in der Schnittzelle der 4. Zeile und 3. Spalte (Z4 – S3).

Um die Formel bezüglich der Koordinaten der jeweiligen Werte dynamisch zu gestalten, sollen die jeweiligen Zeilen- und Spaltenwerte in den Zellen C15 (Zeilennummer) und C16 (Spaltennummer) erfasst werden. Dort tragen Sie also ein, in welcher Zeile und welcher Spalte sich der gesuchte Wert befindet.

Den Monatsumsatz der gewünschten Filiale (in der grünen Zelle) erhalten Sie dann mit der folgenden Formel, die Sie in die Zelle C18 eingeben und durch die Enter-Taste bestätigen:

=INDEX(A1:M13;C15;C16)

Durch die Formel wird automatisch der Umsatz aus der 4. Zeile und der 3. Spalte in die Zelle C18 übertragen. Würden Sie die Zeilen- und Spaltennummern in den Zellen C15 und C16 ändern, dann wird automatisch der jeweilige Umsatz entsprechend angepasst.

Schauen wir uns die Funktionsweise etwas genauer an, indem wir die Argumente der Funktion INDEX() betrachten:

=INDEX(A1:M13;C15;C16)

• A1:M13 = Matrix: Ist die Datentabelle, aus der Sie bestimmte Daten zurückgeben wollen.
• C15 = Zeile: Zeilennummer der Matrix, die den gesuchten Wert enthält.
• C16 = Spalte: Spaltennummer der Matrix, die den gesuchten Wert enthält.

Die Funktion INDEX() hat nun in der Matrix (A1:M13) den Wert der Schnittzelle ausgelesen, in der sich die angegebenen Zeilen- und Spaltennummer schneiden.

Wichtig: Die Zählung der Zeile und Spalte beginnt mit der ersten Zelle des Bereichs, der bei Matrix angegeben ist. Im Beispiel also bei Zelle A1. Das gilt auch dann, wenn in der ersten Zeile und Spalte die jeweiligen Überschriften stehen; im Beispiel Z1 bis Z13 und S1 bis S13 (gelbe Zellen).

Mit der Zeilen- und Spaltennummer können Sie mit der Funktion INDEX() aus einer Matrix einen bestimmten Wert herauslesen. Oft ist diese Vorgehensweise aber nicht praktikabel, da nicht bekannt ist, an welcher Position (Zeile oder Spalte) innerhalb eines Bereichs sich der gesuchte Wert befindet.

Einfacher wäre es, wenn analog zu SVERWEIS() und WVERWEIS() auch Bezeichnungen anstelle von Zeilen- und Spaltennummer verwendet werden können.

Dazu wandeln wir das Ausgangsbeispiel ab, indem wir Spaltennummer durch den jeweiligen Monatsnamen als Kürzel ersetzen. Die Auswahl soll jetzt auch durch die Eingabe des Monatsnamens und nicht mehr durch die Spaltennummer erfolgen. Im folgenden Beispiel soll der Umsatz des Monats November aus der 10. Zeile ausgelesen werden.

Da die Funktion INDEX() als Argument die Spaltennummer benötigt, braucht es eine Lösung, mit der sich die Spaltennummer aus dem Monatsnamen ergibt. Hier kommt die Funktion VERGLEICH() ins Spiel.

Mit der Funktion VERGLEICH() können Sie in einem Bereich nach einem angegebenen Element (zum Beispiel Monatsname) suchen und anschließend die relative Position als Zahl dieses Elements im Bereich zurückgegeben.

Ersetzen Sie daher das Spaltenargument C16 in der Formel =INDEX(A1:M13;C15;C16) durch die folgende Funktion:

=VERGLEICH(C16;A1:M1;0)

Schauen wir uns die Funktionsweise genauer an, indem wir die Argumente der Funktion VERGLEICH() betrachten:

=VERGLEICH(C16;A1:M1;0)

• C16 = Suchkriterium: Ist der Wert, den Sie in der Suchmatrix suchen.
• A1:M1 = Suchmatrix: Ist der Tabellenbereich, in das das Suchkriterium gesucht wird; im Beispiel ist das der Bereich für die Spaltenüberschrift mit den abgekürzten Monatsnamen (Jan etc.).
• 0 = Vergleichstyp: Der Vergleichstyp 0 bedeutet, dass der gesuchte Wert exakt in der Suchmatrix erscheinen muss.

Die Funktion =VERGLEICH(C16;A1:M1;0) sucht demnach im Bereich A1:M1 nach dem Begriff Nov (C16) und gibt die relative Position des Suchbegriffs als Zahl aus. Der Begriff Nov steht im Bereich A1:M1 an 12. Stelle. Es wird somit die Zahl 12 als Ergebnis zurückgegeben.

Das ist der Wert der Spalte, der für die INDEX()-Formel gebraucht wird. Integrieren Sie diese VERGLEICH()-Formel an Stelle des Bezugs C16 in die bisherige INDEX()-Formel. Sie können somit direkt nach Monatsnamen an Stelle von Spaltennummern die Matrix durchsuchen lassen.

Mit der gleichen Vorgehensweise können Sie auch die Zeilennummer in der INDEX()-Formel ersetzen. Ersetzen Sie das Zeilenargument (C15) durch die folgende VERGLEICH()-Funktion:

=VERGLEICH(C15;A1:A13;0)

Die modifizierte INDEX()-Formel schaut dann wie folgt aus:

=INDEX(A1:M13;VERGLEICH(C15;A1:A13;0);VERGLEICH(C16;A1:M1;0))

Durch die Integration der VERGLEICH()-Funktionen sind Sie somit komplett unabhängig von den jeweiligen Zeilen- und Spaltennummern.