Age Grouping

[zinah]

Hi,
Can anyone help me with a formula that can group age like below group?

Book2
	A	B	C	D	E	F	G	H	I
1	Age	Age Grouping							Age Grouping
2	27	26 to 34 years							25 years or younger
3	34	26 to 34 years							26 to 34 years
4	42	35 to 44 years							35 to 44 years
5	24								45 to 54 years
6	38								55 years or older
7	46
8	36
9	53
10	30
11	25
12	43
13	54
14	54
15	50
16	62
17	42
18	62
19	52
20	46
21	38
22	41
23	61
24	47
25	45
26	54
27	55
28	34
29	51
30	57
31	43
32	44
33	61
34	48
35	67
36	52
37	40
38	62
39	53
40	50
41	35
42	53
43	53
44	41
45	59
46	43
47	56
48	42
49	63
50	38
51	49
52	49
53	54
54	42
55	64
56	46
57	52
58	75
59	47
60	42
61	49
Sheet1

 

[etaf]

=INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1))

you could add
=IF( A2="","",INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1)))

Book5
	A	B	C	D	E	F	G	H	I
1	Age	Age Grouping							Age Grouping
2	27	26 to 34 years	26 to 34 years					0	25 years or younger
3	34	26 to 34 years	26 to 34 years					26	26 to 34 years
4	42	35 to 44 years	35 to 44 years					35	35 to 44 years
5	24		25 years or younger					45	45 to 54 years
6	38		35 to 44 years					55	55 years or older
7	46		45 to 54 years
8	36		35 to 44 years
9	53		45 to 54 years
10	30		26 to 34 years
11	25		25 years or younger
12	43		35 to 44 years
13	54		45 to 54 years
14	54		45 to 54 years
15	50		45 to 54 years
16	62		55 years or older
17	42		35 to 44 years
18	62		55 years or older
19	52		45 to 54 years
20	46		45 to 54 years
21	38		35 to 44 years
22	41		35 to 44 years
23	61		55 years or older
24	47		45 to 54 years
25	45		45 to 54 years
26	54		45 to 54 years
27	55		55 years or older
28	34		26 to 34 years
29	51		45 to 54 years
30	57		55 years or older
31	43		35 to 44 years
32	44		35 to 44 years
33	61		55 years or older
34	48		45 to 54 years
35	67		55 years or older
36	52		45 to 54 years
37	40		35 to 44 years
38	62		55 years or older
39	53		45 to 54 years
40	50		45 to 54 years
41	35		35 to 44 years
42	53		45 to 54 years
43	53		45 to 54 years
44	41		35 to 44 years
45	59		55 years or older
46	43		35 to 44 years
47	56		55 years or older
48	42		35 to 44 years
49	63		55 years or older
50	38		35 to 44 years
51	49		45 to 54 years
52	49		45 to 54 years
53	54		45 to 54 years
54	42		35 to 44 years
55	64		55 years or older
56	46		45 to 54 years
57	52		45 to 54 years
58	75		55 years or older
59	47		45 to 54 years
60	42		35 to 44 years
61	49		45 to 54 years
62	25		25 years or younger
63	26		26 to 34 years
64	34		26 to 34 years
65	35		35 to 44 years
66	44		35 to 44 years
67	45		45 to 54 years
68	54		45 to 54 years
69	55		55 years or older
70	56		55 years or older
Sheet1

Cell Formulas
Range	Formula
C2:C70	C2	=INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1))

 

[zinah]

=INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1))

you could add
=IF( A2="","",INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1)))

Book5
	A	B	C	D	E	F	G	H	I
