How do I get this to generate Col E into Col C, according to age range?

CBuu9701

New Member
Joined
Oct 7, 2005
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Book1
ABCDEFGHI
1AgeYearGeneration20212021
2591961The Silent Generation192519457696
3551965Baby Boomer Generation194619645775
4401980Generation X (Baby Bust)196519794256
5421978Xennials197519853646
6341986Millennials/Generation Y, Gen Next198019942741
7521968iGen / Gen Z19952012926
8321988Gen Alpha20132025-48
9411979
10561964
11491971
12631957
13391981
14291991
15361984
16381982
17491971
18411979
19521968
20591961
21571963
22491971
23331987
24351985
25581962
26361984
27351985
28451975
29391981
30331987
31521968
32651955
33381982
34371983
35401980
36241996
37341986
38581962
39291991
40561964
41311989
42271993
43591961
44431977
45371983
46361984
47401980
48281992
49471973
50431977
51561964
52301990
53431977
54471973
55461974
56301990
57581962
58291991
59541966
60441976
61591961
62631957
63641956
64511969
65361984
66291991
Sheet1
Cell Formulas
RangeFormula
H1:I1H1=YEAR(TODAY())
H2H2=H1-G2
I2:I8I2=$I$1-F2
H3H3=H1-G3
H4H4=H1-G4
H5H5=H1-G5
H6H6=H1-G6
H7H7=H1-G7
H8H8=H1-G8
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I figured it would be a VLookup, but I don't think I'm calculating the LookupVector correctly. :-(
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Also some of you year ranges overlap, what should be returned when a year in col B matches two values in col E?
 
Upvote 0
Hi,

VLOOKUP won't work due to the way your E2:G8 Table is set up, you can use LOOKUP.
One problem, as Fluff asked, you have overlapping years in F4:G6

Assuming those overlapping years is a mistake and will be corrected, you can try:

Book3.xlsx
ABCDEFGHI
1AgeYearGeneration20212021
2591961Baby Boomer GenerationThe Silent Generation192519457696
3551965Generation X (Baby Bust)Baby Boomer Generation194619645775
4401980Millennials/Generation Y, Gen NextGeneration X (Baby Bust)196519794256
5421978XennialsXennials197519853646
6341986Millennials/Generation Y, Gen NextMillennials/Generation Y, Gen Next198019942741
7521968Generation X (Baby Bust)iGen / Gen Z19952012926
8321988Millennials/Generation Y, Gen NextGen Alpha20132025-48
9411979Xennials
10561964Baby Boomer Generation
11491971Generation X (Baby Bust)
12631957Baby Boomer Generation
13391981Millennials/Generation Y, Gen Next
14291991Millennials/Generation Y, Gen Next
15361984Millennials/Generation Y, Gen Next
16381982Millennials/Generation Y, Gen Next
17491971Generation X (Baby Bust)
18411979Xennials
19521968Generation X (Baby Bust)
20591961Baby Boomer Generation
21571963Baby Boomer Generation
22491971Generation X (Baby Bust)
23331987Millennials/Generation Y, Gen Next
24351985Millennials/Generation Y, Gen Next
25581962Baby Boomer Generation
26361984Millennials/Generation Y, Gen Next
27351985Millennials/Generation Y, Gen Next
28451975Xennials
29391981Millennials/Generation Y, Gen Next
30331987Millennials/Generation Y, Gen Next
31521968Generation X (Baby Bust)
32651955Baby Boomer Generation
33381982Millennials/Generation Y, Gen Next
34371983Millennials/Generation Y, Gen Next
35401980Millennials/Generation Y, Gen Next
36241996iGen / Gen Z
37341986Millennials/Generation Y, Gen Next
38581962Baby Boomer Generation
39291991Millennials/Generation Y, Gen Next
40561964Baby Boomer Generation
41311989Millennials/Generation Y, Gen Next
42271993Millennials/Generation Y, Gen Next
43591961Baby Boomer Generation
44431977Xennials
45371983Millennials/Generation Y, Gen Next
46361984Millennials/Generation Y, Gen Next
47401980Millennials/Generation Y, Gen Next
48281992Millennials/Generation Y, Gen Next
49471973Generation X (Baby Bust)
50431977Xennials
51561964Baby Boomer Generation
52301990Millennials/Generation Y, Gen Next
53431977Xennials
54471973Generation X (Baby Bust)
55461974Generation X (Baby Bust)
56301990Millennials/Generation Y, Gen Next
57581962Baby Boomer Generation
58291991Millennials/Generation Y, Gen Next
59541966Generation X (Baby Bust)
60441976Xennials
61591961Baby Boomer Generation
62631957Baby Boomer Generation
63641956Baby Boomer Generation
64511969Generation X (Baby Bust)
65361984Millennials/Generation Y, Gen Next
66291991Millennials/Generation Y, Gen Next
Sheet735
Cell Formulas
RangeFormula
H1:I1H1=YEAR(TODAY())
H2H2=H1-G2
I2:I8I2=$I$1-F2
H3H3=H1-G3
H4H4=H1-G4
H5H5=H1-G5
H6H6=H1-G6
H7H7=H1-G7
H8H8=H1-G8
C2:C66C2=LOOKUP(B2,F$2:F$8,E$2:E$8)
 
Upvote 0
Unfortunately, the overlapping years is not a mistake as that's how generation X and Xenials get often reported. Wondering if I should clump them together, but not sure what would make sense.
 
Upvote 0
Ok, how about, for 365
+Fluff 1.xlsm
ABCDEFGHI
1AgeYearGeneration20212021
2591961Baby Boomer GenerationThe Silent Generation192519457696
3551965Generation X (Baby Bust)Baby Boomer Generation194619645775
4401980Xennials Millennials/Generation Y, Gen NextGeneration X (Baby Bust)196519794256
5421978Generation X (Baby Bust) XennialsXennials197519853646
6341986Millennials/Generation Y, Gen NextMillennials/Generation Y, Gen Next198019942741
7521968Generation X (Baby Bust)iGen / Gen Z19952012926
8321988Millennials/Generation Y, Gen NextGen Alpha20132025-48
9411979Generation X (Baby Bust) Xennials
10561964Baby Boomer Generation
11491971Generation X (Baby Bust)
12631957Baby Boomer Generation
13391981Xennials Millennials/Generation Y, Gen Next
14291991Millennials/Generation Y, Gen Next
15361984Xennials Millennials/Generation Y, Gen Next
16381982Xennials Millennials/Generation Y, Gen Next
17491971Generation X (Baby Bust)
18411979Generation X (Baby Bust) Xennials
19521968Generation X (Baby Bust)
20591961Baby Boomer Generation
21571963Baby Boomer Generation
22491971Generation X (Baby Bust)
23331987Millennials/Generation Y, Gen Next
24351985Xennials Millennials/Generation Y, Gen Next
25581962Baby Boomer Generation
26361984Xennials Millennials/Generation Y, Gen Next
27351985Xennials Millennials/Generation Y, Gen Next
28451975Generation X (Baby Bust) Xennials
29391981Xennials Millennials/Generation Y, Gen Next
30331987Millennials/Generation Y, Gen Next
31521968Generation X (Baby Bust)
32651955Baby Boomer Generation
33381982Xennials Millennials/Generation Y, Gen Next
34371983Xennials Millennials/Generation Y, Gen Next
35401980Xennials Millennials/Generation Y, Gen Next
36241996iGen / Gen Z
37341986Millennials/Generation Y, Gen Next
38581962Baby Boomer Generation
39291991Millennials/Generation Y, Gen Next
40561964Baby Boomer Generation
41311989Millennials/Generation Y, Gen Next
42271993Millennials/Generation Y, Gen Next
43591961Baby Boomer Generation
44431977Generation X (Baby Bust) Xennials
45371983Xennials Millennials/Generation Y, Gen Next
46361984Xennials Millennials/Generation Y, Gen Next
47401980Xennials Millennials/Generation Y, Gen Next
48281992Millennials/Generation Y, Gen Next
49471973Generation X (Baby Bust)
50431977Generation X (Baby Bust) Xennials
51561964Baby Boomer Generation
52301990Millennials/Generation Y, Gen Next
53431977Generation X (Baby Bust) Xennials
54471973Generation X (Baby Bust)
55461974Generation X (Baby Bust)
56301990Millennials/Generation Y, Gen Next
57581962Baby Boomer Generation
58291991Millennials/Generation Y, Gen Next
59541966Generation X (Baby Bust)
60441976Generation X (Baby Bust) Xennials
61591961Baby Boomer Generation
62631957Baby Boomer Generation
63641956Baby Boomer Generation
64511969Generation X (Baby Bust)
65361984Xennials Millennials/Generation Y, Gen Next
66291991Millennials/Generation Y, Gen Next
Schedule
Cell Formulas
RangeFormula
H1:I1H1=YEAR(TODAY())
H2H2=H1-G2
I2:I8I2=$I$1-F2
H3H3=H1-G3
H4H4=H1-G4
H5H5=H1-G5
H6H6=H1-G6
H7H7=H1-G7
H8H8=H1-G8
C2:C66C2=TEXTJOIN(CHAR(10),,FILTER($E$2:$E$8,($F$2:$F$8<=B2)*($G$2:$G$8>=B2)))
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Sorry, I'm on 365.
 
Upvote 0
I noticed that you had updated your profile & thanks for that. :)
 
