INDEX / VERGLEICH – Excel und Google Sheets

In diesem Tutorial lernen Sie, wie Sie die Kombination INDEX & MATCH verwenden, um Suchabfragen in Excel und Google Sheets durchzuführen.

INDEX & MATCH, Das perfekte Paar

Schauen wir uns einige der Möglichkeiten an, wie Sie die Funktionen INDEX und VERGLEICH kombinieren können. Die VERGLEICH-Funktion wurde entwickelt, um die relative Position eines Elements innerhalb eines Arrays zurückzugeben, während die INDEX-Funktion ein Element aus einem Array mit einer bestimmten Position abrufen kann. Diese Synergie zwischen den beiden Funktionen ermöglicht es, fast alle Arten von Suchvorgängen durchzuführen.

Die Kombination INDEX / VERGLEICH wurde in der Vergangenheit als Ersatz für die SVERWEIS-Funktion verwendet. Einer der Hauptgründe dafür ist die Möglichkeit, eine linksgerichtete Suche durchzuführen (siehe nächsten Abschnitt).

Hinweis: Die neue XVERWEIS-Funktion kann mittlerweile linksgerichtete Suchvorgänge durchführen.

Nach links nachschlagen

Verwenden wir diese Tabelle mit Basketball-Statistiken:

Wir wollen die Spielernummer von Bob finden. Da die Spielernummer links von der Namensspalte steht, können wir keinen SVERWEIS verwenden.

Stattdessen können wir eine einfache VERGLEICH-Abfrage durchführen, um Bobs Zeile zu berechnen:

=VERGLEICH(H2; B2:B5; 0)

Damit wird nach einer exakten Übereinstimmung mit dem Wort „Bob“ gesucht und unsere Funktion würde die Zahl 2 zurückgeben, da „Bob“ an der zweiten Stelle steht.

Als Nächstes können wir die INDEX-Funktion verwenden, um die Nummer des Spielers zurückzugeben, die einer Zeile entspricht. Für den Moment geben wir einfach manuell „2“ in die Funktion ein:

=INDEX(A2:A5; 2)

Hier verweist INDEX auf A3, da dies die zweite Zelle im Bereich A2:A5 ist, und gibt das Ergebnis 42 zurück. Für unser übergeordnetes Ziel können wir diese beiden Funktionen kombinieren:

=INDEX(A2:A5; VERGLEICH(H2; B2:B5; 0))

Der Vorteil hierbei ist die Tatsache, dass wir ein Ergebnis aus einer Spalte links von der gesuchten Stelle zurückgeben können.

Zweidimensionale Suche

Schauen wir uns unsere Tabelle von vorher an:

 

Diesmal wollen wir jedoch eine bestimmte Statistik abrufen. Wir haben angegeben, dass wir nach Rebounds in Zelle H1 suchen wollen. Anstatt mehrere WENN-Anweisungen zu schreiben, um zu bestimmen, aus welcher Spalte das Ergebnis geholt werden soll, können Sie wieder eine VERGLEICH-Funktion verwenden. Mit der INDEX-Funktion können Sie den Zeilenwert und den Spaltenwert angeben . Wir fügen hier eine weitere VERGLEICH-Funktion hinzu, um zu bestimmen, welche Spalte wir wollen. Das sieht dann folgendermaßen aus:

=VERGLEICH(H1; A1:E1; 0)

Unsere Zelle in H1 ist ein Dropdown-Feld, in dem wir die Kategorie auswählen können, nach der wir suchen wollen, und unsere VERGLEICH-Funktion bestimmt dann, zu welcher Spalte in der Tabelle das gehört. Fügen wir nun dieses neue Element in unsere vorherige Formel ein. Beachten Sie, dass wir das erste Argument so ändern müssen, dass es zweidimensional ist, da wir mittlerweile nicht nur ein Ergebnis aus Spalte A wollen.

=INDEX(A2:E5; VERGLEICH(H2; B2:B5; 0); VERGLEICH(H1; A1:E1; 0))

In unserem Beispiel wollen wir Rebounds für Charlie finden. Unsere Formel wird dies folgendermaßen auswerten:

=INDEX(A2:E5; VERGLEICH("Charlie"; B2:B5; 0); VERGLEICH("Rebounds"; A1:E1; 0))
=INDEX(A2:E5; 3; 4)
=D4
=6

