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

M

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.

[TABLE="width: 844"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item List[/TD]
[TD="align: center"]Units Discount 1[/TD]
[TD="align: center"]Units Discount 2[/TD]
[TD="align: center"]Wholesale Cost[/TD]
[TD="align: center"]Discount Rate 1[/TD]
[TD="align: center"]Discount Rate 2[/TD]
[TD="align: center"]Item Purchased[/TD]
[TD="align: center"]Total Units[/TD]
[TD="align: center"]Per Unit[/TD]
[TD="align: center"]Total Cost[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Bellen[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]$25[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]Bellen[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"] $25[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Carlota[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]$25[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Majestic[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]$50[/TD]
[TD="align: center"]10.0%[/TD]
[TD="align: center"]15.0%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Sunset[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]$75[/TD]
[TD="align: center"]15.0%[/TD]
[TD="align: center"]10.0%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Phenolic[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]$20[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]10.0%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Zapper[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]350[/TD]
[TD="align: center"]$15[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]10.0%[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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,<>
 
Sort by date Sort by votes
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:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Index Match with multiple conditions including dates
Replies
1
Views
275
StephenCrump
S
W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
858
wsr429
W
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
561
Alex Blakenburg
A
A
  • Question Question
Abstract the Quantity Consumption
Replies
1
Views
139
Rijnsent
Rijnsent
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
371
RobP
R

Share this page

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
Back
Top