Es geht bei allen 3 Funktionen um das gleiche. Wenn man ein bestimmtes Wort (Suchkriterium) in einer Tabelle/Matrix findet, möchte man einen Wert aus einer anderen Spalte aus dieser Zeile zurückhaben. Klingt vielleicht etwas kompliziert aber hier noch einmal etwas einfacher.
Stellt Euch vor, ihr möchtet die Telefonnummer eines Freundes in einem Telefonbuch (wer macht denn noch sowas 🙂 ) finden. Der Name des Freundes, ist das Suchkriterium. Mit diesem Suchkriterium im Kopf schlagt ihr das Telefonbuch auf und findet die Seite, in der der Name auftauchen soll. Dann leckt ihr Euch den Finger, geht mit diesem von oben nach unten auf dieser Seite lang und wenn ihr den Namen desjenigen gefunden habt, rutscht der Finger nach rechts und stoppt bei der Telefonnummer.
Nichts anderes macht der SVerweis. Der SVerweis ist genauso gestrickt, wie die eben beschriebene Vorgehensweise. Mit einem Suchwort / Suchkriterium in eine Tabelle gehen, wenn fündig geworden, nach rechts gehen und den Wert auslesen. Und hier sieht man die Einschränkung, die der SVerweis mit sich bringt. Er kann nur Werte RECHTS vom Fundort auslesen/zurückgeben.
Wer flexibel mit seinen Tabellen umgehen kann, kann sich also damit behelfen, dass er die Spalte, in der das Suchwort gefunden werden soll, links von der Spalte anordnet, in der der Rückgabewert (z.B. die Telefonnummer) steht.
Sollte das nicht gehen und die Rückgabespalte steht links von der Suchwortspalte (wenn man diese so bezeichnen will), dann kommen die Funktionen INDEX und VERGLEICH ins Spiel. Gut geraten, es ist eine Kombination aus zwei unterschiedlichen Funktionen. Der SVerweis ist nur eine Funktion, die “Argumente” (Suchkriterium; Angabe der Matrix in der das Suchwort stehen soll; Ausgabespalte als Zahl; Genauigkeitswert 0 oder 1) benötigt.
Um einen Wert links von der Suchspalte zurückgeben zu können, benötigt man zwei Funktionen und jede benötigt ihre eigenen Argumente. Aber keine Angst, erstens handelt es sich bei den Argumenten, die INDEX und VERGLEICH benötigen, um fast die gleichen, die auch der SVerweis braucht und zweitens beschreibe ich Euch gleich wie ihr diese beiden Funktionen so kombinieren könnt, das es funktioniert.
Erst einmal ein Bild, um das besser zu illustrieren:
Die Funktion wird in Zelle “B3” eingetragen.
- Mit dem Suchwort “serge@blub.de” aus Zelle “A3” wird in den Spaltenbereich “C13:C18” gegangen.
- Wenn das Wort gefunden wird, soll der Wert aus Spalte “A13:A18” ausgegeben werden.
- Der gefundene Wert aus Zelle “A14” wird in die Zelle “B3” eingetragen
Wir schauen uns jetzt die Funktionen Index/Vergleich genauer an:
Als erstes untersuchen wir die Funktion Vergleich:
Vergleich(Suchkriterium;Such-Matrix;Vergleichstyp)
Da sind sie ja wieder, die üblichen Verdächtigen, die wir ja schon aus der SVerweis Funktion her kennen. Suchkriterium, Matrix, und Vergleichstyp (Genauigkeitswert)
(0 = genaue Übereinstimmung / 1 = ungefährer Wert)
Was gibt uns die Vergleich-Funktion aber zurück? Richtig, die Nummer der Zeile, in der der übereinstimmende Wert gefunden wurde. Wenden wir diese Vergleichsfunktion mal auf die obige Tabelle an:
Wenn ich in B3 die Formel eingebe: Vergleich(A3;C13;C18;0) erhalte ich eine 2, da ich den serge@blub.de in der zweiten Zeile finde (aus dem roten Bereich untere Abbildung).
Jetzt kommen wir zur Index-Funktion
INDEX(Matrix;Zeile;[Spalte]) … oder ….
INDEX(Bezug;Zeile;[Spalte];[Bereich])
Hier gibt es den feinen Unterschied “Matrix” oder “Bezug“.
- Eine Matrix ist immer ein Bereich über mehr als eine Spalte
- ein Bezug bezieht sich immer nur auf eine Spalte.
OK, ich merke wir müssen hier genauer hinschauen.
Beispiel “Matrix”
=INDEX(A13:C18;2;1) –> Ergebnis: Serge
Warum:
Ein Bereich über mehr als eine Spalte (A-C) ist eine Matrix. Hier erkennt die Formel aber nicht, aus welcher der drei Spalten (A, B oder C) ich meinen Rückgabewert aus der Zeile 2 haben möchte, also muss ich bei einer Matrix am Ende immer die Spaltennummer angeben, aus der ich den Rückgabewert erhalten möchte. Im obigen Fall = 1 also aus der Spalte “Name”.
Beispiel “Bezug”
=INDEX(A13:A18;2) –> Ergebnis: Serge
Warum:
Hier wurde ein Bezug eingetragen, das heißt, ein Bereich innerhalb einer Spalte (A). Hier erkennt die Formel sofort, aus der wie vielten Spalte der Rückgabewert aus Zeile 2 genommen werden soll, nämlich aus der einzigen (1). Daher braucht man am Ende der Indexfunktion keine Angabe darüber zu machen.
Zur besseren Erklärung noch folgendes:
Wenn ich anstelle von Spalte “A”, die Spalte “C” genommen hätte z.B. =INDEX(C13:C18;2), dann bräuchte ich ebenfalls keine Angabe machen.
Ich habe hier die Zeile 2 direkt als Zahl eingetragen, um die Indexfunktion als solche besser erklären zu können. Im Normalfall kenne ich ja nicht die Zeile, wo der Wert gefunden wird. Anstelle der 2 kann ich hier nun meine Vergleich-Funktion eintragen. Das sieht dann folgendermaßen aus:
In der “Matrix-Variante”:
=INDEX(A13:C18;Vergleich(A3;C13;C18;0);1)
In der “Bezugs-Variante”:
=INDEX(A13:A18;Vergleich(A3;C13;C18;0))
Und wer jetzt richtig aufgepasst hat, erkennt nun, worin die Index/Vergleich Funktion ihren Vorteil gegenüber der SVerweis-Funktion hat.
Wenn ich die Matrix-Variante nehme, kann ich mit der Zahl am Ende (hier war es ja die 1) steuern, aus der wie vielten Spalte (rechts oder Links von der Suchspalte ist völlig egal) ich meine Rückgabe-Spalte wähle.
Wenn ich die Bezugs-Variante nehme, kann ich mit der Angabe der Spalte (hier A) steuern, aus welcher Spalte ich meine Rückgabewert erhalten möchte. Auch hier ist es vollkommen egal, ob links oder rechts der Suchspalte.
Wer hierzu noch Fragen, Kritik oder Anregungen hat, gerne über die Kommentarfunktion.
LG John