Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=VLOOKUP
Hi John,
I want 0.45% Cheers, G |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=VLOOKUP
Do I need to make cells in my lookup sheet % cells? Or is the % on
the end enough? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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! :) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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! :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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! :) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
=VLOOKUP
You my friend are a god :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |