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.

A | B | C | D | E | F | G | H | I | J | |

1 | Item List | Units Discount 1 | Units Discount 2 | Wholesale Cost | Discount Rate 1 | Discount Rate 2 | Item Purchased | Total Units | Per Unit | Total Cost |

2 | Bellen | 100 | 250 | $25 | 5.0% | 5.0% | Bellen | 300 | $25 | #REF! |

3 | Carlota | 100 | 200 | $25 | 5.0% | 5.0% | ||||

4 | Majestic | 50 | 150 | $50 | 10.0% | 15.0% | ||||

5 | Sunset | 75 | 150 | $75 | 15.0% | 10.0% | ||||

6 | Phenolic | 150 | 250 | $20 | 5.0% | 10.0% | ||||

7 | Zapper | 200 | 350 | $15 | 5.0% | 10.0% |

<tbody>

</tbody>

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<index(b2:b7,match(g2,a2:a7,0)),h2*i2,< strong="">,IF(H2</index(b2:b7,match(g2,a2:a7,0)),h2*i2,<><index(c2:c7,match(g2,a2:a7,0)),<index(b2:b7,match(g2,a2:a7,0)),h2*i2,< strong=""><index(c2:c7,match(g2,a2:a7,0)),< strong=""><index(c2:c7,match(g2,a2:a7,0)),vlookup(g2,a2:f7,4,0))*i2*(1-index(f2:f7,match(g2,b2:b7,0),< font="">

IF(H2>INDEX(D2:D7,MATCH(G2,A2:A7,0)),VLOOKUP(G2,A2:F7,4,0))*I2*

</index(c2:c7,match(g2,a2:a7,0)),vlookup(g2,a2:f7,4,0))*i2*(1-index(f2:f7,match(g2,b2:b7,0),<><index(c2:c7,match(g2,a2:a7,0)),vlookup(g2,a2:f7,4,0))*i2*(1-index(f2:f7,match(g2,b2:b7,0),< font="">(1-INDEX(E2:E7,MATCH(G2,A2:A7,0)))*((1-INDEX(F2:F7,MATCH(G2,A2:A7,0)))))))</index(c2:c7,match(g2,a2:a7,0)),vlookup(g2,a2:f7,4,0))*i2*(1-index(f2:f7,match(g2,b2:b7,0),<></index(c2:c7,match(g2,a2:a7,0)),<>

**<index(b2:b7,match(g2,a2:a7,0)),h2*i2,< strong="">**<index(b2:b7,match(g2,a2:a7,0)),h2*i2,< strong=""><index(c2:c7,match(g2,a2:a7,0)),< strong="">

**<index(c2:c7,match(g2,a2:a7,0)),< strong="">**

</index(c2:c7,match(g2,a2:a7,0)),<></index(b2:b7,match(g2,a2:a7,0)),h2*i2,<></index(c2:c7,match(g2,a2:a7,0)),<>

I tried to paste the formula, but it only pastes part of it, I do not know why..

Is there a formula that could derive the correct answer, sorry for the formula, I tried to paste it from word numerous times and it for

<index(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),< strong=""><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(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(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),<><index(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),< strong="">Hope we can solve this, thank you for taking the time to read this much appreciated. </index(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),<><index(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),< strong="">

<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(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(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),<></index(c2:c7,match(g2,a2:a7,0)),<></index(b2:b7,match(g2,a2:a7,0)),h2*i2,<>

**<index(b2:b7,match(g2,a2:a7,0)),h2*i2,< strong="">**</index(c2:c7,match(g2,a2:a7,0)),<index(b2:b7,match(g2,a2:a7,0)),h2*i2,<>

**<index(c2:c7,match(g2,a2:a7,0)),< strong="">**</index(b2:b7,match(g2,a2:a7,0)),h2*i2,<>**<index(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),< strong=""><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(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(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),<></index(c2:c7,match(g2,a2:a7,0)),<></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(b2:b7,match(g2,a2:a7,0)),h2*i2,if(h2<index(c2:c7,match(g2,a2:a7,0)),<>