Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
Driving me mad!
I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
Try to use this formula as search value (1st argument) in VLOOKUP:
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi €žKneeDown2Up€ť ezt Ă*rta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
Many thanks for getting back to me Stefi. The formula works on its own fine,
but when I put it within the vlookup statement it returns a "Ref#" error. This is what I have; =VLOOKUP(DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),mar06,2) mar06 being a named list which has the equivelant dates. The cells are all formatted as dates and the list is in ascending order. Is there something I'm doing wrong here? Thanks "Stefi" wrote: Try to use this formula as search value (1st argument) in VLOOKUP: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi €žKneeDown2Up€ť ezt Ă*rta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
You are trying to retrieve data from the second column of the named
range mar06 - is this defined as being 2 columns wide? Hope this helps. Pete On Mar 30, 3:58 pm, KneeDown2Up wrote: Many thanks for getting back to me Stefi. The formula works on its own fine, but when I put it within the vlookup statement it returns a "Ref#" error. This is what I have; =VLOOKUP(DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),mar06,2) mar06 being a named list which has the equivelant dates. The cells are all formatted as dates and the list is in ascending order. Is there something I'm doing wrong here? Thanks "Stefi" wrote: Try to use this formula as search value (1st argument) in VLOOKUP: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi ,,KneeDown2Up" ezt írta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
Ok, panic over - sorted! I had exported a (huge) list of data from another
application into the spreadsheet. It turns out (and thanks to Stefi, I came across it) that the dates weren't changing when I tried formatting them into reverse order, i.e. 2007,03,01. Why they are like that is beyond me and why I can't change their formatting is annoying! I now have to manually change all the dates to enable this to work! (pc's and time eh?!!) Thanks again Stefi, it works great now. "KneeDown2Up" wrote: Many thanks for getting back to me Stefi. The formula works on its own fine, but when I put it within the vlookup statement it returns a "Ref#" error. This is what I have; =VLOOKUP(DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),mar06,2) mar06 being a named list which has the equivelant dates. The cells are all formatted as dates and the list is in ascending order. Is there something I'm doing wrong here? Thanks "Stefi" wrote: Try to use this formula as search value (1st argument) in VLOOKUP: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi €žKneeDown2Up€ť ezt Ă*rta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
Thanks Pete - yes the list is 2 columns wide - all working now - see previous
post. "Pete_UK" wrote: You are trying to retrieve data from the second column of the named range mar06 - is this defined as being 2 columns wide? Hope this helps. Pete On Mar 30, 3:58 pm, KneeDown2Up wrote: Many thanks for getting back to me Stefi. The formula works on its own fine, but when I put it within the vlookup statement it returns a "Ref#" error. This is what I have; =VLOOKUP(DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),mar06,2) mar06 being a named list which has the equivelant dates. The cells are all formatted as dates and the list is in ascending order. Is there something I'm doing wrong here? Thanks "Stefi" wrote: Try to use this formula as search value (1st argument) in VLOOKUP: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi ,,KneeDown2Up" ezt Ă*rta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and dates!
If you post the format of your date cells and how they exactly look like,
we'll probably find a method to avoid manual manipulation of a huge amount of data. Stefi €žKneeDown2Up€ť ezt Ă*rta: Ok, panic over - sorted! I had exported a (huge) list of data from another application into the spreadsheet. It turns out (and thanks to Stefi, I came across it) that the dates weren't changing when I tried formatting them into reverse order, i.e. 2007,03,01. Why they are like that is beyond me and why I can't change their formatting is annoying! I now have to manually change all the dates to enable this to work! (pc's and time eh?!!) Thanks again Stefi, it works great now. "KneeDown2Up" wrote: Many thanks for getting back to me Stefi. The formula works on its own fine, but when I put it within the vlookup statement it returns a "Ref#" error. This is what I have; =VLOOKUP(DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)),mar06,2) mar06 being a named list which has the equivelant dates. The cells are all formatted as dates and the list is in ascending order. Is there something I'm doing wrong here? Thanks "Stefi" wrote: Try to use this formula as search value (1st argument) in VLOOKUP: =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) Regards, Stefi €žKneeDown2Up€ť ezt Ă*rta: Driving me mad! I am trying to use a vlookup (to another sheet) that has dates from 2005/6 - I need to match corresponding dates from this year to previous years, i.e. 1/3/07 in sheet 1 needs to return the data next to the corresponding date in the previous year's column(2). Column D (sheet 2) Dates Column E (sheet 2) Values Column A Sheet 1 Dates Column B sheet 1 Values Column E sheet 1 Vlookup formula. I have tried various permeatations of formula to no avail, such as Vlooup(left(a3),left(mar06),2) It doesn't seem to like the Left function. I've also tried the DAY function, also to no avail. Thanks for any pointers. Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting dates with Vlookup | Excel Discussion (Misc queries) | |||
Deleting dates and vlookup | Excel Discussion (Misc queries) | |||
dates and vlookup | Excel Worksheet Functions | |||
VLOOKUP calendar dates | Excel Discussion (Misc queries) | |||
VLookUp with Dates | Excel Worksheet Functions |