Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula?
I a parcel value (real estate) that will be typed into a cell (22,500). I
need this value to look at a table and determine what the the closest value preceding and following it will be such as 20,000 (precediing) and 25,000 (following). -- Janette |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula?
VLOOKUP may be the best option for you:
Look in the Excel help menu: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) Lookup_value The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. Table_array Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is: Less than 1, VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value. Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Also look at ROUND, CEILING, and FLOOR: B2 = 29000 C1:C12 = 5,000 to 60,000, in increments of 5,000 A1 = CEILING(VLOOKUP(B1,C1:C12,1),10000) =30,000 A1 =CEILING(VLOOKUP(B1,C1:C12,1),1) =25,000 Regards, Ryan--- -- RyGuy "Janette" wrote: I a parcel value (real estate) that will be typed into a cell (22,500). I need this value to look at a table and determine what the the closest value preceding and following it will be such as 20,000 (precediing) and 25,000 (following). -- Janette |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|