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

 

[Bernard Liengme]

Experiment with =VLOOKUP(X3,$A$2:$I$5,3,TRUE)
I think this does what you want
best wishes