Taking the right amount out of more than one condition

[JimmyBambo]

Hi friends,
I took picture of my problem as it is easier for me to explain on it, as I cant fully solve this problem.
So I have to determine percentage of discounts (F row) according to conditions in table on right side.
1) For different suppliers I have different discount rates on different dates,
2) For some products of suppliers I have also different discount rates.
So for example, if producer is Coca Cola, product is Coca Cola, date of invoice is 28.01.2018, discount is 5%. If it was product Fanta or Sprite, discount would be 7%.
I hope so that this is solvable, thank you in advance

 

[oldbrewer]

is name of product actually name of supplier ?

 

[oldbrewer]

If it was product Fanta or Sprite, discount would be 7%. Sprite would be 6% ????

 

[JimmyBambo]

is name of product actually name of supplier ?

Thank you for question.
Yes it is, it was my mistake.
Correct table is below.

 

[JimmyBambo]

Thank you again.
If it was Fanta, discount would be 7% if it was invoiced until 30.04.2018.
If date of invoice is from 30.04.2018. until 25.08.2018., discount is 8% for fanta.-
For Sprite discount is 6% until 25.08.2018, from 25.08.2018 until 28.09.2018 is 7%.
If it was some product which is supplied by Coca Cola, but it is not in list "Product name" (column I), like Coca Cola zero for example, than take discount from name of supplier (Coca Cola, column H), and ignore column I "Product name".

 

[oldbrewer]

buyer

supplier

prod

date

dis

helper1

CS

COC

coca

28/01/2018

5

COC

CS

COC

fan

23/07/2018

8

COCfan

CS

PEP

pepsi

16/08/2018

9

PEP

CS

NES

nesc

21/11/2018

3

NES

mytable

FF

PEP

7up

05/02/2018

10

PEP

FF

COC

ccz

01/05/2018

6

COC

FF

NES

nesg

30/08/2018

6

NESnesg

1

2

3

R

PEP

mir

15/03/2018

8

PEPmir

helper

supplier

product

30/04/2018

25/08/2018

28/09/2018

R

COC

spr

30/06/2018

6

COCspr

COC

COC

5

6

5

R

COC

coca

15/07/2018

6

COC

COCfan

COC

fan

7

8

8

R

NES

nese

29/09/2018

3

NES

COCspr

COC

spr

6

6

7

R

NES

nesa

31/10/2018

3

NES

NES

NES

4

2

3

GS

PEP

spr

13/03/2018

#N/A

PEPspr

NESnes

NES

nes

3

2

3

GS

PEP

mir

23/08/2018

8

PEPmir

NESnesg

NES

nesg

5

6

6

GS

NES

neso

15/11/2018

12

NESneso

NESneso

NES

neso

10

10

12

PEP

PEP

10

9

11

PEPmir

PEP

mir

8

8

9

mytable2

01/01/2018

1

01/05/2018

2

29/09/2018

3

31/12/2018

3

does this look along the right lines ?

<colgroup><col span="3"><col><col span="9"><col span="3"><col span="2"></colgroup><tbody>
</tbody>

 

[oldbrewer]

can cocacola and pepsi both supply sprite ?

 

[JimmyBambo]

No, only Coca Cola can produce sprite.
TY

 

[JimmyBambo]

Yes, this look right.

 

[JimmyBambo]

can cocacola and pepsi both supply sprite ?

I made mistake, there should be 7up. This is just example of my problem so I made some mistakes, because full table is something like 500k lines, but when I resolve this problem in example, I will resolve everything.