| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value when Date x is between Date y and z
Hi,
I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula something like {=IF(AND (X3A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick. Any suggestions welcome Thanks ...pc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value when Date x is between Date y and z
Hi,
try =VLOOKUP(x3,A1:C5,3) "PMC1" wrote: Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula something like {=IF(AND (X3A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick. Any suggestions welcome Thanks ...pc |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value when Date x is between Date y and z
On Jul 30, 1:13*pm, Eduardo wrote:
Hi, try =VLOOKUP(x3,A1:C5,3) "PMC1" wrote: Hi, I have a table something like this: * * * * * * A * * * * * * * * * * * *B * * * * * * * * C 1 * * * From * * * * * * * *To * * * * * * * * Result 2 * * * 01/07/2009 * * *01/07/2010 * * *0-1 3 * * * 01/07/2010 * * *01/07/2011 * * *1-2 4 * * * 01/07/2011 * * *01/07/2012 * * *2-3 5 * * * 01/07/2012 * * *01/07/2013 * * *3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula something like {=IF(AND (X3A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick. Any suggestions welcome Thanks ...pc Hi Eduardo, Thanks for your reply. I'm looking to return the value in column C where the the value of X3 is BETWEEN the value in the A and B so if the value if X3 is between the dates in A3 and B3 the this would return "1-2" ...pc |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value when Date x is between Date y and z
Hi,
The formula given is doing what you need if I enter 16/10/2010 it change to 1-2 "PMC1" wrote: On Jul 30, 1:13 pm, Eduardo wrote: Hi, try =VLOOKUP(x3,A1:C5,3) "PMC1" wrote: Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula something like {=IF(AND (X3A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick. Any suggestions welcome Thanks ...pc Hi Eduardo, Thanks for your reply. I'm looking to return the value in column C where the the value of X3 is BETWEEN the value in the A and B so if the value if X3 is between the dates in A3 and B3 the this would return "1-2" ...pc |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return a value when Date x is between Date y and z
On Jul 30, 1:42*pm, Eduardo wrote:
Hi, The formula given is doing what you need if I enter 16/10/2010 it change to 1-2 "PMC1" wrote: On Jul 30, 1:13 pm, Eduardo wrote: Hi, try =VLOOKUP(x3,A1:C5,3) "PMC1" wrote: Hi, I have a table something like this: * * * * * * A * * * * * * * * * * * *B * * * * * * * * C 1 * * * From * * * * * * * *To * * * * * * * * Result 2 * * * 01/07/2009 * * *01/07/2010 * * *0-1 3 * * * 01/07/2010 * * *01/07/2011 * * *1-2 4 * * * 01/07/2011 * * *01/07/2012 * * *2-3 5 * * * 01/07/2012 * * *01/07/2013 * * *3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula something like {=IF(AND (X3A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick.. Any suggestions welcome Thanks ...pc Hi Eduardo, Thanks for your reply. I'm looking to return the value in column C where the the value of X3 is BETWEEN the value in the A and B so if the value if X3 is between the dates in A3 and B3 the this would return "1-2" ...pc Eduardo, you are indeed correct - I didn't think about the vlookup using "approx match". Thanks for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| from date return week date range | Excel Worksheet Functions | |||
| return a date from range, date is between dates in two other cells | Excel Discussion (Misc queries) | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
| calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
Hybrid Mode
