| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I return a value between two other values from a table
Col A Col B Col C
Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
With your dollar amounts in column E and the percentage in F and the lookup
value in A1 try this. =VLOOKUP(A1,E1:F,2,1) HTH Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
Woops, to clarify... only use the dollar amounts of the upper value. So it
would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the lower limits. Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
Hi Biff,
Could you send me a worksheet with your solution, I'm a bit confused with your solution... albeit from a pro I know it's on target. Seems mine worked with the upper limits in my test...? Thanks, Howard "T. Valko" wrote in message ... You had it right the first time! *Do use* the lower limits. E1 = 9001 =VLOOKUP(E1,A1:C6,3) Result = 15% -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Woops, to clarify... only use the dollar amounts of the upper value. So it would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the lower limits. Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
Here's a link to a sample file:
xSampleLookup.xls 14kb http://cjoint.com/?griaEtaxYD The lookup value in cell E1 is a formula generated random number from 1,000 to 100,000. Press F9 to generate a new number. -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Hi Biff, Could you send me a worksheet with your solution, I'm a bit confused with your solution... albeit from a pro I know it's on target. Seems mine worked with the upper limits in my test...? Thanks, Howard "T. Valko" wrote in message ... You had it right the first time! *Do use* the lower limits. E1 = 9001 =VLOOKUP(E1,A1:C6,3) Result = 15% -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Woops, to clarify... only use the dollar amounts of the upper value. So it would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the lower limits. Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
Thanks, Biff
"T. Valko" wrote in message ... Here's a link to a sample file: xSampleLookup.xls 14kb http://cjoint.com/?griaEtaxYD The lookup value in cell E1 is a formula generated random number from 1,000 to 100,000. Press F9 to generate a new number. -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Hi Biff, Could you send me a worksheet with your solution, I'm a bit confused with your solution... albeit from a pro I know it's on target. Seems mine worked with the upper limits in my test...? Thanks, Howard "T. Valko" wrote in message ... You had it right the first time! *Do use* the lower limits. E1 = 9001 =VLOOKUP(E1,A1:C6,3) Result = 15% -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Woops, to clarify... only use the dollar amounts of the upper value. So it would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the lower limits. Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do I return a value between two other values from a table
You're welcome!
-- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message ... Thanks, Biff "T. Valko" wrote in message ... Here's a link to a sample file: xSampleLookup.xls 14kb http://cjoint.com/?griaEtaxYD The lookup value in cell E1 is a formula generated random number from 1,000 to 100,000. Press F9 to generate a new number. -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Hi Biff, Could you send me a worksheet with your solution, I'm a bit confused with your solution... albeit from a pro I know it's on target. Seems mine worked with the upper limits in my test...? Thanks, Howard "T. Valko" wrote in message ... You had it right the first time! *Do use* the lower limits. E1 = 9001 =VLOOKUP(E1,A1:C6,3) Result = 15% -- Biff Microsoft Excel MVP "L. Howard Kittle" wrote in message . .. Woops, to clarify... only use the dollar amounts of the upper value. So it would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the lower limits. Regards, Howard "P Forbes" <P wrote in message ... Col A Col B Col C Row1 $1,000.00 $2,500.00 10% Row2 $2,501.00 $5,000.00 12% Row3 $5,001.00 $10,000.00 15% Row4 $10,001.00 $15,000.00 18% Row5 $15,001.00 $20,000.00 21% Row6 $20,001.00 $99,999.00 24% Here's what I have. I need to return the percentage for a value greater than or equal to Col A and less than or equal to column B. For example, if the lookup amount is $9,001, the percentage should be 15%. I'm not looking for an exact match, but a range between column A & B that will return the value in column C. Any help is greatly appreciated! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
| find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
| Pivot Table (vlookup 2 column text values, return 1 value) | Excel Discussion (Misc queries) | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
Linear Mode
