Excel: Ersatz für SVERWEIS

Sie kennen die SVERWEIS-Funktion? Dann wissen Sie sicherlich auch, dass diese Funktion nicht in allen Situationen eingesetzt werden kann. So kann der SVERWEIS nur einen Rückgabewert rechts neben der Suchspalte zurückliefern und gibt beim Annähern nur den nächstkleineren Wert zurück. Was aber, wenn der Rückgabewert links neben der Suchspalte steht und Sie beim Annähern den nächstgrößeren Wert zurückgeliefert haben möchten? Die Lösung ist eine Kombination aus den Funktionen VERGLEICH und INDEX.

Ausgangssituation:

Sie möchte zur ArtikelNr. „10800“ den Preis ermitteln.

Schauen wir uns zuerst den Aufbau der VERGLEICH-Funktion an:
=VERGLEICH(Suchkriterium; Suchmatrix, Vergleichstyp)

Suchkriterium:
Das eindeutige Kriterium, zu dem Sie einen Rückgabewert ermitteln möchten

Suchmatrix:
Der Zellbereich, in dem nach dem Suchkriterium gesucht werden soll

Vergleichstyp:
„ 0“= Das Suchkriterium muss exakt übereinstimmend gefunden werden
(Andernfalls liefert die Funktion „#NV“ zurück)

„ 1“= Liefert ggf. den numerisch oder alphabetisch nächstkleineren Wert zurück.
„-1“= Liefert ggf. den numerisch oder alphabetisch nächstgrößeren Wert zurück.

Wichtig:

• Möchten wir ggf. den nächstkleineren Wert zurückgeliefert bekommen (Vergleichstyp =1), dann muss die Suchmatrix aufsteigend sortiert sein!
• Möchten wir ggf. den nächstgrößeren Wert zurückgeliefert bekommen (Vergleichstyp =-1), dann muss die Suchmatrix absteigend sortiert sein!
• Möchten wir den exakt übereinstimmen Wert zurückgeliefert bekommen (Vergleichstyp =0), dann ist eine Sortierung der Suchmatrix nicht notwendig!

Auf unser Beispiel bezogen muss die Funktion als folgendermaßen lauten:

Das Ergebnis der Funktion lautet „6“. Die VERGLEICH-Funktion liefert uns also nur die Position der Fundstelle innerhalb der Suchmatrix zurück. Jetzt müssen wir nur noch den Wert der 6ten Position in der Spalte „Preis“ ermitteln. Dazu bedienen wir uns der Funktion INDEX: Sie liefert einen Wert zurück, der innerhalb eines Zellbereichs im Schnittpunkt einer festzulegenden Spalte und Zeile liegt.

=INDEX(Matrix; [Zeile]; [Spalte])

Matrix:
Ist der Zellbereich, in dem wir den Wert einer bestimmten Zeilen- und Spaltenposition ermitteln wollen.

Zeile:
Ist die gewünschte Zeilenposition als Zahl oder Bezug (optionaler Wert)

Spalte:
Ist die gewünschte Spaltenposition als Zahl oder Bezug (optionaler Wert)

Das Schöne an unserer Situation ist, dass wir die Zeilenposition bereits kennen: Zeile 6, die uns die VERGLEICH-Funktion bereits errechnet hat. Die INDEX-Funktion muss also wie folgt aufgebaut werden:

Die Funktion liefert uns den Wert der 6ten Zeile innerhalb der Matrix zurück: 281,00 €. Da wir für die Matrix nur die Zellen einer Spalte (der Preisspalte) markieren müssen, kann der Parameter „Spalte“ entweder ganz entfallen, oder wird mit „1“ festgelegt. Die Profis können natürlich auch beiden Funktionen ineinander bauen: Hierbei ersetzen wir den Parameter „Zeile“ im INDEX durch die VERGLEICH-Funktion.

=INDEX(C6:C15;VERGLEICH(C2;D6:D15;0);1)

Weitere Tipps zu Excel erhalten Sie in unserem Seminar Excel – Formeln und Funktionen. Wir freuen uns auf Sie!

Beitragsbild: © panthermedia.net / Leung Cho Pan