1	Age	Age Grouping							Age Grouping
2	27	26 to 34 years	26 to 34 years					0	25 years or younger
3	34	26 to 34 years	26 to 34 years					26	26 to 34 years
4	42	35 to 44 years	35 to 44 years					35	35 to 44 years
5	24		25 years or younger					45	45 to 54 years
6	38		35 to 44 years					55	55 years or older
7	46		45 to 54 years
8	36		35 to 44 years
9	53		45 to 54 years
10	30		26 to 34 years
11	25		25 years or younger
12	43		35 to 44 years
13	54		45 to 54 years
14	54		45 to 54 years
15	50		45 to 54 years
16	62		55 years or older
17	42		35 to 44 years
18	62		55 years or older
19	52		45 to 54 years
20	46		45 to 54 years
21	38		35 to 44 years
22	41		35 to 44 years
23	61		55 years or older
24	47		45 to 54 years
25	45		45 to 54 years
26	54		45 to 54 years
27	55		55 years or older
28	34		26 to 34 years
29	51		45 to 54 years
30	57		55 years or older
31	43		35 to 44 years
32	44		35 to 44 years
33	61		55 years or older
34	48		45 to 54 years
35	67		55 years or older
36	52		45 to 54 years
37	40		35 to 44 years
38	62		55 years or older
39	53		45 to 54 years
40	50		45 to 54 years
41	35		35 to 44 years
42	53		45 to 54 years
43	53		45 to 54 years
44	41		35 to 44 years
45	59		55 years or older
46	43		35 to 44 years
47	56		55 years or older
48	42		35 to 44 years
49	63		55 years or older
50	38		35 to 44 years
51	49		45 to 54 years
52	49		45 to 54 years
53	54		45 to 54 years
54	42		35 to 44 years
55	64		55 years or older
56	46		45 to 54 years
57	52		45 to 54 years
58	75		55 years or older
59	47		45 to 54 years
60	42		35 to 44 years
61	49		45 to 54 years
62	25		25 years or younger
63	26		26 to 34 years
64	34		26 to 34 years
65	35		35 to 44 years
66	44		35 to 44 years
67	45		45 to 54 years
68	54		45 to 54 years
69	55		55 years or older
70	56		55 years or older
Sheet1

Cell Formulas
Range	Formula
C2:C70	C2	=INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1))

Perfect, what about if I need to update the tenure category based on hire date?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Age	Age Grouping	Hire Date	Tenure Category					Age Grouping		Tenure Category
2	27	26 to 34 years	1/6/2020					0	25 years or younger		Less than 2 years
3	34	26 to 34 years	5/4/2020					26	26 to 34 years		2 to 5 years
4	42	35 to 44 years	4/6/2020					35	35 to 44 years		6 to 10 years
5	24	25 years or younger	4/13/2020					45	45 to 54 years		11 to 15 years
6	38	35 to 44 years	8/1/2021					55	55 years or older		16 to 20 years
7	46	45 to 54 years	8/3/2021								More than 20 years
8	36	35 to 44 years	8/2/2021
9	53	45 to 54 years	9/1/2021
10	30	26 to 34 years	7/27/2021
11	25	25 years or younger	8/2/2021
12	43	35 to 44 years	9/6/2021
13	54	45 to 54 years	4/3/2017
14	54	45 to 54 years	11/13/2006
15	50	45 to 54 years	10/29/2013
16	62	55 years or older	4/3/2017
17	42	35 to 44 years	4/3/2017
18	62	55 years or older	4/3/2017
19	52	45 to 54 years	4/16/2007
20	46	45 to 54 years	9/14/2009
21	38	35 to 44 years	8/14/2007
22	41	35 to 44 years	4/25/2007
23	61	55 years or older	3/26/2007
24	47	45 to 54 years	1/4/2005
25	45	45 to 54 years	8/6/2007
26	54	45 to 54 years	3/10/2008
27	55	55 years or older	10/8/2003
28	34	26 to 34 years	7/5/2012
29	51	45 to 54 years	1/31/2005
30	57	55 years or older	8/15/2005
31	43	35 to 44 years	4/26/2010
32	44	35 to 44 years	11/4/2013
33	61	55 years or older	10/1/2003
34	48	45 to 54 years	12/13/2004
35	67	55 years or older	8/14/2000
36	52	45 to 54 years	10/31/2011
37	40	35 to 44 years	10/13/2008
38	62	55 years or older	4/16/2012
39	53	45 to 54 years	2/25/2009
40	50	45 to 54 years	1/11/2010
41	35	35 to 44 years	11/18/2013
42	53	45 to 54 years	6/21/2000
43	53	45 to 54 years	10/23/2013
44	41	35 to 44 years	11/4/2009
45	59	55 years or older	1/1/2010
46	43	35 to 44 years	2/6/2012
47	56	55 years or older	5/27/2013
48	42	35 to 44 years	9/5/2011
49	63	55 years or older	12/27/2010
50	38	35 to 44 years	3/12/2012
51	49	45 to 54 years	6/19/2006
52	49	45 to 54 years	10/29/2012
53	54	45 to 54 years	10/1/2012
54	42	35 to 44 years	2/13/2012
55	64	55 years or older	2/9/2003
56	46	45 to 54 years	10/21/2013
57	52	45 to 54 years	11/26/2012
58	75	55 years or older	4/1/2008
59	47	45 to 54 years	5/10/2011
60	42	35 to 44 years	5/29/2007
61	49	45 to 54 years	11/8/2010
Sheet1

