VLOOKUP 1

[ADE6]

Hi all

I have amounts in column C that need to be multiplied by a certain percentage to give profit figures which need to be shown in column D.

The percentage to be used in the calculation depends upon the range that the value in column C appears.

---Col F----------------Col G
>0<=500,000 15%
>500,000<=1000000 12%
>1000000<=3000000 10%
>3000000<=6000000 9%
>6000000<=10000000 8%
>10000000<=20000000 7%

So if the values in Column C are as shown below,the first value 393,160 lies between >0<=500,000,so column D would equal 393,160*15%

The fifth value in column C 552,361 lies between >500,000<=1000000,so column D would equal 552,361*12%


Col C
393,160
393,160
440,339
493,179
552,361
618,644
692,882
776,027
869,151
973,449
1,090,262
1,221,094
1,367,625

I know the formula below would work if i was looking for an exact match,how can i modify this so that i can find values that fall into a range.

=VLOOKUP(C1,F1:G6,2,FALSE)

Thanks for the ideas

Ade

=VLOOKUP(C1,F1:G6,2,FALSE)

 

[Zathras]

This is the time for the fourth parameter to be TRUE. Look in the help file for more assistance.

 

[johnnygeo]

just for fun, you can do this without vlookup:

        colA    colB    colC
row1    min     max     mult
row2    0       500     0.15
row3    500     1000    0.12
row4    1000    2500    0.12
row5    2500    3500    0.12
row6    3500    5000    0.12

if your input value is in D1, then your output formula could be:
=SUM(C2:C6)-SUMIF(A2:A6,">" & D1,C2:C6)-SUMIF(B2:B6,"<=" & D1,C2:C6)

 

[AaronKamp]

=VLOOKUP(C1,$F$1:$G$12,2,TRUE)

For this to work, I changed how the range was listed in the following manner:
------F------
0
500000
500000.001
1000000
1000000.001
3000000
3000000.001
6000000
6000000.001
10000000
10000000.001
20000000
---G---
0.15
0.15
0.12
0.12
0.1
0.1
0.9
0.9
0.8
0.8
0.7
0.7