Band based on age

[Jimbola]

I have a list of date of birth in col A and in Col B I need to allocate to them a band based on the table below (bands in bold);


[TABLE="width: 206"]
<tbody>[TR]
[TD="align: right"]01/09/1997[/TD]
[TD="align: right"] 31/08/1998[/TD]
[TD] N[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/1998[/TD]
[TD="align: right"]31/08/1999[/TD]
[TD] M[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/1999[/TD]
[TD="align: right"]31/08/2000[/TD]
[TD] L[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2000[/TD]
[TD="align: right"]31/08/2001 [/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2001[/TD]
[TD="align: right"]31/08/2002[/TD]
[TD] J[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2002[/TD]
[TD="align: right"]31/08/2003[/TD]
[TD] I[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2003[/TD]
[TD="align: right"] 31/08/2004 [/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2004[/TD]
[TD="align: right"] 31/08/2005 [/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2005[/TD]
[TD="align: right"]31/08/2006[/TD]
[TD] F[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2006[/TD]
[TD="align: right"]31/08/2007[/TD]
[TD] E[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2007[/TD]
[TD="align: right"]31/08/2008[/TD]
[TD] D[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2008[/TD]
[TD="align: right"]31/08/2009[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2009[/TD]
[TD="align: right"]31/08/2010 [/TD]
[TD] B[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2010[/TD]
[TD="align: right"]31/08/2011[/TD]
[TD] A[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]

To many bands to do an IF or IFS statement, is there another way?

 

[mole999]

question about 2012, how would you deal with extensibility. Maybe low tek but thinking a vlookup with that data on a hidden sheet for ease of update

 

[Jimbola]

question about 2012, how would you deal with extensibility. Maybe low tek but thinking a vlookup with that data on a hidden sheet for ease of update

There will be no 2012 these are all the bands there will ever be. How would you do it with vlookup?

 

[Joe4]

You use the "Approximate Match" argument in VLOOKUP (set 4th argument to TRUE).

So set up your lookup table, just as it appears in your first post. Let's say that range is in cells A1:C14 on Sheet2, and the date you are looking up is in cell D1.
Then your formula would look like:

=VLOOKUP(D1,Sheet2!$A$1:$C$14,3,TRUE)

 

[Jimbola]

That's the cookie, not sure why I didn't see that.