Cell Formulas
Range	Formula
B2:B61	B2	=IF( A2="","",INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1)))

 

[etaf]

you could use a helper column to calculate the years - Or direct from the date

=INDEX($K$2:$K$7,MATCH(DATEDIF(C2,TODAY(),"Y"),$J$2:$J$7,1))

datedif with the "Y" will give the complete years

 

[etaf]

you could use a helper column to calculate the years - Or direct from the date

=INDEX($K$2:$K$7,MATCH(DATEDIF(C2,TODAY(),"Y"),$J$2:$J$7,1))

datedif with the "Y" will give the complete years

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Age	Age Grouping	Hire Date	Tenure Category	years				Age Grouping		Tenure Category
2	27	26 to 34 years	6/1/20		2 to 5 years			0	25 years or younger	0	Less than 2 years
3	34	26 to 34 years	4/5/17		6 to 10 years			26	26 to 34 years	2	2 to 5 years
4	42	35 to 44 years	6/4/01		More than 20 years			35	35 to 44 years	6	6 to 10 years
5	24	25 years or younger	4/13/23		Less than 2 years			45	45 to 54 years	11	11 to 15 years
6	38	35 to 44 years	1/8/07		16 to 20 years			55	55 years or older	16	16 to 20 years
7	46	45 to 54 years	3/8/10		11 to 15 years					21	More than 20 years
8	36	35 to 44 years	2/8/97		More than 20 years
9	53	45 to 54 years	1/9/21		2 to 5 years
10	30	26 to 34 years
11	25	25 years or younger	2/8/21		2 to 5 years
12	43	35 to 44 years	6/9/21		2 to 5 years
13	54	45 to 54 years	3/4/17		6 to 10 years
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF( A2="","",INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1)))
E2:E13	E2	=IF(C2="","",INDEX($K$2:$K$7,MATCH(DATEDIF(C2,TODAY(),"Y"),$J$2:$J$7,1)))

 

[zinah]

you could use a helper column to calculate the years - Or direct from the date

=INDEX($K$2:$K$7,MATCH(DATEDIF(C2,TODAY(),"Y"),$J$2:$J$7,1))

datedif with the "Y" will give the complete years

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Age	Age Grouping	Hire Date	Tenure Category	years				Age Grouping		Tenure Category
2	27	26 to 34 years	6/1/20		2 to 5 years			0	25 years or younger	0	Less than 2 years
3	34	26 to 34 years	4/5/17		6 to 10 years			26	26 to 34 years	2	2 to 5 years
4	42	35 to 44 years	6/4/01		More than 20 years			35	35 to 44 years	6	6 to 10 years
5	24	25 years or younger	4/13/23		Less than 2 years			45	45 to 54 years	11	11 to 15 years
6	38	35 to 44 years	1/8/07		16 to 20 years			55	55 years or older	16	16 to 20 years
7	46	45 to 54 years	3/8/10		11 to 15 years					21	More than 20 years
8	36	35 to 44 years	2/8/97		More than 20 years
9	53	45 to 54 years	1/9/21		2 to 5 years
10	30	26 to 34 years
11	25	25 years or younger	2/8/21		2 to 5 years
12	43	35 to 44 years	6/9/21		2 to 5 years
13	54	45 to 54 years	3/4/17		6 to 10 years
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF( A2="","",INDEX($I$2:$I$6,MATCH(A2,$H$2:$H$6,1)))
E2:E13	E2	=IF(C2="","",INDEX($K$2:$K$7,MATCH(DATEDIF(C2,TODAY(),"Y"),$J$2:$J$7,1)))

THANK YOU SOOO MUCH @etaf , I was using months instead of years, this worked perfectly great.

 

[etaf]

you are welcome