Multiple Vlookuip, Index and Match Function in One Formula Question?

[Vladmir1111]

I have a problem calculating the last discount rate, you can see in column F, you can select whatever Item from the item list, in this case I selected Bellen, if you purchase less than 100 units of bellen, you pay the wholesale cost of $25, if you purchase more than 100 units but less than 250 units, you get a 5% discount on the price per unit for that item, and if you purchase over 250 units, you get an additional 5% discount.

I attached a picture of the table below

excel question.JPG

The formula below works up until the second discount, in cell H2, 300 units were purchased which means the buyer is entitled to both discounts on the cost per unit, total cost is derived by 300 x (1-5%) x (1-5%) x $25 = $6,768.75 is the correct answer.

In cell J2, I get a #REF!, now below is the formula I have in cell J2, the highlighted red part is the part that works if units are below 100 and or 250, but once over 250, it does not work, I looked at it on formula evaluator, it goes astray when it starts evaluating the third IF formula. The reason I use this formula is because I want to be able to select any product form the list and type in the units which will then give me the total cost based on the criteria. I think I might have the parenthesis in the wrong place or used one more index function than needed.

IF(H2<INDEX(B2:B7,MATCH(G2,A2:A7,0)),H2*I2, IF(H2<INDEX(C2:C7,MATCH(G2,A2:A7,0)),VLOOKUP(G2,A2:F7,4,0))*I2*(1-INDEX(F2:F7,MATCH(G2,B2:B7,0),IF(H2>INDEX(D2:D7,MATCH(G2,A2:A7,0)),VLOOKUP(G2,A2:F7,4,0))*I2* (1-INDEX(E2:E7,MATCH(G2,A2:A7,0)))*((1-INDEX(F2:F7,MATCH(G2,A2:A7,0)))))))

Hope we can solve this, thank you for taking the time to read this much appreciated.

[:) Sixthsense :)]

Instead of screenshot attach a sample workbook with expected output for better understanding