Excel Funktion Sverweis oder Wenn?
Hallo liebe Community,
welche Formel müsste ich hier in C2 bis C7 verwenden um den korrekten Rabatt zu erhalten? Geht es Ohne Wenn Funktion, falls die Werte in A11-14 geändert werden?
4 Antworten
Versuch es mal hiermit (in C2 und dann nach unten kopieren)
=VERSCHIEBUNG(A$10;VERGLEICH(A2;B$11:B$14;1);0)
Variante mit Funktionsnamen in Englisch:
=OFFSET(A$10;MATCH(A2;B$11:B$14;1);0)
Anmerkung: Damit das funktionert, muss die Rabattliste unbedingt in "Ab Kundenzahl" aufsteigend sortiert sein, so wie es in den Beispieldaten auch gegeben ist.
Für den SVERWEIS müsstest du die Spalten der unteren Tabelle tauschen.
Dann funktioniert es.
=SVERWEIS(A2;$A$11:$B$14;2;WAHR)
SVERWEIS kann nur "nach rechts" vom Gefundenen Wert referenzieren. Daher ist OFFSET meist die bessere Wahl.
Du meinst mit OFFSET BEREICH.VERSCHIEBEN?
Ich kenne als guten SVERWEIS-Ersatz INDEX + VERGLEICH. VERGLEICH kann auch ungenau suchen. Das würde dann so aussehen:
=INDEX($A$11:$A$14;VERGLEICH(A2;$B$11:$B$14;1))
Du meinst mit OFFSET BEREICH.VERSCHIEBEN?
Nein, das meinte ich nicht. Siehe meine Antwort zum Problem.
PS: Mein Kommentar war an FS gerichtet, da er ein #NV rausbekommt. Deine Antwort ist natürlich vollkommen korrekt.
Hi,
ich würde immer wenn es um Matritzen geht, die INDEX(VERGLEICH)-Lösung verwenden.
also hier in C2 die Formel
=INDEX(A$11:B$14;VERGLEICH(A2;B$11:B$14;1);1)
einsetzen und runterkopieren bis C7.
In Prosa heißt die Formel: gib mir aus der Rabatt-Tabelle den Wert, für die aktuelle Kundenzahl.
Du vergleichst quasi die aktuelle Kundenzahl in A2 mit der Liste der Grenzwerte in B11 bis B14. Die so gewonnene Zahl ist die Zeilenzahl für die Matrix A11:B14, die den Rabatt in Spalte 1 enthält. Die Zahl ganz hinten in der Vergleichs-Formel sagt aus, ob das Ergebnis gleich oder darunter oder drüber liegen soll. Hier ist gefragt größer, also drüber also 1.
Die $-Zeichen in der Formel (bei den Matrizen) fixieren den Bereich der Matrix, so dass man die Formel nach unten kopieren kann ohne dass der Matrix-Bereich verschoben wird.
Alles klar?
Ich würde es mit sverweis machen, weil Formeln mit verschachtelten wenn schnell unübersichtlich werden.
danke. Da die angaben ja ab einem bestimmten wert gelten - wie kann man ein #NV vermeiden?
Hier das Beispiel:
https://www.dropbox.com/s/2twvz4b6lu4gjzd/Sverweis_Rabatt.xlsx?dl=0
Es gibt keinen Wert wo NV# als Ergebnis kommen könnte, weil ja in der Angabe steht ab?
Zum Vermeiden von NV#-Werten gibt es es sonst die Funktion WENNNV, wo man bestimmen kann, welches Ergebnis man statt NV# haben möchte.
übrigens: Bei SVERWEIS hängt es vom letzten Parameter ab, ob es genau sucht oder den Wert AB der vorgegebenen Zahl nimmt.
Danke, das funktioniert. Warum muss ich dafür das vertauschen, dass kein #NV rauskommt?