Return a value when Date x is between Date y and z

[PMC1]

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
(X3>A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick.

Any suggestions welcome

Thanks

...pc

 

[Eduardo]

Hi,
try

=VLOOKUP(x3,A1:C5,3)

 

[PMC1]

Hi,
try

=VLOOKUP(x3,A1:C5,3)

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]

Hi,
The formula given is doing what you need if I enter 16/10/2010 it change to
1-2

 

[PMC1]

Hi,
The formula given is doing what you need if I enter 16/10/2010 it change to
1-2

Eduardo, you are indeed correct - I didn't think about the vlookup
using "approx match".

Thanks for your help