Die Excel INDEX-Funktion ist ein unverzichtbares Werkzeug für jeden, der mit großen Datensätzen arbeitet und spezifische Informationen schnell und präzise abrufen möchte. Sie ermöglicht es Ihnen, einen Wert basierend auf seiner Position in einem definierten Zellbereich zu ermitteln. Stellen Sie sich vor, Sie spielen eine Partie excel schiffe versenken – Sie geben Koordinaten ein (Zeile und Spalte), und Excel liefert Ihnen den Wert, der sich an genau dieser Schnittstelle befindet. Diese Funktion ist nicht nur für sich allein leistungsstark, sondern entfaltet ihr volles Potenzial insbesondere in Kombination mit anderen Excel-Funktionen wie VERGLEICH und INDIREKT, um hochdynamische und flexible Datenabfragen zu realisieren.
In diesem umfassenden Leitfaden werden wir die INDEX-Funktion detailliert beleuchten. Wir beginnen mit ihren Grundlagen, erklären ihre Syntax und zeigen praktische Anwendungsbeispiele. Anschließend tauchen wir in die Welt der Funktionskombinationen ein: Zuerst erfahren Sie, wie INDEX und VERGLEICH zusammenarbeiten, um statische Suchen zu überwinden und positionsunabhängig Werte zu finden. Danach widmen wir uns der INDIREKT-Funktion, die es Ihnen erlaubt, Zellbezüge dynamisch zu gestalten. Abschließend zeigen wir Ihnen die Königsdisziplin – die Kombination aller drei Funktionen (INDEX, VERGLEICH und INDIREKT), um Daten aus variablen Bereichen und sogar unterschiedlichen Tabellenblättern abzurufen. Unser Ziel ist es, Ihnen ein tiefes Verständnis dieser essenziellen Excel-Werkzeuge zu vermitteln, damit Sie Ihre Datenanalyse auf ein neues Level heben können.
Die Grundlagen der INDEX-Funktion in Excel
Die INDEX-Funktion ist ein Eckpfeiler für viele fortgeschrittene Excel-Anwendungen und bietet eine direkte Methode, um Werte aus einem bestimmten Bereich zu extrahieren. Ihr Prinzip ist einfach: Sie definieren einen Bereich und geben dann die Zeilen- und Spaltennummer innerhalb dieses Bereichs an, um den gewünschten Wert zu erhalten.
Die Syntax der INDEX-Funktion lautet: =INDEX(Matrix; Zeilen_Nr; [Spalten_Nr]).
- Matrix: Der Zellbereich, in dem Sie suchen möchten.
- Zeilen_Nr: Die Zeilennummer innerhalb der Matrix, aus der Sie einen Wert zurückgeben möchten.
- Spalten_Nr: (Optional) Die Spaltennummer innerhalb der Matrix, aus der Sie einen Wert zurückgeben möchten. Wenn die Matrix nur eine Zeile oder eine Spalte ist, ist
Spalten_Nrnicht erforderlich.
Nehmen wir an, Sie haben einen Datenbereich von A1 bis E5. Wenn Sie den Wert in der Zelle Zeile 2 / Spalte 5 (innerhalb dieses Bereichs) ermitteln möchten, würden Sie die INDEX-Funktion entsprechend konfigurieren.
Zuerst geben Sie die gewünschten Suchparameter ein, beispielsweise den Wert 2 für die Zeile und den Wert 5 für die Spalte in separate Zellen (z.B. B8 und B9).
Eingabe der gewünschten Zeilen- und Spaltennummern in separaten Zellen für die INDEX-Funktion
Für die Eingabe der INDEX-Formel wählen Sie als Erstes den Bereich aus (z.B. A1:E5), innerhalb dessen Sie suchen möchten. Danach referenzieren Sie die Zellen (B8 und B9), in die Sie zuvor die beiden Zahlen/Suchparameter für die gewünschte Zeile und Spalte geschrieben haben.
Excel-Formel mit der INDEX-Funktion, die auf einen Zellbereich und separate Suchparameter-Zellen verweist
Als Ergebnis erhalten Sie in unserem Beispiel den Wert 20 zurück, da dieser Wert in der Zelle an der Schnittstelle von Zeile 2 und Spalte 5 (innerhalb des definierten Bereichs) steht.
Ergebnis der INDEX-Funktion, das den Wert an der Schnittstelle der angegebenen Zeilen- und Spaltenkoordinaten zeigt
Wenn Sie die Suchparameter für Zeile und Spalte ändern, werden Ihnen die entsprechend anderen Werte angezeigt. Ein besonderer Fall tritt ein, wenn Sie für die Zeile oder die Spalte den Wert 1 eingeben: Dann wird Ihnen die entsprechende Beschriftung (Kopfzeile oder Spaltenbeschriftung) angezeigt, da sich diese Titel ebenfalls innerhalb des von Ihnen ausgewählten Bereichs befinden. Die INDEX-Funktion ist somit ein flexibles Werkzeug, um genau auf die Datenpunkte zuzugreifen, die Sie benötigen.
Anzeige der Beschriftung, wenn die Zeilen- oder Spaltennummer 1 als Suchparameter in der INDEX-Funktion verwendet wird
Beispiel, wie die INDEX-Funktion verschiedene Werte basierend auf angepassten Suchparametern zurückgibt
Die INDEX-Funktion lässt sich für vielfältige Aufgaben effektiv einsetzen und wird insbesondere in Kombination mit der VERGLEICH-Funktion zu einem außerordentlich mächtigen Werkzeug. Diese Kombination erlaubt es, die statische Eingabe von Zeilen- und Spaltennummern zu überwinden und stattdessen dynamisch nach Kriterien zu suchen.
INDEX und VERGLEICH: Die mächtige Kombination für dynamische Suchen
Die INDEX-Funktion ist ein starkes Werkzeug, das einen Wert basierend auf seiner Position in einem Bereich zurückgibt. In vielen praktischen Szenarien wissen wir jedoch nicht die exakte Zeilen- oder Spaltennummer des gesuchten Wertes. Hier kommt die VERGLEICH-Funktion ins Spiel, die die Position eines Elements in einem Bereich zurückgibt. Die Kombination aus INDEX und VERGLEICH bildet eine der flexibelsten und leistungsfähigsten Formeln in Excel und kann in vielen Fällen die SVERWEIS-Funktion ersetzen oder sogar übertreffen, da sie weniger Einschränkungen hinsichtlich der Suchrichtung hat.
Zur Erinnerung: Mithilfe der INDEX-Funktion ermitteln Sie einen Wert, abhängig von dessen Position innerhalb eines zuvor ausgewählten Bereichs. Das heißt, Sie haben eine Tabelle oder einen Datenbereich und müssen durch Angabe der gewünschten Zeile und Spalte eine bestimmte Position definieren – also quasi eine Koordinate eingeben –, um jenen Wert zu erhalten, der sich in der Zelle im Schnittpunkt dieser beiden Parameter befindet.
In der Praxis ist diese Herangehensweise oftmals nicht sehr sinnvoll, denn in vielen Fällen wissen Sie nicht, an welcher Position (Zeile bzw. Spalte oder beides) innerhalb eines Bereichs sich der gesuchte Wert genau befindet. In unserem Beispiel finden Sie nur drei Zeilen mit Werten und vier Spalten mit Produktkriterien.
Kleine Beispieltabelle mit drei Zeilen und vier Spalten Produktkriterien, die die Einschränkungen der reinen INDEX-Funktion verdeutlicht
Würden Sie aus einer Tabelle oder einem Datenbereich mit mehreren Dutzend oder sogar Hunderten oder gar Tausenden Zeilen bzw. Spalten einen ganz bestimmten Wert heraussuchen müssen, würde es natürlich wenig Sinn machen und wäre darüber hinaus viel zu zeitaufwendig, immer wieder sämtliche Spalten- und/oder Zeilenköpfe durchzugehen, bis Sie die Position des gesuchten Werts bzw. die Positionen mehrerer gesuchter Werte gefunden haben.
In unserem Beispiel suchen wir nun in der Tabelle jenen Wert, der für das Produkt A in der Spalte mit dem Produktkriterium „grün“ eingetragen ist.
Suchtabelle, die den gewünschten Wert für Produkt A und das Kriterium "grün" hervorhebt
Würde man nur mit der INDEX-Funktion allein arbeiten, müsste man zunächst alle Zeilen und Spalten durchzählen, um zu erfahren, in welcher Zeile sich Produkt A und in welcher Spalte sich das Produktkriterium „grün“ befindet. Dieses Zählen wird mithilfe der VERGLEICH-Funktion umgangen. Die VERGLEICH-Funktion sucht nach einem angegebenen Element in einem Bereich von Zellen und gibt die relative Position dieses Elements zurück. Dies ist besonders nützlich, um dynamisch die Zeilen- oder Spaltennummer für die INDEX-Funktion zu ermitteln. Um Daten aus zwei Listen zu vergleichen, können Sie auch diesen Beitrag lesen: excel 2 listen vergleichen.
Für die Erstellung der Formel geben Sie zunächst den Bereich (A1:D4) ein, in dem sich der gesuchte Wert befindet. Im nächsten Schritt müssten Sie nun eigentlich die betreffende Zeilennummer bzw. die entsprechende Spaltennummer eingeben. Wir wollen aber nicht mehr die Zeilen und die Spalten durchzählen, sondern die Produktangabe A sowie das Produktkriterium “grün” verwenden – also Excel selbst vergleichen bzw. abzählen lassen, in welcher Zeile bzw. Spalte sich der jeweilige Suchparameter befindet.
Zu diesem Zweck erweitern Sie die Formel durch die VERGLEICH-Funktion. In der jeweiligen Klammer geben Sie zunächst die Zelle mit dem gesuchten Parameter („A“ = B6 | „grün“ = B8) ein, danach wählen Sie den dazugehörigen Suchbereich aus, also alle Zeilenköpfe (A1:A4) bzw. alle Spaltenköpfe (A1:D1), innerhalb deren Excel die gesuchten Werte vergleichen muss, und geben am Ende der Klammer den Wert 0 ein, weil ja eine genaue Übereinstimmung gegeben sein muss.
Die vollständige Formel sieht dann so aus: =INDEX(A1:D4;VERGLEICH(B6;A1:A4;0);VERGLEICH(B8;A1:D1;0)).
Die kombinierte INDEX- und VERGLEICH-Formel zur dynamischen Suche nach Werten basierend auf Suchparametern in separaten Zellen
Als Ergebnis bekommen Sie den Wert 20, weil sich dieser in der Zelle an der Schnittstelle von Zeile 2 (Produkt A) und Spalte D (Produktkriterium grün) befindet. Dies zeigt die enorme Flexibilität dieser Kombination.
Ergebnis der kombinierten INDEX- und VERGLEICH-Funktion, die den korrekten Wert aus der Tabelle dynamisch abruft
Die Kombination der INDEX-Funktion mit der VERGLEICH-Funktion ersetzt in vielen Fällen die SVERWEIS-Funktion, da sie im Gegensatz zu SVERWEIS nicht an eine feste Suchrichtung gebunden ist und auch Spalten links vom Suchkriterium zurückgeben kann. Sie ist eine leistungsstarke Alternative für komplexe Datenabfragen und bietet deutlich mehr Flexibilität.
Die INDIREKT-Funktion verstehen: Flexible Zellbezüge meistern
Die INDIREKT-Funktion in Excel ist ein mächtiges, aber oft missverstandenes Werkzeug. Ihr Name kann anfangs verwirrend sein, da sie nicht direkt auf eine Zelle verweist, sondern den Zellbezug aus einem Textstring generiert. Vereinfacht ausgedrückt, erlaubt INDIREKT Ihnen, einen Bezug zu einer Zelle oder einem Bereich zu erstellen, dessen Adresse nicht fest in der Formel verankert ist, sondern dynamisch aus dem Inhalt einer anderen Zelle oder einem berechneten Text abgeleitet wird.
Üblicherweise werden zwischen zwei Zellen (oder zwischen einer Zelle und einem größeren Zellbereich) normale, direkte Bezüge hergestellt, wie z.B. =A1. Mit der INDIREKT-Funktion nehmen Sie ebenfalls Bezug auf eine bestimmte Zelle, allerdings fungiert hier eine dritte Zelle quasi als Relais-Station. Sie können sich das Ganze wie eine Fernsehübertragung zwischen zwei Kontinenten über einen TV-Satelliten vorstellen:
- Zelle A1 (Wert 1000) ist der Übertragungswagen eines Fernsehsenders und befindet sich in Washington D.C., von wo aus ein Reporter über eine Demonstration vor dem Weißen Haus berichtet.
- Zelle C5 ist der Fernsehapparat in einem Wohnzimmer in Europa.
- Der graue Bereich A4:C4 stellt den Atlantischen Ozean dar, über den aufgrund der Erdkrümmung keine direkte Funkverbindung von A1 nach C5 möglich ist.
Wir brauchen also, wenn wir die Fernsehübertragung von der Demonstration vor dem Weißen Haus (Zelle A1) auf unserem Fernsehapparat in Europa (Zelle C5) live verfolgen wollen, eine Relais-Station in Gestalt eines TV-Satelliten (Zelle C1), der sich in einer geostationären Umlaufbahn um die Erde befindet und eine ständige direkte Verbindung sowohl zu A1 als auch zu C5 besitzt.
Der TV-Satellit ist auf die Frequenz der Zelle A1 eingestellt, daher schreiben wir in die Zelle C1 den Wert „A1“. Von der Zelle C5 aus nehmen wir nun auf dem Umweg über die Zelle C1 einen INDIREKTEN Bezug auf die Zelle A1.
Analogie zur INDIREKT-Funktion: Eine dritte Zelle (C1) dient als Relaisstation, um einen indirekten Bezug von C5 zu A1 herzustellen
Wie beschrieben, wird hier also kein direkter Bezug zwischen C5 und A1 hergestellt, sondern ein INDIREKTER „Bezug aus einem Text(feld)“. Als Ergebnis wird uns dann der Text/Wert, der in der Zelle A1 steht, ausgeworfen.
Anstatt also in die Zelle C5 den (direkten) Bezug zur Zelle A1 als (direkte) Formel (=A1) hineinzuschreiben, holen wir uns diesen Text/Wert auf dem Umweg über eine andere Zelle (in unserem Fall über C1). Der Text bzw. der Wert, den sich die Zelle C1 aus der Zelle A1 geholt hat, fließt somit über die Formel =INDIREKT(C1) von A1 über C1 in die Zelle C5 ein.
Demonstration der INDIREKT-Funktion, die den Wert aus Zelle A1 über den Textbezug in Zelle C1 in Zelle C5 anzeigt
Ein praktisches Beispiel für die INDIREKT-Funktion ist die Möglichkeit, Dropdown-Listen dynamisch zu gestalten oder auf Zellbereiche in verschiedenen Tabellenblättern zu verweisen, deren Namen in einer Zelle gespeichert sind. Dies eröffnet enorme Möglichkeiten für die Erstellung flexibler und wartungsfreundlicher Excel-Modelle.
Abwandlung der INDIREKT-Funktion
Für Bezüge können Sie nicht nur die Standard-Schreibweise (A1, B5), sondern auch die „ZS“- bzw. „Zeilen-Spalten“-Schreibweise (Z1S1) einsetzen. Der Eintrag Z1S1 (1. Zeile, 1. Spalte) in Zelle B11 entspricht in unserem Fall somit der Zelle „A1“.
Damit jedoch die INDIREKT-Formel diese Schreibweise auch als Bezug interpretieren kann, muss die Formel noch durch den Zusatz FALSCH ergänzt werden. Die Syntax wäre dann =INDIREKT(B11;FALSCH).
Verwendung der INDIREKT-Funktion mit der ZS-Schreibweise und dem optionalen Argument FALSCH, um den Bezug korrekt zu interpretieren
Diese Flexibilität macht die INDIREKT-Funktion zu einem leistungsstarken Werkzeug für Szenarien, in denen Zellbezüge dynamisch geändert werden müssen, sei es durch Benutzereingaben, Formelergebnisse oder andere dynamische Bedingungen.
INDEX, VERGLEICH und INDIREKT: Daten aus mehreren Tabellen dynamisch abfragen
Die wahre Stärke von Excel entfaltet sich oft in der intelligenten Kombination verschiedener Funktionen. Wenn wir die INDEX-Funktion, die VERGLEICH-Funktion und die INDIREKT-Funktion miteinander verbinden, können wir hochdynamische und flexible Datenabfragen erstellen, die weit über die Möglichkeiten einfacher Suchfunktionen hinausgehen. Diese leistungsstarke Kombination ermöglicht es uns, nicht nur Werte basierend auf Kriterien zu finden, sondern auch den Suchbereich selbst dynamisch anzupassen, wodurch wir Daten aus verschiedenen Tabellen oder sogar Tabellenblättern mit derselben Formel abrufen können.
Lassen Sie uns die Rollen der einzelnen Funktionen in dieser Kombination noch einmal kurz rekapitulieren:
- Die
INDEX-Funktion gibt uns einen Wert, abhängig von der Position innerhalb einer Tabelle, zurück. Sie ist der Kern, der den gesuchten Wert extrahiert. - Die
VERGLEICH-Funktion findet für uns die gesuchte Position (Zeilen- oder Spaltennummer) und liefert diese Information an dieINDEX-Funktion. Sie ist der “Suchmotor”, der die Koordinaten liefert. - Mithilfe der
INDIREKT-Funktion können wir den Bereich, in dem wir suchen, verändern. Das heißt, wir sind nicht auf eine einzige Tabelle beschränkt, sondern können die gleiche Formel auch auf andere Tabellen und Bereiche anwenden, deren Namen oder Bezüge in einer Zelle hinterlegt sind. Sie ist der “Schalter”, der den Suchbereich umschaltet.
Stellen Sie sich vor, Sie haben auf der linken Seite zwei Tabellen, aus denen Sie die Daten ziehen möchten. Auf der rechten Seite wurden in den Zellen H3, H4 und H5 bereits Dropdown-Listen mit den Auswahlmöglichkeiten für die Kriterien „Extras“, „Produkt“ und „Farbe“ für die verschiedenen Vorgaben vorbereitet. Eine bestimmte Kombination dieser Kriterien soll uns dann die entsprechenden Daten auswerfen. Durch Klicken auf eine der drei Zellen H3, H4 oder H5 erscheint rechts ein kleines Kästchen, mit dessen Hilfe sich das dazugehörige Dropdown-Menü öffnen lässt; auf diese Weise kann man die jeweils gewünschte Kriterien-Kombination auswählen.
Wir werden nun den Einsatz der INDEX-Funktion sowohl allein als auch in Kombination mit der VERGLEICH-Funktion für die gesuchte Kriterien-Kombination „Ohne Extras – B – grün“ erklären, bevor wir die INDIREKT-Funktion hinzufügen.
Schritt 1) Die grundlegende INDEX-Funktion
Zunächst ermitteln wir den Wert mit der reinen INDEX-Funktion, um das grundlegende Verständnis zu festigen. Angenommen, der Wert für „Produkt B“ und „grün“ in der Tabelle „Ohne Extras“ befindet sich in Zeile 3 und Spalte 4 (innerhalb des Bereichs A3:D6).
Die Formel wäre: =INDEX(A3:D6;3;4).
Einsatz der INDEX-Funktion mit festen Zeilen- und Spaltennummern, um einen Wert aus einem definierten Bereich abzurufen
Schritt 2) Kombination von INDEX- und VERGLEICH-Funktion
Um die Zeilen- und Spaltennummern dynamisch zu ermitteln, kombinieren wir INDEX mit VERGLEICH. Die VERGLEICH-Funktion sucht nach der Position des Produkts (H4) in der Produktliste (A3:A6) und nach der Position der Farbe (H5) in der Kopfzeile (A3:D3).
Die Formel sieht dann so aus: =INDEX(A3:D6;VERGLEICH(H4;A3:A6;0);VERGLEICH(H5;A3:D3;0)).
Kombination der INDEX- und VERGLEICH-Funktion zur dynamischen Abfrage eines Wertes basierend auf Kriterien aus Dropdown-Listen
Das Ergebnis ist in beiden Fällen (Schritt 1 und Schritt 2) 35, da dies der Wert für „Produkt B“ und „grün“ in der ersten Tabelle ist.
Ergebnis der INDEX- und INDEX/VERGLEICH-Kombination, das den Wert 35 für die ausgewählten Kriterien anzeigt
Schritt 3) Kombination von INDEX-, VERGLEICH- und INDIREKT-Funktion
Durch die Hinzunahme der INDIREKT-Funktion ermöglicht es uns Excel, nicht nur im Bereich der oberen Tabelle „Ohne Extras“ (A3:D6) zu suchen, sondern unsere Suche auch auf die untere Tabelle „Mit Extras“ (A9:D12) auszuweiten. Dies geschieht, indem wir den bisher fest definierten Suchbereich (A3:D6) am Beginn der Formel durch die INDIREKT-Funktion ersetzen. Die INDIREKT-Funktion referenziert hierbei eine Zelle (z.B. I3), die den Namen des gewünschten Suchbereichs (z.B. „A3:D6“ oder „A9:D12“) als Text enthält.
Wenn die Zelle I3 den Text A3:D6 enthält, wird die obere Tabelle durchsucht. Wenn I3 den Text A9:D12 enthält, wird die untere Tabelle durchsucht.
Die Formel wird dann wie folgt angepasst: =INDEX(INDIREKT(I3);VERGLEICH(H4;A3:A6;0);VERGLEICH(H5;A3:D3;0)).
Die erweiterte Formel mit INDEX, VERGLEICH und INDIREKT, die einen dynamischen Suchbereich über eine Zelle (I3) steuert
Diese Erweiterung der Formel ermöglicht es nun, dass der Bereich, in dem gesucht wird, über eine Dropdown-Box (die beispielsweise den Inhalt von Zelle I3 steuert) dynamisch verändert werden kann. Dies ist besonders nützlich, wenn Sie mit mehreren identisch aufgebauten Datentabellen arbeiten und je nach Auswahl des Benutzers in einem anderen Datensatz suchen möchten, ohne die Formel manuell anpassen zu müssen.
Fazit
Die Excel INDEX-Funktion, insbesondere in Kombination mit VERGLEICH und INDIREKT, ist ein unverzichtbares Trio für jeden fortgeschrittenen Excel-Anwender. Sie bietet eine Flexibilität und Leistungsfähigkeit, die weit über traditionelle Suchfunktionen wie SVERWEIS hinausgeht. Durch die präzise Positionierung von Werten mit INDEX, die dynamische Ermittlung dieser Positionen mit VERGLEICH und die flexible Anpassung des Suchbereichs mit INDIREKT können Sie komplexe Datenabfragen und interaktive Dashboards erstellen, die Ihre Datenanalyse revolutionieren.
Diese Funktionen ermöglichen es Ihnen, Daten nicht nur effizienter zu finden, sondern auch Ihre Arbeitsblätter robuster und benutzerfreundlicher zu gestalten, indem Sie manuelle Anpassungen minimieren und die Fehleranfälligkeit reduzieren. Nehmen Sie sich die Zeit, diese Funktionen zu üben und in Ihren eigenen Arbeitsmappen anzuwenden. Beginnen Sie mit einfachen INDEX-Anwendungen, erweitern Sie diese um VERGLEICH für dynamische Suchen und integrieren Sie schließlich INDIREKT für maximale Flexibilität bei der Bereichsauswahl. Ihr Verständnis und Ihre Beherrschung dieser powerfulen Kombination werden Ihre Excel-Fähigkeiten erheblich erweitern und Ihnen helfen, Ihre Daten effektiver zu nutzen. Probieren Sie es selbst aus und entdecken Sie das volle Potenzial dieser Funktionen!