Building cell value from multiple cells with Countif?

[KW1M]

I have COUNTIF working to calculate years of service/membership; but now I would like to add the person's name (First and Last) so I know who qualifies versus just a count.

MrExcel test.xlsx
	A	B	C	D	E	F
1	First Name	Last Name	Created
2	Chris	Bull	2014
3	Tom	Bull	1987
4	Dave	Chris	1992
5	Dave	****	2003
6	Alan	Fill	2020
7	John	Flat	2013
8	Cliff	How	2021
9	Paul	Hye	1991
10	Ray	Kerchunck	2006
11	Kevin	Kooper	2022
12	Martin	Kooper	2019
13	Rick	Laverne	2013
14	Evan	MacTire	2022
15	Paul	Mula	2021
16	Jack	Mum	1983
17	Brent	None	2014
18	John	Pucker	2015
19	David	Rubber	2023
20	Mike	Teb	2019
21	Bud	Valerie	1993
22	John	Wank	1990
23	Dave	Walt	2022
24
25	2024 Recognition Status (Continuous Service)		Recepients		Start Date	End Date
26	13 years	2			10/1/2024	10/1/2011
27	26 years	5			10/1/2011	10/1/1998
28	39 years	1			10/1/1998	10/1/1985
29					10/1/1985	10/1/1972
TEST

Cell Formulas
Range	Formula
B26:B28	B26	=COUNTIFS(Table13[Created],"<="&E27,Table13[Created],">="&F27)

 

[Fluff]

How about

=TEXTJOIN(CHAR(10),,FILTER(Table13[First Name]&" "&Table13[Last Name],(Table13[Created]<=E27)*(Table13[Created]>=F27)))

 

[KW1M]

How about

=TEXTJOIN(CHAR(10),,FILTER(Table13[First Name]&" "&Table13[Last Name],(Table13[Created]<=E27)*(Table13[Created]>=F27)))

Is FILTER a special function? Excel (2019)
When I paste into cell C26 (unmerged the cell first) - getting "That function does not exist" pop up.

 

[Fluff]

Filter is not available in 2019, but does exist in 365 which your profile says you are using.

 

[KW1M]

Thanks -- home laptop running MS Office Pro Plus 2019, not 365. Will check work laptop... Thank you!

 

[Fluff]

For 2019 you can use

=TEXTJOIN(CHAR(10),,IF((Table13[Created]<=E27)*(Table13[Created]>=F27),Table13[First Name]&" "&Table13[Last Name],""))

Which will need to be confirmed with Ctrl Shift Enter.

 

[KW1M]

Thanks -- I've got it working now in 2019 (and work running 365)... just need to play with formatting to put commas between names - but 99% there.

Thank you and Happy New Year!

 

[Fluff]

If you want commas rater than a line feed, you can use

=TEXTJOIN(", ",,FILTER(Table13[First Name]&" "&Table13[Last Name],(Table13[Created]<=E27)*(Table13[Created]>=F27)))