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

MXL

Board Regular
Joined
Jun 15, 2010
Messages
67
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.

ABCDEFGHIJ
1Item ListUnits Discount 1Units Discount 2Wholesale CostDiscount Rate 1Discount Rate 2Item PurchasedTotal UnitsPer UnitTotal Cost
2Bellen100250$255.0%5.0%Bellen300 $25#REF!
3Carlota100200$255.0%5.0%
4Majestic50150$5010.0%15.0%
5Sunset75150$7515.0%10.0%
6Phenolic150250$205.0%10.0%
7Zapper200350$155.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,<>
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,388
Office Version
  1. 365
Platform
  1. MacOS
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top