Excel Formula Help : IF + Vlookup + Sum

[madracer]

Hi Guys,

New to the forum, and must commend the great help its been over the years, but ahve a question which i cannot find teh answer to, regarding an IF + Vlookup + Sum Formula.

Here is the sheet i am working with,

Cost Table Day 1 to 3 Day 4 to 8 Day 8 or Greater
Adam $10 $20 $30
Ben $15 $30 $45
Chris $8 $17 $28
Dave $14 $29 $32
Ed $25 $28 $35
Fay $7 $17 $24
Gary $11 $21 $31
Harry $13 $22 $36

Days Worked Cost
Adam 2 ?
Harry 5 ?
Fay 9 ?
Ben 15 ?
Adam 22 ?
Gary 7 ?
Dave 4 ?
Chris 8 ?
Ed 3 ?



I need a forumla, that will be able to calculate the cost related to each person, based on the specific cost per day from the Cost Table.

So for example, if we take the second item, Harry.
The forumla, should look up the name Harry in the Cost table, then detect that the days worked for him is 5, then perform a calculation for his cost. ie. 3 days x $13 and 2 days x $22.

Have attached the excel file for better viewing.

Hope that is clear enough to assist me.

Thanks in advance.

[NBVC]

Try:

=MIN(B12,3)*VLOOKUP(A12,$A$2:$D$9,2,0)+IF(B12>3,MIN(B12-3,5)*VLOOKUP(A12,$A$2:$D$9,3,0),0)+IF(B12>8,(B12-8)*VLOOKUP(A12,$A$2:$D$9,4,0),0)

copied down.

or if your names are alphabetically ascendingly listed as per sample, then

=MIN(B12,3)*LOOKUP(A12,$A$2:$B$9)+IF(B12>3,MIN(B12-3,5)*LOOKUP(A12,$A$2:$C$9),0)+IF(B12>8,(B12-8)*LOOKUP(A12,$A$2:$D$9),0)

[madracer]

Thanks Works like the bomb!

Great help, and super fast!