INDEX und MATCH mit mehreren Arrays

Angenommen, Sie haben mehrere Tabellen mit denselben Überschriften wie unten gezeigt. Das Suchen von Werten, die den angegebenen Kriterien in diesen Tabellen entsprechen, kann eine schwierige Aufgabe sein. In diesem Tutorial werden wir darüber sprechen, wie man einen Wert über mehrere Arrays, Bereiche oder Gruppen hinweg durch das Abgleichen spezifischer Kriterien mit den Funktionen INDEX, MATCH und CHOOSE findet.

Wie sucht man einen Wert über mehrere Arrays?

Um die Leiter verschiedener Gruppen zu kennen, die verschiedenen Abteilungen angehören, können Sie zunächst die CHOOSE-Funktion verwenden, um die Tabelle auszuwählen, aus der der Name des Leiters zurückgegeben werden soll. Die MATCH-Funktion wird dann die Position des Leiters in der Tabelle ermitteln, zu der er/sie gehört. Schließlich wird die INDEX-Funktion den Leiter auf Basis der Positionsangaben sowie der spezifischen Spalte abrufen, in der die Namen der Leiter aufgelistet sind.

Allgemeine Syntax

=INDEX(CHOOSE(Array_Nummer,Array1,Array2,…),MATCH(Suchwert,Suchbereich,0),Spaltennummer)

Um den Leiter der Gruppe D zu kennen, die zur Abteilung A gehört, kopieren Sie bitte die folgende Formel in die Zelle G5, oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

=INDEX(CHOOSE(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ Hinweis: Die Dollarzeichen ($) oben zeigen absolute Bezüge an, was bedeutet, dass sich die Namens- und Klassenbereiche in der Formel nicht ändern, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um die Formel auf die darunter liegenden Zellen anzuwenden, und ändern Sie dann die array_num entsprechend.

Erklärung der Formel

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

• CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Die CHOOSE-Funktion gibt das erste Array aus den drei in der Formel aufgelisteten Arrays zurück. Es wird also $B$5:$C$8 zurückgegeben, d.h., der Datenbereich der Abteilung A.
• MATCH(F5,$B$5:$B$8,0): Der match_type 0 zwingt die MATCH-Funktion, die Position des ersten Treffers von Gruppe D, dem Wert in der Zelle F5, im Array $B$5:$B$8 zurückzugeben, welches 4 ist.
• INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),22) = INDEX($B$5:$C$8,4,22): Die INDEX-Funktion ruft den Wert am Schnittpunkt der 4. Zeile und 2. Spalte des Bereichs $B$5:$C$8 ab, welcher Emily ist.

Um zu vermeiden, dass Sie jedes Mal, wenn Sie die Formel kopieren, die array_num in der Formel ändern müssen, können Sie die Hilfsspalte, Spalte D, verwenden. Die Formel würde dann so aussehen:

=INDEX(CHOOSE(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ Hinweis: Die Zahlen 1, 2, 3 in der Hilfsspalte stehen für array1, array2, array3 innerhalb der CHOOSE-Funktion.

Verwandte Funktionen

Verwandte Formeln