Upvote 0
Ok, how about, for 365
+Fluff 1.xlsm
ABCDEFGHI
1AgeYearGeneration20212021
2591961Baby Boomer GenerationThe Silent Generation192519457696
3551965Generation X (Baby Bust)Baby Boomer Generation194619645775
4401980Xennials Millennials/Generation Y, Gen NextGeneration X (Baby Bust)196519794256
5421978Generation X (Baby Bust) XennialsXennials197519853646
6341986Millennials/Generation Y, Gen NextMillennials/Generation Y, Gen Next198019942741
7521968Generation X (Baby Bust)iGen / Gen Z19952012926
8321988Millennials/Generation Y, Gen NextGen Alpha20132025-48
9411979Generation X (Baby Bust) Xennials
10561964Baby Boomer Generation
11491971Generation X (Baby Bust)
12631957Baby Boomer Generation
13391981Xennials Millennials/Generation Y, Gen Next
14291991Millennials/Generation Y, Gen Next
15361984Xennials Millennials/Generation Y, Gen Next
16381982Xennials Millennials/Generation Y, Gen Next
17491971Generation X (Baby Bust)
18411979Generation X (Baby Bust) Xennials
19521968Generation X (Baby Bust)
20591961Baby Boomer Generation
21571963Baby Boomer Generation
22491971Generation X (Baby Bust)
23331987Millennials/Generation Y, Gen Next
24351985Xennials Millennials/Generation Y, Gen Next
25581962Baby Boomer Generation
26361984Xennials Millennials/Generation Y, Gen Next
27351985Xennials Millennials/Generation Y, Gen Next
28451975Generation X (Baby Bust) Xennials
29391981Xennials Millennials/Generation Y, Gen Next
30331987Millennials/Generation Y, Gen Next
31521968Generation X (Baby Bust)
32651955Baby Boomer Generation
33381982Xennials Millennials/Generation Y, Gen Next
34371983Xennials Millennials/Generation Y, Gen Next
35401980Xennials Millennials/Generation Y, Gen Next
36241996iGen / Gen Z
37341986Millennials/Generation Y, Gen Next
38581962Baby Boomer Generation
39291991Millennials/Generation Y, Gen Next
40561964Baby Boomer Generation
41311989Millennials/Generation Y, Gen Next
42271993Millennials/Generation Y, Gen Next
43591961Baby Boomer Generation
44431977Generation X (Baby Bust) Xennials
45371983Xennials Millennials/Generation Y, Gen Next
46361984Xennials Millennials/Generation Y, Gen Next
47401980Xennials Millennials/Generation Y, Gen Next
48281992Millennials/Generation Y, Gen Next
49471973Generation X (Baby Bust)
50431977Generation X (Baby Bust) Xennials
51561964Baby Boomer Generation
52301990Millennials/Generation Y, Gen Next
53431977Generation X (Baby Bust) Xennials
54471973Generation X (Baby Bust)
55461974Generation X (Baby Bust)
56301990Millennials/Generation Y, Gen Next
57581962Baby Boomer Generation
58291991Millennials/Generation Y, Gen Next
59541966Generation X (Baby Bust)
60441976Generation X (Baby Bust) Xennials
61591961Baby Boomer Generation
62631957Baby Boomer Generation
63641956Baby Boomer Generation
64511969Generation X (Baby Bust)
65361984Xennials Millennials/Generation Y, Gen Next
66291991Millennials/Generation Y, Gen Next
Schedule
Cell Formulas
RangeFormula
H1:I1H1=YEAR(TODAY())
H2H2=H1-G2
I2:I8I2=$I$1-F2
H3H3=H1-G3
H4H4=H1-G4
H5H5=H1-G5
H6H6=H1-G6
H7H7=H1-G7
H8H8=H1-G8
C2:C66C2=TEXTJOIN(CHAR(10),,FILTER($E$2:$E$8,($F$2:$F$8<=B2)*($G$2:$G$8>=B2)))
Thanks! Now... I have to walk through this and see if I understand what you did. :-) Have a great weekend!
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top