Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and rounding
1,000 $2
2,000 $2.50 3,000 $2.75 4,000 $2.89 When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75 By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and rounding
On Wed, 20 Jan 2010 13:53:02 -0800, excelrookie
wrote: 1,000 $2 2,000 $2.50 3,000 $2.75 4,000 $2.89 When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75 By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? TIA Try the CEILING formula. Replace K15 with =CEILING(K15,1000) Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and rounding
"excelrookie" wrote:
By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? Ostensibly: =VLOOKUP(ROUNDUP(K15,-3),Information!AE2:AF1001,2) But perhaps your table could be constructed differently, to wit: 0 $2 1000 $2.50 2000 $2.75 3000 $2.89 If you want a lookup of 1000 to return $2 instead of $2.50, change 1000 in the table to something appropriate. "Something appropriate" depends on the precision that you need. For example, if the lookup value is an integer, then: 0 $2 1001 $2.50 2001 $2.75 3001 $2.89 If the lookup value has a precision of 2 decimal places, then: 0 $2 1000.01 $2.50 2000.01 $2.75 3000.01 $2.89 Each of these solutions, including your original method, has it pros and cons. ----- original message ----- "excelrookie" wrote: 1,000 $2 2,000 $2.50 3,000 $2.75 4,000 $2.89 When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75 By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? TIA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and rounding
Thanks Joe, The first one did it!! I didn't create the table I am using and
it has 1,000's of entries so I would prefer not to change them all :) "Joe User" wrote: "excelrookie" wrote: By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? Ostensibly: =VLOOKUP(ROUNDUP(K15,-3),Information!AE2:AF1001,2) But perhaps your table could be constructed differently, to wit: 0 $2 1000 $2.50 2000 $2.75 3000 $2.89 If you want a lookup of 1000 to return $2 instead of $2.50, change 1000 in the table to something appropriate. "Something appropriate" depends on the precision that you need. For example, if the lookup value is an integer, then: 0 $2 1001 $2.50 2001 $2.75 3001 $2.89 If the lookup value has a precision of 2 decimal places, then: 0 $2 1000.01 $2.50 2000.01 $2.75 3000.01 $2.89 Each of these solutions, including your original method, has it pros and cons. ----- original message ----- "excelrookie" wrote: 1,000 $2 2,000 $2.50 3,000 $2.75 4,000 $2.89 When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75 By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? TIA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and rounding
Hi,
Sort the range (assumed in C7:D10) in descending order of column C. Cell C14 has 2500. You could then try this formula. =INDEX($C$7:$D$10,MATCH(C14,C7:C10,-1),2) There are more formula based approached but this is a simple one -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "excelrookie" wrote in message ... 1,000 $2 2,000 $2.50 3,000 $2.75 4,000 $2.89 When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75 By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the "2,500". How can I make K15 round up to the next thousand so I will get 2.75? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and rounding | New Users to Excel | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
Vlookup rounding up rather than down | Excel Worksheet Functions | |||
Vlookup Rounding | Excel Discussion (Misc queries) | |||
Rounding criteria within a nested vlookup and hlookup | Excel Worksheet Functions |