Multiple Vlookup, Index and Match Functions in a Formula Question?

[MXL]

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.

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

 

[etaf]

should
IF(H2<index(b2:b7,match(g2,a2:a7,0)),h2*i2
be I2 ?

IF H2 is less than the discount 1 , then use the wholesale price D2

then
IF(H2>INDEX(D2:D7,MATCH(G2,A2:A7,0)),
is looking at D?

so should it read H2*vlookup(G2,A2:A7, 4, false) <index(b2:b7,match(g2,a2:a7,0)),h2*d2 ,
<index(b2:b7,match(g2,a2:a7,0)),h2*d2
<index(b2:b7,match(g2,a2:a7,0)),h2*d2

IF(H2>INDEX(C2:C7,MATCH(G2,A2:A7,0)),
300 x (1-5%) x (1-5%) x $25
H2* (1-VLOOKUP(G2,A2:F7,6,0)) * (1- VLOOKUP(G2,A2:F7,5,0)) * VLOOKUP(G2,A2:F7,4,0) ,

otherwise its less than discount 2 but greater than discount 1

H2* (1- VLOOKUP(G2,A2:F7,5,0)) * VLOOKUP(G2,A2:F7,4,0)



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