Vlookup cumulative total

[Candyland25]

Hello,
I'm trying to write a vlookup with a combination of doing cumulative with some multiplication. Here is my example:

PC Cost: $500
10% Maintenance Fee
15% Setup Fee
5% Warranty Fee

Maintenance fee would be $50 (500*.10).
Setup Fee would be $82.50 since it's cumulative of (500+50)*.15
Warranty Fee would be $31.63 since it's cumulative of (500+50+82.50)*.05

Total cost would be $664.13

Since I would be looking for certain items cost (PC, Monitors etc.) I need to do a vlookup formula to pull this information in from a separate sheet.

So here is how far I have gotten: =IFERROR(VLOOKUP(A1,'Items'!$B$4:$AP$20,4,0) + VLOOKUP(A1,'Items'!$B$4:$AP$20,4,0)*Fee!A2,0)
This gives $550 but I try to follow the same logic to do the rest of the calculation the formula breaks.

How do i get cumulative of (500+50)*.15 using vlookup?

Thank you so much.

 

[Laurence83]

Hi,

I've found this forum very helpful, so I'm glad there is one I can help with for a change!

Have a go with this formula (just replace the VlookupItemX with the appropriate vlookup)

=VlookupItem1*(1+VlookupItem2)*(1+VlookupItem3)*(1+VlookupItem4)

Laurence

 

[portews]

If the percentages stay the same, you can calculate them in the formula and all you need to return with the Vlookup is the cost, correct? Assuming the Vlookup you show returns the $500 cost, you then multiply it by 1.1 for the 10% (1 for the cost of the PC and .1 for the 10%), 1.15 for 15% and 1.05 for the warranty.

=VLOOKUP(A1,'Items'!$B$4:$AP$20,4,0)*1.1*1.15*1.05

 

[Candyland25]

If the percentages stay the same, you can calculate them in the formula and all you need to return with the Vlookup is the cost, correct? Assuming the Vlookup you show returns the $500 cost, you then multiply it by 1.1 for the 10% (1 for the cost of the PC and .1 for the 10%), 1.15 for 15% and 1.05 for the warranty.

=VLOOKUP(A1,'Items'!$B$4:$AP$20,4,0)*1.1*1.15*1.05

Awesome! Thank you. This worked