Excel suchen in matrix

Mit den Funktionen SVERWEIS() und WVERWEIS() können Siehe einen Tabellenbereich oder eine Matrix sowohl vertikal als auch horizontal nach bestimmten Kriterien durchsuchen und selbst so einen bestimmten Inhalt aus dem Tabellenbereich rückgabe lassen. Hierbei funktioniert die Suche aber immer nur in eine Richtung: horizontal oder vertikal. Je danach, 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.

Ein einfaches Beispiel, um das Funktionsweise von INDEX() zu erklären

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 Niederlassungen vertikal angeordnet sind (Z2 bis Z13).

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

Um die Form bezüglich der Koordinaten der jeweiligen Werte dynamisch an 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, das Sie in die Zelle C18 eingeben und durch das 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 das 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.

Wie ist das Funktion INDEX() aufgebaut?

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 die Sie bestimmte Daten zurückgeben wollen.
• C15 = Zeile: Zeilennummer die 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 das angegebenen Zeilen- und Spaltennummer schneiden.

Wichtig: Die Zählung die 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 das jeweiligen Überschriften stehen; im Beispiel Z1 bis Z13 und S1 bis S13 (gelbe Zellen).

INDEX() mit Bezeichnungen anstelle von Nummern einsetzen

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. Das Auswahl soll jetzt auch durch die Eingabe des Monatsnamens und nicht mehr durch die Spaltennummer erzielen. 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 weg 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 deshalb das Spaltenargument C16 in der Formel =INDEX(A1:M13;C15;C16) durch die folgende Funktion:

=VERGLEICH(C16;A1:M1;0)

Was macht die Excel-Funktion VERGLEICH()?

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

=VERGLEICH(C16;A1:M1;0)

• C16 = Suchkriterium: Ist die 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 gesuch 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 die 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.