Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
I am trying to use dates in a vlookup table and then looking up its value.
For example I have a date range from 01-01-07 to 01-28-07 and the value to be looked up is 01, the next range is 01-29-07 to 02-25-07, and value 02, the next range is 02-26-07 to 04-01-07, value is 3. However, when I have the vlookup look at the date 03-26-07 it shows the value as 02. Anyone know what I am doing wrong. The table is 01/01/07 AP017 01/28/07 AP017 02/25/07 AP027 04/01/07 AP037 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
You need to show us the formula you are using. Important detail! :)
Tyro "hmteacher" wrote in message ... I am trying to use dates in a vlookup table and then looking up its value. For example I have a date range from 01-01-07 to 01-28-07 and the value to be looked up is 01, the next range is 01-29-07 to 02-25-07, and value 02, the next range is 02-26-07 to 04-01-07, value is 3. However, when I have the vlookup look at the date 03-26-07 it shows the value as 02. Anyone know what I am doing wrong. The table is 01/01/07 AP017 01/28/07 AP017 02/25/07 AP027 04/01/07 AP037 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
Think you just need to re-define your ascending sort vlookup table like this
1-Jan-07 AP017 29-Jan-07 AP027 26-Feb-07 AP037 1-Apr-07 AP047 .... then your vlookup* on the date: 26-Mar-07 will return the correct: AP037 *example in E1: =VLOOKUP(D1,A:B,2,TRUE) where D1 contains the lookup date and the lookup table is in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "hmteacher" wrote: I am trying to use dates in a vlookup table and then looking up its value. For example I have a date range from 01-01-07 to 01-28-07 and the value to be looked up is 01, the next range is 01-29-07 to 02-25-07, and value 02, the next range is 02-26-07 to 04-01-07, value is 3. However, when I have the vlookup look at the date 03-26-07 it shows the value as 02. Anyone know what I am doing wrong. The table is 01/01/07 AP017 01/28/07 AP017 02/25/07 AP027 04/01/07 AP037 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
I found the problem. I needed to use the start date of the date range. I
was using the end date of the date range. Thanks for the input. "Max" wrote: Think you just need to re-define your ascending sort vlookup table like this 1-Jan-07 AP017 29-Jan-07 AP027 26-Feb-07 AP037 1-Apr-07 AP047 ... then your vlookup* on the date: 26-Mar-07 will return the correct: AP037 *example in E1: =VLOOKUP(D1,A:B,2,TRUE) where D1 contains the lookup date and the lookup table is in cols A and B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "hmteacher" wrote: I am trying to use dates in a vlookup table and then looking up its value. For example I have a date range from 01-01-07 to 01-28-07 and the value to be looked up is 01, the next range is 01-29-07 to 02-25-07, and value 02, the next range is 02-26-07 to 04-01-07, value is 3. However, when I have the vlookup look at the date 03-26-07 it shows the value as 02. Anyone know what I am doing wrong. The table is 01/01/07 AP017 01/28/07 AP017 02/25/07 AP027 04/01/07 AP037 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
I found the problem. I needed to use the start date of the date range. I
was using the end date of the date range. Thanks for the input. "Tyro" wrote: You need to show us the formula you are using. Important detail! :) Tyro "hmteacher" wrote in message ... I am trying to use dates in a vlookup table and then looking up its value. For example I have a date range from 01-01-07 to 01-28-07 and the value to be looked up is 01, the next range is 01-29-07 to 02-25-07, and value 02, the next range is 02-26-07 to 04-01-07, value is 3. However, when I have the vlookup look at the date 03-26-07 it shows the value as 02. Anyone know what I am doing wrong. The table is 01/01/07 AP017 01/28/07 AP017 02/25/07 AP027 04/01/07 AP037 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using dates in the lookup range
Glad you got it sorted out over there.
Thanks for feeding back. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "hmteacher" wrote in message ... I found the problem. I needed to use the start date of the date range. I was using the end date of the date range. Thanks for the input. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup problem in lookup range | Excel Worksheet Functions | |||
Lookup based on range of dates | Excel Worksheet Functions | |||
lookup/vlookup for dates, please help! | Excel Worksheet Functions | |||
VLOOKUP range lookup | Excel Worksheet Functions | |||
Range Lookup in VLookup ?'s | Excel Worksheet Functions |