Excel-Funktionen INDEX & VERGLEICH schnell erklärt

Bei der Excel-Funktion INDEX könnt ihr den Wert einer bestimmten Zelle in einem festgelegten Bereich der Tabelle auslesen. Wird das mit der Funktion VERGLEICH kombiniert, könnt ihr damit in komplexen Tabellen blitzschnell Werte finden. GIGA erklärt euch, wie es gemacht wird.

Die Excel-Funktion INDEX arbeitet üblicherweise mit drei Werten:

• Der Definition des Auswertungsbereiches (z.B. von Zelle A1 bis Zelle K250)
• Der Zahl der Zeile (die dritte Zeile wäre etwa Zeile 3)
• Und der Zahl der Spalte (also etwa Spalte G)

Dadurch wird die Position einer Zelle genau bestimmt und dann deren Wert ausgegeben. In Verbindung mit der Funktion VERGLEICH könnt ihr das nutzen, um beispielsweise aus einem Dropdown-Menü einen Artikel zu wählen und aus dem nächsten Menü eine Größe, um zu erfahren, wie viele Artikel in dieser Größe vorrätig sind.

Unser Video zeigt euch noch ein paar Excel-Tipps:

Die Funktion INDEX in Excel-Tabellen

Tatsächlich ist die Excel-Funktion INDEX nur ein Basiswerkzeug für andere Berechnungen. Grob gesagt tut sie nichts, was man nicht genauso schnell selbst nachsehen könnte: Man wählt eine Zeile und eine Spalte und will den Wert an der Kreuzung der beiden Linien haben. Also gibt man Zeile und Spalte ein, um das Ergebnis angezeigt zu bekommen.

Oder anders gesagt: Die Funktion INDEX bekommt eine Position genannt und Excel gibt dann den Wert an dieser Stelle aus.

Im obigen Beispiel könnt ihr für die Zeile und die Spalte eine Zahl eingeben. Daraufhin wird durch die Funktion INDEX der Wert der entsprechenden Zeile ermittelt und angezeigt.

Die Funktion INDEX ist so aufgebaut:

=INDEX(Bereich;Zeile;Spalte)

• Den Bereich legt ihr selbst fest: Er erstreckt sich üblicherweise von der ersten Zelle bis zur letzten Zelle der Tabelle, sodass alle Werte ermittelt werden können.
• Zeile und Spalte werden jeweils durch die entsprechende Zahl gekennzeichnet.

In diesem Beispiel erstreckt sich also der Bereich von Zelle A1 bis Zelle D4 – das entspricht dem Bereich A1:D4.

Die Formel für die Funktion lautet ganz einfach =INDEX(A1:D4;B6;B7)

Das bedeutet:

• Der Bereich erstreckt sich von A1 bis D4.
• In Zelle B6 gibt man den Wert für die Zeile
• In Zelle B7 soll der Wert für die Spalte

Nur im gewählten Bereich gibt die Tabelle korrekte Werte aus. Die Zeilen werden dabei wie immer in Zahlen ausgedrückt. Bei den Spalten werden nun – statt der Buchstaben – ebenfalls Zahlen zur Positionsbestimmung gewählt.

Excel: Funktion INDEX mit VERGLEICH kombinieren

INDEX nimmt die Zeilen- und Spalten-Nummer einer Zelle und guckt dort nach dem Wert. Das kann man auch selbst machen. Richtig angewendet wird die Funktion in Kombination mit der Excel-Funktion VERGLEICH.

Verständlich wird das etwa mit folgendem Beispiel:

• In einer umfangreichen Tabelle stehen die Zahlen der im Lager befindlichen Kleidungsstücke.
• Alle Artikelbezeichnungen stehen in Spalte A.
• Alle Kleidungsgrößen stehen in Zeile 1.
• Nun soll die vorhandene Anzahl der jeweiligen Kleidungsstücke in verschiedenen Größen angezeigt werden, wenn man sie aus einer Dropdown-Liste auswählt.

Hier seht ihr, wie die Kombination der Funktionen INDEX und VERGLEICH dazu führt, dass ihr schnell herauskriegt, welche Größen noch vorrätig sind.

• Im Beispiel wird in einem Dropdown-Menü in A23 eine Kleidungsart herausgesucht.
• In D23 analog dazu die Größe.
• Die Funktion VERGLEICH macht aus dieser Auswahl eine Zeilen- und Spalten-Nummer.
• Diese werden in die Funktion INDEX eingesetzt, um an der Schnittstelle der Position die Anzahl der vorrätigen Artikel auszugeben.

Was macht dabei die Funktion VERGLEICH?

Bei VERGLEICH nehmt ihr den Wert einer Zelle und vergleicht ihn mit den Werten in einer sogenannten Suchmatrix. Damit ist entweder eine ganze Zeile oder eine Spalte mit eindeutigen Werten gemeint. Der „Vergleichstyp“ sollte 0 sein, damit die Werte exakt übereinstimmen.

Beispiel:

• Für die Anzeige des Wertes mit INDEX braucht ihr die Zeile und Spalte eines Wertes.
• Ihr wollt wissen, in welcher Zeile die schwarzen Hemden stehen.
• Außerdem braucht ihr die Spalten-Nummer der Größe L.
• Wenn ihr beide Werte habt und sie automatisch in die Funktion INDEX eingesetzt werden, erfahrt ihr sofort, wie viele schwarze Hemden in Größe L noch auf Lager sind.

So ist die Funktion Vergleich aufgebaut:

=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)

• Das Suchkriterium ist der gesuchte Artikel bzw. die Größenbezeichnung. Es geht um exakte Übereinstimmungen, wie etwa „Hemd schwarz“.
• Die Suchmatrix ist entweder die Spalte mit den Artikeln (A2:A20) oder die Zeile mit den Größen (B1:K1).
• Vergleichstyp ist 0 für eine exakte Übereinstimmung (0=Erster exakt übereinstimmender Wert. 1=größter Wert der kleiner oder gleich dem Suchkriterium ist. -1=kleinster Wert, der größer oder gleich dem Suchkriterium ist).

So baut ihr das im Bild zu sehende Beispiel nach

• Tragt in die erste Spalte eure Artikelbezeichnungen ein.
• Die Zeile 1 füllt ihr mit den Größenbezeichnungen aus.
• Füllt die Tabelle jetzt mit euren Lagerhaltungswerten.
• Macht aus den Artikel und Größen je eine Dropdown-Liste, damit ihr später schnell das Passende auswählen könnt.
• Daneben schreibt ihr eine VERGLEICH-Funktion, die den aktuellen Wert des Dropdowns mit der Suchmatrix vergleicht und euch die Zahl der übereinstimmenden Zeile oder Spalte angibt.

Wie im obigen Bild zu sehen, habe ich aus den Werten für den Artikelnamen und die Größe je eine Dropdown-Auswahl gemacht. Das geht folgendermaßen:

• In die Zelle klicken, die eine Dropdown-Auswahl erhalten soll.
• Oben im Menü den Reiter Daten anwählen und dort Datenüberprüfung auswählen.
• Im nachfolgenden Fenster bei Zulassen den Wert Liste anklicken.
• Dann unten ins Feld Quelle klicken.
• Jetzt mit der Maus die Zellen vom ersten bis zum letzten Artikel markieren.
• Mit OK bestätigen.

Also lautet die Formel für die Artikelbezeichnungen etwa:

=VERGLEICH(A23;A2:A20;0)

A23 ist die Zelle, in der sich das Dropdown mit den Artikelnamen befindet.

A2:A20 ist der Bereich, in dem gesucht werden soll – die Spalte mit den Artikeln.

0 ist der Vergleichstyp und heißt, dass die Übereinstimmung exakt sein soll.

Wenn ihr also jetzt im Dropdown „Pullover rot“ sucht, wird dieser Wert mit der Artikelliste verglichen. Diese Bezeichnung steht in Zeile 14 der Suchmatrix, also wird 14 an dieser Stelle ausgeben. Damit habt ihr jetzt die Zeilen-Nummer für die Funktion INDEX.

Die Funktion für die Größe (Spalten) ist so aufgebaut:

=VERGLEICH(D23;B1:K1;0)

Der Wert aus dem Dropdown in D23 wird mit der Suchmatrix B1:K1 verglichen. Die Fundstelle wird als Zahl der Spalte angegeben.

Nun können diese beiden ermittelten Werte, die sich in B23 (Zeilen-Nummer) und E23 (Spalten-Nummer) befinden, in die Funktion Index eingesetzt werden.

Die Funktion für den Index lautet:

=INDEX(B2:K20;B23;E23)

B20:K20 ist der gesamte Suchbereich mit Lagerhaltungszahlen. In B23 und E23 stehen Zeile und Spalte des ausgewählten Artikels.

Ausgegeben wird die Anzahl der im Lager befindlichen Artikel. Das Prinzip funktioniert auch für andere Werte, die sich in zwei Eigenschaften aufteilen lassen (Name und Größe, Artikelnummer und Packungsgröße,…)

Du willst keine News rund um Technik, Games und Popkultur mehr verpassen? Keine aktuellen Tests und Guides? Dann folge uns auf Facebook oder Twitter.