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

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

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

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

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