Wir haben nun einen flexiblen Aufbau erzielt, mit dem der Benutzer jeden beliebigen Wert aus unserer Tabelle abrufen kann, ohne mehrere Formeln oder verzweigte WENN-Anweisungen schreiben zu müssen.

Mehrere Abschnitte

Es wird nicht oft verwendet, aber INDEX hat ein fünftes Argument, das angegeben werden kann, um zu bestimmen, welcher Bereich innerhalb des ersten Arguments verwendet werden soll. Das bedeutet, dass wir eine Möglichkeit brauchen, um mehrere Bereiche in das erste Argument zu übertragen. Das kann man mit einem zusätzlichen Klammersatz erreichen. Dieses Beispiel veranschaulicht, wie Sie Ergebnisse aus verschiedenen Tabellen auf einem Arbeitsblatt mit INDEX abrufen können.

Hier ist das Layout, das wir verwenden werden. Wir haben Statistiken für drei verschiedene Quartale des Spiels.

In den Zellen H1:H3 haben wir Dropdown-Listen (Datenüberprüfung) für unsere verschiedenen Auswahlmöglichkeiten erstellt. Die Dropdown-Liste für das Quartal kommt aus J2:J4. Wir werden dies für eine weitere VERGLEICH-Anweisung verwenden, um den zu verwendenden Bereich zu bestimmen. Unsere Formel in H4 wird folgendermaßen aussehen:

=INDEX((A3:E6; A10:E13; A17:E20); VERGLEICH(H2; B3:B6; 0); VERGLEICH(H1; A2:E2; 0); VERGLEICH(H3; J2:J4; 0))

Wir haben bereits besprochen, wie die beiden inneren VERGLEICH-Funktionen funktionieren, also konzentrieren wir uns auf das erste und das letzte Argument:

=INDEX((A3:E6; A10:E13; A17:E20); ...; VERGLEICH(H3; J2:J4; 0))

Wir haben der INDEX-Funktion mehrere Arrays im ersten Argument gegeben, indem wir sie alle in Klammern gesetzt haben. Eine andere Möglichkeit, dies zu tun, ist die Verwendung von Formeln bzw. Definieren von Namen. Sie könnten einen Namen mit der Bezeichnung „MyTable“ und der folgenden Definition definieren:

=’Mehrere Abschnitte‘!$A$3:$E$6;’Mehrere Abschnitte‘!$A$10:$E$13;’Mehrere Abschnitte‘!$A$17:$E$20)

Aud diese Weise ergibt sich die folgende Formel:

=INDEX(MyTable;VERGLEICH(H2;Table134[Name];0);VERGLEICH(H1;Table134[#Kopfzeilen];0);VERGLEICH(H3;J2:J4;0))

Gehen wir noch einmal auf die gesamte Anweisung zurück. Unsere verschiedenen VERGLEICH-Funktionen werden der INDEX-Funktion genau sagen, wo sie suchen soll. Zunächst legen wir fest, dass „Charlie“ die 3. Zeile ist. Als nächstes wollen wir „Rebounds“, was der 4. Spalte entspricht. Schließlich haben wir festgelegt, dass wir das Ergebnis aus der 2. Tabelle haben wollen. Die Formel wird dies folgendermaßen auswerten:

=INDEX((A3:E6; A10:E13; A17:E20); VERGLEICH(H2; B3:B6; 0); VERGLEICH(H1; A2:E2; 0); VERGLEICH(H3; J2:J4; 0))
=INDEX((A3:E6, A10:E13, A17:E20); 3; 4; 2)
=INDEX(A10:E13; 3; 4)
=D13
=14

Wie zu Beginn dieses Beispiels erwähnt, sind Sie darauf beschränkt, dass sich die Tabellen auf demselben Arbeitsblatt befinden müssen. Wenn Sie Ihrem INDEX korrekt mitteilen können, aus welcher Zeile, Spalte und/oder welchem Bereich Sie Daten abrufen möchten, wird Ihnen INDEX sehr gute Dienste leisten.

Google Sheets – INDEX & VERGLEICH

Alle oben genannten Beispiele funktionieren in Google Sheets genau so wie in Excel.