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);

01/09/1997	31/08/1998	N
01/09/1998	31/08/1999	M
01/09/1999	31/08/2000	L
01/09/2000	31/08/2001	K
01/09/2001	31/08/2002	J
01/09/2002	31/08/2003	I
01/09/2003	31/08/2004	H
01/09/2004	31/08/2005	G
01/09/2005	31/08/2006	F
01/09/2006	31/08/2007	E
01/09/2007	31/08/2008	D
01/09/2008	31/08/2009	C
01/09/2009	31/08/2010	B
01/09/2010	31/08/2011	A

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>

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.