Zweidimensionale Suchen mit INDEX-MATCH-MATCH

Stellen Sie sich ein Raster mit monatlichen Verkaufszahlen vor, in dem regions links untereinander und months oben nebeneinander stehen. Sie möchten die Zahl ermitteln, an der sich eine ausgewählte Region und ein ausgewählter Monat treffen.

Eine normale Suche findet einen Wert, indem sie nur in eine Richtung sucht. Eine zweidimensionale Suche sucht gleichzeitig in beide Richtungen: Sie findet die richtige Zeile und die richtige Spalte und gibt anschließend den Wert an ihrem Schnittpunkt zurück.

Das klassische Werkzeug dafür ist INDEX in Kombination mit zwei MATCH-Aufrufen, häufig als INDEX-MATCH-MATCH bezeichnet.

INDEX gibt anhand seiner Position einen Wert aus einem Bereich zurück. Die vollständige Form lautet INDEX(array, row_num, column_num).

Übergeben Sie der Funktion einen Zellblock, eine Zeilennummer und eine Spaltennummer. Sie gibt dann den Wert an dieser Stelle zurück. Befindet sich ein Raster beispielsweise ab B2, liefert die Abfrage nach Zeile 3 und Spalte 2 den Wert, der sich 3 Zeilen weiter unten und 2 Spalten weiter rechts innerhalb dieses Blocks befindet.

Der entscheidende Punkt: INDEX benötigt Positionen, keine Bezeichnungen. Genau diese liefert MATCH.

MATCH ermittelt die Position eines Werts innerhalb einer einzelnen Zeile oder Spalte. Die Form lautet MATCH(lookup_value, lookup_array, match_type).

Verwenden Sie 0 als Übereinstimmungstyp für eine exakte Übereinstimmung. Das Ergebnis ist eine Zahl: die Position des Werts, gezählt ab 1.

Wenn „East“ das zweite Element im Bereich A2:A5 ist, gibt MATCH den Wert 2 zurück. Diese 2 kann als Zeilennummer für INDEX dienen.

Für eine zweidimensionale Suche führen Sie MATCH zweimal aus:

• Ein MATCH-Aufruf ermittelt, in welcher Zeile sich Ihre Region befindet.
• Ein MATCH-Aufruf ermittelt, in welcher Spalte sich Ihr Monat befindet.

Übergeben Sie anschließend beide Zahlen an INDEX. Der MATCH-Aufruf für die Zeile durchsucht einen vertikalen Bereich mit Bezeichnungen, der MATCH-Aufruf für die Spalte einen horizontalen Bereich mit Überschriften.

Das Ergebnis ist die einzelne Zelle, an der sich diese Zeile und Spalte kreuzen.

Stellen Sie sich folgendes Layout vor. Die Regionsbezeichnungen stehen in A2:A5 (East, West, North, South). Die Monatsüberschriften stehen in B1:D1 (Jan, Feb, Mar). Die eigentlichen Verkaufszahlen füllen B2:D5.

Zwei Eingabezellen steuern die Suche: G1 enthält die gewünschte Region, und G2 enthält den gewünschten Monat.

Unser Ziel ist eine einzelne Formel, die G1 und G2 ausliest und die passende Verkaufszahl aus B2:D5 zurückgibt.

Ermitteln Sie zunächst die Region. MATCH durchsucht die vertikale Liste der Bezeichnungen A2:A5 nach dem in G1 eingegebenen Wert.

Wenn G1 „North“ enthält und North die dritte Bezeichnung ist, gibt dieser MATCH-Aufruf 3 zurück.

Diese Zahl teilt INDEX mit, welche Zeile des Datenblocks gelesen werden soll. Beachten Sie, dass wir A2:A5, also nur die Bezeichnungen, durchsuchen – nicht die Daten. Dadurch stimmt Position 3 mit der dritten Datenzeile überein.

Ermitteln Sie als Nächstes den Monat. Dieser MATCH-Aufruf durchsucht die horizontale Überschriftenzeile B1:D1 nach dem Wert in G2.

Wenn G2 „Feb“ enthält und Feb die zweite Überschrift ist, gibt MATCH 2 zurück.

Diese Zahl wird zur Spaltenposition für INDEX. Wie bei den Zeilen durchsuchen wir nur die Überschriften B1:D1, damit die Position mit den Datenspalten in B2:D5 übereinstimmt.

Schließen Sie nun beide MATCH-Aufrufe in INDEX ein. Der Datenblock B2:D5 ist das Array, der MATCH-Aufruf für die Zeile liefert die Zeilennummer und der MATCH-Aufruf für die Spalte die Spaltennummer.

Wenn G1 „North“ und G2 „Feb“ enthält, liefert MATCH für die Zeile 3 und MATCH für die Spalte 2. INDEX gibt daher den Wert in Zeile 3, Spalte 2 von B2:D5 zurück.

Diese einzelne Formel bildet die vollständige zweidimensionale Suche.

Angenommen, B2:D5 enthält Folgendes: In der Zeile von North stehen für Jan 50, für Feb 80 und für Mar 65.

• MATCH("North", A2:A5, 0) gibt 3 zurück.
• MATCH("Feb", B1:D1, 0) gibt 2 zurück.
• INDEX(B2:D5, 3, 2) liest Zeile 3, Spalte 2 und gibt 80 zurück.

Ändern Sie G1 in „East“ oder G2 in „Mar“, und die gesamte Formel wird sofort neu berechnet. Das ist die Stärke, INDEX mit zwei MATCH-Suchen zu steuern.

VLOOKUP durchsucht nur die erste Spalte und gibt einen Wert zurück, der sich um eine feste Anzahl von Spalten rechts davon befindet. Um zwischen Monaten zu wechseln, müssten Sie den Spaltenindex selbst fest codieren oder berechnen.

Mit INDEX-MATCH-MATCH können sowohl die Zeile als auch die Spalte dynamisch anhand einer Bezeichnung ausgewählt werden. Sie können Spalten neu anordnen oder neue Monate einfügen, und die Formel funktioniert weiterhin, weil sie den Text der Überschrift abgleicht und nicht eine feste Anzahl verwendet.

Der häufigste Fehler sind nicht übereinstimmende Bereiche. Der Bereich für den Zeilen-MATCH muss dieselbe Höhe wie der INDEX-Datenblock haben, und der Bereich für den Spalten-MATCH muss dieselbe Breite haben.

A2:A5 umfasst hier 4 Zeilen, und B2:D5 ebenfalls 4 Zeilen. Daher bedeutet ein MATCH-Ergebnis von 3 tatsächlich die dritte Datenzeile. Wenn Sie versehentlich A1:A5 durchsuchen, das eine Überschrift enthält, verschieben sich die Positionen um eins, und Sie erhalten die falsche Zelle.

Sie haben das Muster für eine zweidimensionale Suche kennengelernt:

• INDEX gibt einen Wert an einer Zeilen- und Spaltenposition innerhalb eines Blocks zurück.
• Ein MATCH findet die Zeile, indem es vertikale Bezeichnungen durchsucht.
• Ein zweites MATCH findet die Spalte, indem es horizontale Überschriften durchsucht.

Die kombinierte Formel =INDEX(data, MATCH(row), MATCH(col)) liest zwei Eingaben und gibt den Wert an deren Schnittpunkt zurück. Achten Sie darauf, dass die MATCH-Bereiche dieselbe Größe wie der Datenblock haben, um Fehlzuordnungen zu vermeiden.