If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel date intervals look up
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/tutorials...p-project.aspx |
#2
|
|||
|
|||
Excel date intervals look up
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)) -- Jacob "adam smith" wrote: 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/tutorials...p-project.aspx . |
Thread Tools | |
Display Modes | |
|
|