look up maximum value in range and return corresponding values in another column

[junkforhr]

I have a dataset (see sample below). What I'm looking to do is return in one cell where the highest unique count is the same for different periods. I have found the below array formula, but would prefer not use this, as the actual data will be quite large.

=IFERROR(INDEX($J$8:$J$500, SMALL(IF(LARGE($H$8:$H$500,1)=$H$8:$H$500, ROW($H$8:$H$500)-ROW($H$8)+1), ROW(1:1))),"" )

Desired outcome in cell K3:
11/04/2023, 21/04/2023, 25/04/2023, 05/05/2023

Unique Count	period
35​	11/04/2023​
12​	11/04/2023​
29​	12/04/2023​
27​	13/04/2023​
16​	14/04/2023​
33​	17/04/2023​
29​	18/04/2023​
22​	19/04/2023​
19​	20/04/2023​
35​	21/04/2023​
27​	24/04/2023​
35​	25/04/2023​
12​	26/04/2023​
30​	27/04/2023​
29​	28/04/2023​
10​	29/04/2023​
30​	30/04/2023​
27​	1/05/2023​
21​	2/05/2023​
15​	3/05/2023​
22​	4/05/2023​
35​	5/05/2023​
34​	6/05/2023​
18​	7/05/2023​
12​	8/05/2023​
8​	9/05/2023​
2​	10/05/2023​

 

[zepharoz]

Pardon my excel skills. Would it not be better to do this on a new sheet.

=unique( Entire column for Period)

=MAX(IF(B:B= {unique date provided from formula above}, A:A))

 

[Fluff]

How about

Fluff.xlsm
	H	I	J	K
1
2
3				11/04/2023
4				21/04/2023
5				25/04/2023
6				05/05/2023
7	Unique Count		period
8	35		11/04/2023
9	12		11/04/2023
10	29		12/04/2023
11	27		13/04/2023
12	16		14/04/2023
13	33		17/04/2023
14	29		18/04/2023
15	22		19/04/2023
16	19		20/04/2023
17	35		21/04/2023
18	27		24/04/2023
19	35		25/04/2023
20	12		26/04/2023
21	30		27/04/2023
22	29		28/04/2023
23	10		29/04/2023
24	30		30/04/2023
25	27		01/05/2023
26	21		02/05/2023
27	15		03/05/2023
28	22		04/05/2023
29	35		05/05/2023
30	34		06/05/2023
31	18		07/05/2023
32	12		08/05/2023
33	8		09/05/2023
34	2		10/05/2023
Master

Cell Formulas
Range	Formula
K3:K7	K3	=IFERROR(AGGREGATE(15,6,$J$8:$J$500/($H$8:$H$500=MAX($H$8:$H$500)),ROWS(K$3:K3)),"")

 

[junkforhr]

Thanks Fluff, Exactly what I was after

 

[Fluff]

You're welcome & thanks for the feedback.