Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup(date, range,3)
Hello Group,
I am doing a vlookup on a date, I will take the closest date, so have not used the last parameter. It returns the last date in the list always. Both the date in the Lookup and in the table are type = 1, a number. Am I missing something here or is it not possible to do this? Thanks, David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup(date, range,3)
I will take the closest date, so have not used the last parameter.
vlookup(date, range,3) It returns the last date in the list always. When you omit the range_lookup argument the table_array *must* be sorted by the date in ascending order. The "closest" date will be the closest date that is less than or equal to the lookup date. For example: 1/1/2010...10 1/5/2010...25 1/8/2010...42 If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and the result would be 25. 1/5/2010 is the closest date that is *less than or equal to* the lookup date 1/7/2010. Is that what you had in mind? -- Biff Microsoft Excel MVP "David" wrote in message ... Hello Group, I am doing a vlookup on a date, I will take the closest date, so have not used the last parameter. It returns the last date in the list always. Both the date in the Lookup and in the table are type = 1, a number. Am I missing something here or is it not possible to do this? Thanks, David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup(date, range,3)
Thanks. I changed it to "false" and it worked.
"T. Valko" wrote: I will take the closest date, so have not used the last parameter. vlookup(date, range,3) It returns the last date in the list always. When you omit the range_lookup argument the table_array *must* be sorted by the date in ascending order. The "closest" date will be the closest date that is less than or equal to the lookup date. For example: 1/1/2010...10 1/5/2010...25 1/8/2010...42 If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and the result would be 25. 1/5/2010 is the closest date that is *less than or equal to* the lookup date 1/7/2010. Is that what you had in mind? -- Biff Microsoft Excel MVP "David" wrote in message ... Hello Group, I am doing a vlookup on a date, I will take the closest date, so have not used the last parameter. It returns the last date in the list always. Both the date in the Lookup and in the table are type = 1, a number. Am I missing something here or is it not possible to do this? Thanks, David . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup(date, range,3)
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "David" wrote in message ... Thanks. I changed it to "false" and it worked. "T. Valko" wrote: I will take the closest date, so have not used the last parameter. vlookup(date, range,3) It returns the last date in the list always. When you omit the range_lookup argument the table_array *must* be sorted by the date in ascending order. The "closest" date will be the closest date that is less than or equal to the lookup date. For example: 1/1/2010...10 1/5/2010...25 1/8/2010...42 If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and the result would be 25. 1/5/2010 is the closest date that is *less than or equal to* the lookup date 1/7/2010. Is that what you had in mind? -- Biff Microsoft Excel MVP "David" wrote in message ... Hello Group, I am doing a vlookup on a date, I will take the closest date, so have not used the last parameter. It returns the last date in the list always. Both the date in the Lookup and in the table are type = 1, a number. Am I missing something here or is it not possible to do this? Thanks, David . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup using match and date range | Excel Discussion (Misc queries) | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
finding a max date in a range in vlookup data | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |