Sverweis mit mehreren Kriterien

[Excelmonsta]

Hallo zusammen,

ich bin neu hier und möchte gerne an diesem Forum teilnehmen da ich in Zukunft viel mit Excel-Formeln
und Office-Programmen arbeiten darf

Ich habe einige Grundkenntnisse - bei Formeln mit mehreren Kriterien habe ich jedoch schon Schwierigkeiten,
mir ist bewusst, dass ich noch sehr viel zu lernen habe,
für viele von euch sind meine Fragestellungen sicher ähnlich dem 1x1...ich wäre aber über etwas Hilfe dankbar und möchte
in Zukunft auch gerne an Diskussionen bzw. Lösungen teilnehmen.

Anbei eine Arbeitsmappe mit folgender Aufgabenstellung:

Berechne die Provision vom Umsatz, die rechts stehende Matrix ist eigentlich in einem anderen Tabellenblatt.
Die Provision berechnet sich je nach Kostenstelle unterschiedlich, teils 7,5 % teils 8,5 %.
Gleichzeitig bezieht sich die Provision auf die Kategorie in Spalte A - also bei Buchstabe K werden die Provisionssätze
aus Spalte I genommen, bei Buchstabe L werden die Provisionssätze aus Spalte H genommen.

In meinem Ansatz habe ich mit dem SVerweis die Kostenstellen mit der Matrixspalte 2 verknüpft, irgendwie komm ich
nicht weiter wie ich die Option der Buchstaben K und L mit einarbeite...würdet ihr komplett anders vorgehen oder kann
man meine Formel ergänzen?

Ich würde mich sehr über eine Antwort freuen, ich hoffe das mit dem Dateiupload hat geklappt.
Vielen Dank im Voraus

[WillWissen]

Hi,

meinst du das so?

Tabelle1

	A	B	C	D	E
1	Kategorie	Umsatz		Kostenstelle	Provision
2	L	80		100	7,50%
3	L	90		101	7,50%
4	L	12		102	8,50%
5	L	80		103	8,50%
6	K	13		104	2,50%
7	K	15		105	2,50%
8	L	90		100	7,50%
9	L	2		101	7,50%
10	K	300		102	2,50%
11	K	50		103	7,00%

Formeln der Tabelle

Zelle Formel E2 =SVERWEIS(D2;$G$17:$I$23;VERGLEICH(A2;$G$17:$I$17;0);0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[BoskoBiati]

Hallo,

oder so:

Code:

=B2*SVERWEIS(D2;$G$18:$I$23;2+(A2="K");0)

[Excelmonsta]

Hallo WillWissen,

das sieht sehr gut aus, habe das gerade kopiert,
die Formel passt super - Vielen Dank - ich habe "befürchtet" das es für einige
sehr schnell gemacht ist, was mir gerade aufzeigt - wie weit mein Weg noch
ist und was für ein doch kleines Licht ich bin...Es gibt noch viel zu lernen 

Danke für die schnelle Hilfe und interessante Lösung, ich werde mich jetzt daran setzen und versuchen
die Formel mit dem Vergleich nachzuvollziehen. 

Einen schönen Abend noch und bis bald - ich hoffe ich kann mich dann auch bald einmal an anderen
Diskussionen beteiligen.

Viele Grüsse

[Excelmonsta]

Hallo Edgar,

die Lösung ist auch super - vielen Dank.
Bei beiden Lösungen ist mir aufgefallen, dass im 
"Bereich Verweis" eine "0" eingefügt ist - könnt ihr
dazu noch was sagen warum das so ist?

Vielen Dank im Voraus.

[WillWissen]

Hi,

schau dir mal die Syntax an, dann wirst du es verstehen:

[
Bild bitte so als Datei hochladen: Klick mich!
]

[Jockel]

Hallo, ich habe noch zwei mit INDEX()...:

Und: hier braucht man keinstenfalls 0 hier geht auch 1 als Bereichs_Verweis

Zusatz mit VERWEIS()..:

Code:

=B2*VERWEIS(D2;$G$18:$G$23;WAHL(2-(A2="L");$H$18:$H$23;$I$18:$I$23))

Arbeitsblatt mit dem Namen 'Tabelle1'
	H	I
2	6	6

Zelle	Formel
H2	=INDEX(($H$18:$H$23;$I$18:$I$23);VERGLEICH(D2;$G$18:$G$23;1);;2-(CODE(A2)-75))*B2
I2	=INDEX($H$18:$I$23;VERGLEICH(D2;$G$18:$G$23;1);2-(CODE(A2)-75))*B2

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

[Luffy]

Hallo,

hier auch noch eine =SUMMENPRODUKT() Variante:

Code:

=SUMMENPRODUKT((A2=$H$17:$I$17)*(D2=$G$18:$G$23)*($H$18:$I$23))

[BoskoBiati]

Hallo Luffy,

da SUMMENPRODUKT ein Ressourcenfresser ist, wäre das hier eher nicht die 1.Wahl.

@Jörg,

Deine Formeln bringen aber Fehler, wenn da dummerweise ein Kleinbuchstabe steht!

[Luffy]

Hallo Edgar

da SUMMENPRODUKT ein Ressourcenfresser ist...

wirklich? 

Das ist mir noch nie aufgefallen. 
Habe eine Kalkulation von meiner Arbeit hier aufm Desktop mit 690 =SUMMENPRODUKT() Formeln und einer großen Datenmenge. 
Diese Datei hat 636KB. 
Lösche ich alle 690 Formeln komm ich auf 560KB.

Habe auch noch ein Test gemacht und der Datei von Excelmonsta deine Formel beigefügt,

Code:

=B2*VERWEIS(D2;$G$18:$G$23;WAHL(2-(A2="L");$H$18:$H$23;$I$18:$I$23))

So hat sie 34KB

Wenn ich die SUMMENPRODUKT Formel einfüge hat sie genau so 34KB.