Excel date intervals look up

[adam smith]

Hi Im trying to lookup a value based on date intervals for academic years, using birthday to find the year.

So for example if I enter a birthday of 22/09/1997 then it will bring me the name of the academic year.

Please I appreciate any help. below is my table to look up.

01/09/2005 31/08/2006 Reception
01/09/2004 31/08/2005 Reception
01/09/2003 31/08/2004 Year 1
01/09/2002 31/08/2003 Year 2
Juniors
01/09/2001 31/08/2002 Year 3
01/09/2000 31/08/2001 Year 4
01/09/1999 31/08/2000 Year 5
01/09/1998 31/08/1999 Year 6 - 11+ transfer 2010**
High school year - 2009
01/09/1997 31/08/1998 Year 7
01/09/1996 31/08/1997 Year 8
01/09/1995 31/08/1996 Year 9
01/09/1994 31/08/1995 Year 10
01/09/1993 31/08/1994 Year 11
Schools and colleges - sixth form
01/09/1992 31/08/1993 Year 12
01/09/1991 31/08/1992 Year 13



Submitted via EggHeadCafe - Software Developer Portal of Choice
C# : Create Setup project which also include multiple applications in one setup
http://www.eggheadcafe.com/tutorial...e34-8bc2794e4fb8/c--create-setup-project.aspx

 

[Jacob Skaria]

Hi Adam

With your data in ColA,ColB and ColC try the below formula with the birthday
in cell D1

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(C1:C100,MATCH(1,(A1:A100<=D1)*(B1:B100>=D1),0))