Produce list of significant birthdays (birthdays ending in a 0)

[Jasesair]

I have all the data needed but am struggling to create a working formula to show significant birthdays.

AA6:AA203 has ages as at 31 Dec
B6:B203 has everyone's names
C6:C203 has everyone's DOB

I think I'm wanting to identify the nth occurrence of column AA range ending in a 0, then return their name and their DOB. Ideally, if the formula could include significant birthdays of 18 and 21 as well as ones ending in 0, that would be amazing! I've been trying to avoid array formulas. Any assistance would be brilliant!

 

[Jasesair]

Hi Peter, wonder if you might be able to assist with a little amendment to the above. I've decided that a 10th birthday isn't significant...all other birthdays that end in a 0 are, but not 10!!! I'm still learning so unsure how to adjust to suit this. Would really appreciate any help.

 

[Peter_SSs]

I've decided that a 10th birthday isn't significant...all other birthdays that end in a 0 are, but not 10!!! I'm still learning so unsure how to adjust to suit this.

Try adding this
=IFERROR(AGGREGATE(15,6,AA$2:AA$20/((AA$2:AA$20>10)*(MOD(AA$2:AA$20,10)=0)+(AA$2:AA$20=18)+(AA$2:AA$20=21)),ROWS(AE$2:AE2)),"")