Formula to return an answer for 8 different date ranges

**ecuguy** · 06-26-2012, 02:28 PM

Hello, I was trying to come up with a formula that would take a list of dates and return back a value, 1st year, 2nd year, 3rd year, etc.
For example, Column A has a list of 10,000 or more dates ranging from Jan 2005 to May 2012. In column B, I would like to create a formula that would return what year range that date fell on. 03/01/2012 to 02/28/13 Year 1
03/01/2011 to 02/29/12 Year 2
03/01/2010 to 02/28/11 Year 3
03/01/2009 to 02/28/10 Year 4
03/01/2008 to 02/28/09 Year 5
03/01/2007 to 02/29/08 Year 6
03/01/2006 to 02/28/07 Year 7
03/01/2005 to 02/28/06 Year 8
So if the date in column A was 03/15/2010, the formula would return a value of 3, if the date in column A was 12/21/2007, the formula would return a value of 6.

Any help with this would be appreciated.
Thank you.

**NBVC** · 06-26-2012, 02:42 PM

If you place the lower bound dates in chronological ascending order, stating with the 2005 year... in say R1:R8, then the corresponding definitions, say in U1:U8

and say first date is in A2, try formula:

=LOOKUP(A2,$R$1:$U$8)

copied down