Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup: source is specific and table is a range
my template calculates a specific value, ie 1,357 or 12,789. The rates I
apply to these values are based on the range that the value falls into, ie 0 < 1,500 0.145 1.5<2.5M 0.130 2.5 < 4M 0.106 4M < 6M 0.089 6M < 10M 0.077 10M < 15M 0.065 15M < Avg. 0.054 20M < Open 0.051 I can't get a vlookup to work without using the lowest and highest value of each rate range: 1 0.170 1499 0.170 1500 0.153 2499 0.153 2,500 0.124 3999 0.124 4,000 0.107 5999 0.107 6,000 0.094 9999 0.094 10,000 0.080 14999 0.080 15,000 0.068 19999 0.068 20,000 0.064 How can I write my vlookup formula (or other applicable formula) to read my table as shown in my first example. Thanks much, Andy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup: source is specific and table is a range
Are you using the fourth argument of VLOOKUP? ie
=VLOOKUP(A1,B1:B1000,2,FALSE) FALSE gives an exact match, TRUE or the forth argument omitted gives the nearest match. Regards, Alan. "AJB" wrote in message ... my template calculates a specific value, ie 1,357 or 12,789. The rates I apply to these values are based on the range that the value falls into, ie 0 < 1,500 0.145 1.5<2.5M 0.130 2.5 < 4M 0.106 4M < 6M 0.089 6M < 10M 0.077 10M < 15M 0.065 15M < Avg. 0.054 20M < Open 0.051 I can't get a vlookup to work without using the lowest and highest value of each rate range: 1 0.170 1499 0.170 1500 0.153 2499 0.153 2,500 0.124 3999 0.124 4,000 0.107 5999 0.107 6,000 0.094 9999 0.094 10,000 0.080 14999 0.080 15,000 0.068 19999 0.068 20,000 0.064 How can I write my vlookup formula (or other applicable formula) to read my table as shown in my first example. Thanks much, Andy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup: source is specific and table is a range
specifically, I cannot get '3,456 to match up to the range 2,500 - 4,000
without displaying 2,500 : 0.124 3999 : 0.124 I am trying to eliminate visual confusion by using one label for each rate size: 2,500 - 4,000 : 0.124 Sorry if this is not very clear, thanks for your assistance Alan, Andy "Alan" wrote: Are you using the fourth argument of VLOOKUP? ie =VLOOKUP(A1,B1:B1000,2,FALSE) FALSE gives an exact match, TRUE or the forth argument omitted gives the nearest match. Regards, Alan. "AJB" wrote in message ... my template calculates a specific value, ie 1,357 or 12,789. The rates I apply to these values are based on the range that the value falls into, ie 0 < 1,500 0.145 1.5<2.5M 0.130 2.5 < 4M 0.106 4M < 6M 0.089 6M < 10M 0.077 10M < 15M 0.065 15M < Avg. 0.054 20M < Open 0.051 I can't get a vlookup to work without using the lowest and highest value of each rate range: 1 0.170 1499 0.170 1500 0.153 2499 0.153 2,500 0.124 3999 0.124 4,000 0.107 5999 0.107 6,000 0.094 9999 0.094 10,000 0.080 14999 0.080 15,000 0.068 19999 0.068 20,000 0.064 How can I write my vlookup formula (or other applicable formula) to read my table as shown in my first example. Thanks much, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table as Source Range | Excel Discussion (Misc queries) | |||
Pivot Table - Efficient Source Range | Excel Discussion (Misc queries) | |||
Using cursor keys to select Pivot Table Source Data Range | Excel Discussion (Misc queries) | |||
Pivot Table Source Worksheet and Range | Excel Discussion (Misc queries) | |||
Increasing the Source Data range for an existing Pivot Table | Excel Worksheet Functions |