Vlookup with multiple conditions

[olivera87]

Hello,

I ask for your help. I would like to optimize my everyday work, since now it is devided into many excel files that take long to check them all. I have joined all the excel files into one, and now I need help to set up an automatic output cell that would agree with many conditions.

So we have columns: CUSTOMER CODE (3-5 numbers usually), PRODUCT CODE (also 3-5 numbers usually), QUANTITY, PRICE, DATE. There are many customers that have some of the same products, with a price (unique for every partner) that depends on the order quantity (the more products you order, the lower the price). Example: customer 12546 has a product 5555 for a price 2,10 eur if they order 100 pcs, 2,00 eur for 200 pcs, and 1,6 eur for 500 pcs). The quantity is unique for every partner, just like the prices. The date is important since the price can change in the future and I would like to only enter a new period and price when it does.

My wish is to have a sheet with a table where I would write: CUSTOMER CODE, PRODUCT CODE, QUANTITY AND DATE manualy and the PRICE per pcs would be shown automaticly in the next cell considering the conditions. I attached the picture of how the data base would look, and what I would like to get out with a formula.

Please help me, which function would be the best. As I researched, VLOOK would not be the best for this.

Thank you so much for your help.

 

[kweaver]

Is the result correct if you change "SUMIFS" to "MINIFS" ?