=VLOOKUP
 

Hello folks,

Someone from accounts presented me with am interesting VLOOKUP problem
(interesting because I don't fully understand Excel).

I have a colum of figures from B6 to B38 I want to present the data in
D6 to D38 I would like to apply a % from another worksheet F6 to F8.
The % to be applied varies depending on the amount in B6 to B38

** EXAMPLE.

B6 to B38

12,191,984
12,497,627
10,854,088
10,628,373
9,950,800
9,667,445
10,129,664
10,308,847
10,629,769
10,413,796
10,358,897
10,367,604
10,673,805
10,274,035
9,977,234
10,277,299
9,910,732
10,104,963
10,318,960
9,952,196
10,079,331
10,197,333
10,355,460
10,362,925
9,802,999
10,005,238
9,986,293
9,166,972
9,513,328
9,927,705
10,279,043
10,059,406
11,169,842

% to be applied.

0 8,499,999 0.45%
8,500,000 9,999,999 0.35%
10,000,000 to 19,999,999 0.25%
20,000,000 to 39,999,999 0.15%

Make sense? I know what I want to do in my head but I don't know how
to make this VLOOKUP do it.

Can someone assisnt me?

Thank you :)

=VLOOKUP
 

Display your % like this :

0 0.45%
8500000 0.35%
10000000 0.25%
20000000 0.15%
40000000 0.00%

from F6 to G10 on Sheet2.

Then, for cell A6, use :
=A6*VLOOKUP(A6,Sheet2!F6:G10,2,1)
HTH
Daniel
Hello folks,

Someone from accounts presented me with am interesting VLOOKUP problem
(interesting because I don't fully understand Excel).

I have a colum of figures from B6 to B38 I want to present the data in
D6 to D38 I would like to apply a % from another worksheet F6 to F8.
The % to be applied varies depending on the amount in B6 to B38

** EXAMPLE.

B6 to B38

12,191,984
12,497,627
10,854,088
10,628,373
9,950,800
9,667,445
10,129,664
10,308,847
10,629,769
10,413,796
10,358,897
10,367,604
10,673,805
10,274,035
9,977,234
10,277,299
9,910,732
10,104,963
10,318,960
9,952,196
10,079,331
10,197,333
10,355,460
10,362,925
9,802,999
10,005,238
9,986,293
9,166,972
9,513,328
9,927,705
10,279,043
10,059,406
11,169,842

% to be applied.

0 8,499,999 0.45%
8,500,000 9,999,999 0.35%
10,000,000 to 19,999,999 0.25%
20,000,000 to 39,999,999 0.15%

Make sense? I know what I want to do in my head but I don't know how
to make this VLOOKUP do it.

Can someone assisnt me?

Thank you :)

=VLOOKUP
 

Hi,
Do you want .45 or .0045?
..45% will be X*.0045


John


wrote in message ...
Hello folks,

Someone from accounts presented me with am interesting VLOOKUP problem
(interesting because I don't fully understand Excel).

I have a colum of figures from B6 to B38 I want to present the data in
D6 to D38 I would like to apply a % from another worksheet F6 to F8.
The % to be applied varies depending on the amount in B6 to B38

** EXAMPLE.

B6 to B38

12,191,984
12,497,627
10,854,088
10,628,373
9,950,800
9,667,445
10,129,664
10,308,847
10,629,769
10,413,796
10,358,897
10,367,604
10,673,805
10,274,035
9,977,234
10,277,299
9,910,732
10,104,963
10,318,960
9,952,196
10,079,331
10,197,333
10,355,460
10,362,925
9,802,999
10,005,238
9,986,293
9,166,972
9,513,328
9,927,705
10,279,043
10,059,406
11,169,842

% to be applied.

0 8,499,999 0.45%
8,500,000 9,999,999 0.35%
10,000,000 to 19,999,999 0.25%
20,000,000 to 39,999,999 0.15%

Make sense? I know what I want to do in my head but I don't know how
to make this VLOOKUP do it.

Can someone assisnt me?

Thank you :)

=VLOOKUP
 

Hi John,

I want 0.45%

Cheers,

G

=VLOOKUP
 

Do I need to make cells in my lookup sheet % cells? Or is the % on
the end enough?

=VLOOKUP
 

=A6*VLOOKUP(A6,Sheet2!F6:G10,2,1)

Why is there a G in there?

I'll tell you where my data is:-

The figures are in cells B6 to B38 I want the answer to display in D6
to D38

The % charges are in a sheet called Charging Bands and they are F6 to
F9

Thanks! :)

=VLOOKUP
 

You need a column for the amounts (F6:F10) and one for the % (G6:G10).
Have a look at an examplefile at :
http://www.filedropper.com/grumblenoise
Daniel

=A6*VLOOKUP(A6,Sheet2!F6:G10,2,1)

Why is there a G in there?

I'll tell you where my data is:-

The figures are in cells B6 to B38 I want the answer to display in D6
to D38

The % charges are in a sheet called Charging Bands and they are F6 to
F9

Thanks! :)

=VLOOKUP
 

Sorry preceding example contains macros; use this one :
http://www.filedropper.com/grumblenoise_1
Daniel

=A6*VLOOKUP(A6,Sheet2!F6:G10,2,1)

Why is there a G in there?

I'll tell you where my data is:-

The figures are in cells B6 to B38 I want the answer to display in D6
to D38

The % charges are in a sheet called Charging Bands and they are F6 to
F9

Thanks! :)

=VLOOKUP
 

You my friend are a god :)