Excel – Werte heraussuchen 1

Häufig muss man für eine Berechnung Werte heraussuchen – zum Beispiel Provisionssätze oder Preise. Im einfachsten Fall sieht das so aus:

Von D1 bis E6 stehen die Beträge und die dafür fälligen Provisionssätze. In den Spalten A und B wird berechnet, wie viel Provision in Euro gezahlt wird. Das wird über den SVERWEIS berechnet.

Die Formel in B2 lautet:
=A2*SVERWEIS(A2;$D$2:$E$6;2;WAHR)

Das Argument WAHR ist hier wichtig, weil ich nicht exakte Werte suche. Für den Betrag von 800 Euro werden 1 % Provision gezahlt – 800 liegt zwischen 500 und 1000. WAHR steht für eine “Ungefähre Übereinstimmung” und wird immer verwendet, wenn Sie Werte “von – bis” suchen.

Der SVERWEIS wird unpraktisch, wenn es sehr viele Spalten gibt und Sie variabel reagieren müssen. In der Tabelle unten ist die Provision nicht nur vom Betrag abhängig, sondern zusätzlich von der Bewertung A, B oder C. Da der SVERWEIS immer eine feste Spaltenzahl (1, 2 oder 3) benötigt, müssten Sie die Spaltenzahl mit einer WENN-Funktion erst mal ermitteln.

=SVERWEIS(A2;$E$1:$H$6;WENN(B2=“A“;1;WENN(B2=“B“;2;3));WAHR)

Das funktioniert zwar, ist aber unflexibel. Kommen weitere Bewertungen hinzu, muss die Formel ständig angepasst werden.

Hier bietet sich an, mit INDEX und VERGLEICH zu arbeiten.

Die Formel in C2 sieht so aus:
=A2*INDEX($E$1:$H$6;VERGLEICH($A2;$E$1:$E$6;1);VERGLEICH($B2;$E$1:$H$1;0))

Das ist zwar deutlich länger als der SVERWEIS, hat aber mehrere Vorteile:

1. Ich bin flexibel, wenn sich Bewertungen ändern
2. Um verschobene Spalten muss ich mich nicht kümmern – fällt die Bewertung B weg und wird die Spalte gelöscht, läuft der SVERWEIS ins Leere. Mit INDEX sucht sich die Funktion selber den neuen Standort der Bewertung „C“.

Der INDEX braucht als erstes Argument die Matrix von E1 bis H6. Hier wird gesucht.
Das zweite Argument ermittelt die Zeilenzahl, indem nach dem Betrag gesucht wird. In Klartext: Suche den Betrag aus A2 im Bereich von E1 bis E6. Als Vergleichstyp wird “1” angegeben – das steht für “kleiner als”.
Das dritte Argument sucht die Spaltenzahl und vergleich dafür die Bewertung aus B2 mit der Überschrift von E1 bis H1 – hier wird eine genaue Übereinstimmung gesucht, also der Vergleichstyp “0”.
Wenn ich aus Zeilenzahl und Spaltenzahl ermittelt habe, welcher Prozentsatz zu berücksichtigen ist, multipliziere ich diese Zahl direkt mit dem Betrag aus A2: =A2*INDEX….

Morgen geht es einen Schritt weiter – die Spaltenbeschriftung muss erst einmal ermittelt werden.