INDEX und MATCH mit mehreren Arrays
Angenommen, Sie haben mehrere Tabellen mit identischen Überschriften wie unten gezeigt. Das Auffinden von Bereichen, die den vorgegebenen Kriterien entsprechen, könnte eine Herausforderung für Sie darstellen. In diesem Tutorial zeigen wir Ihnen, wie Sie einen Wert über mehrere Arrays, Bereiche oder Gruppen hinweg suchen können – und zwar mithilfe der INDEX-,MATCH-- und CHOOSE-Funktionen, indem Sie bestimmte Kriterien abgleichen.
Wie sucht man einen Wert über mehrere Arrays hinweg?
Um den Leiter verschiedener Gruppen aus unterschiedlichen Abteilungen zu ermitteln, können Sie zunächst die CHOOSE-Funktion verwenden, um die Tabelle festzulegen, aus der der Name des Leiters zurückgegeben werden soll. Anschließend ermittelt die MATCH-Funktion die Position des Leiters in der Tabelle, zu der er oder sie gehört. Schließlich ruft die INDEX-Funktion den Leiternamen anhand dieser Positionsangabe sowie der Spalte ab, in der die Namen der Leiter aufgelistet sind.
Allgemeine Syntax
=INDEX(CHOOSE())array_num,array1 ,array2 ,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Die Zahl, anhand derer CHOOSE ein Array aus der Liste array1, array2, … auswählt, um das Ergebnis daraus zurückzugeben.
- array1, array2, …: Die Arrays, aus denen das Ergebnis zurückgegeben werden soll. Hier bezieht sich dies auf die drei Tabellen.
- lookup_value: Der Wert, den die Kombinationsformel verwendet, um die Position des zugehörigen Leiters zu ermitteln. In diesem Fall bezieht er sich auf die angegebene Gruppe.
- lookup_array: Der Zellbereich, in dem der lookup_value aufgelistet ist. Hier bezieht sich dies auf den Gruppenbereich. Hinweis: Sie können den Gruppenbereich einer beliebigen Abteilung verwenden, da alle identisch sind – wir benötigen lediglich die Positionsnummer.
- column_num: Die Spalte, aus der Sie die Daten abrufen möchten.
Um den Leiter der Gruppe D aus Abteilung Azu ermitteln, kopieren oder geben Sie bitte die folgende Formel in die Zelle G5 ein und drücken Sie Enter, 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 kennzeichnen absolute Bezüge, 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 darunterliegenden Zellen anzuwenden, und passen Sie anschließend die array_numentsprechend an.
Erläuterung 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 1. Array aus den drei in der Formel aufgeführten Arrays zurück. Sie liefert daher $B$5:$C$8 – also den Datenbereich der Abteilung A.
- MATCH(F5,$B$5:$B$8,0): Der match_type 0 zwingt die MATCH-Funktion dazu, die Position des ersten Vorkommens von Gruppe D – dem Wert in Zelle F5 – im Array $B$5:$B$8 zurückzugeben, was 4 ist.
- INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2Die INDEX-Funktion ruft den Wert an der Schnittstelle der 4. Zeile und der 2. Spalte des Bereichs $B$5:$C$8 ab – also Emily.
Um zu vermeiden, dass Sie die array_numWenn Sie bei jeder Kopie der Formel Anpassungen vornehmen müssten, können Sie eine Hilfsspalte verwenden – in diesem Fall Spalte D. Die Formel sähe dann wie folgt aus:
=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,3in der Hilfsspalte kennzeichnen das array1,array2,array3innerhalb der CHOOSE-Funktion.
Verwandte Funktionen
Die Excel-INDEX-Funktion gibt den angezeigten Wert an einer vorgegebenen Position innerhalb eines Bereichs oder Arrays zurück.
Die Excel-Funktion „VERGLEICH“ sucht in einem Zellbereich nach einem bestimmten Wert und gibt dessen relative Position zurück.
Die CHOOSE-Funktion gibt basierend auf der angegebenen Indexnummer einen Wert aus der Liste der Wertargumente zurück. Beispiel: CHOOSE(3,"Apfel","Pfirsich",„Orange") liefert „Orange“ – denn die Indexnummer 3 verweist auf den dritten Wert in der Liste.
Verwandte Formeln
Suchwertbereich aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe
Wenn Sie wissen, wie die VLOOKUP-Funktion verwendet wird, um Werte innerhalb eines Arbeitsblatts zu suchen, ist das Auffinden von Werten aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe für Sie kein Problem.
VLOOKUP mit dynamischem Blattnamen
In vielen Fällen müssen Sie Daten aus mehreren Arbeitsblättern zusammenführen. Mit einer Kombination aus der VLOOKUP- und der INDIRECT-Funktion erstellen Sie eine Formel, die mithilfe eines dynamischen Blattnamens gezielt Werte über mehrere Arbeitsblätter hinweg abruft.
Suche mit mehreren Kriterien mithilfe von INDEX und MATCH
Bei der Arbeit mit einer umfangreichen Excel-Datenbank, die zahlreiche Spalten und Zeilenüberschriften enthält, ist es oft schwierig, Einträge zu finden, die mehrere Kriterien erfüllen. In solchen Fällen empfiehlt sich der Einsatz einer Matrixformel mit den Funktionen INDEX und MATCH.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.