Aggregate function assistance or some other function?

[cspengel]

So basically I need the lowest values in column Y for every name in A38:A58 that occurs in the rows of columns H:K based on the number next to the name in column C ( row 38-row 58).

The column this formula would be in is column N.
For each row that contains the name, and if the row contains one of the lowest values, an "x" will appear in column N.

I tried using

=IF(Y2<=AGGREGATE(15,6,$Y$2:$Y$1500/($H$2:$K$1500=H2),VLOOKUP(H2,$A$38:$C$58,3,0)),"x","")

However that did not quite work right as I am not sure you can use a four column range with aggregate? Thanks for any assistance!

correlationrandom1(in_progress)5(USE4FORNOW)REVISION5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	QB	FLEX	FLEX	FLEX	FLEX		QB	FLEX	FLEX	FLEX	FLEX	ComboID	Selection MVP	Selection Flex	Salary	Projection	Floor Projection	Ceiling Projection↑	^ Difference	Target	Depth Total	TARGET PT 12+	Ownership L|U	Correlation	Value↓
2	Justin Herbert	Justin Herbert	Justin Herbert	Justin Herbert	Justin Herbert		Nick Foles	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Joshua Kelley	101265			59000	64.305	43.5	86.5	2.7712	61.53	6	3	0.99%	1.09	5.151233211
3	Austin Ekeler	Austin Ekeler	Austin Ekeler	Austin Ekeler	Austin Ekeler		Justin Herbert	Nick Foles	Michael Pittman Jr.	Cameron Dicker	Joshua Kelley	16215			59000	68.24	49.5	93	2.76835	66.225	6	3	0.99%	1.09	5.339626068
4	Keenan Allen	Keenan Allen	Keenan Allen	Keenan Allen	Keenan Allen		Nick Foles	Austin Ekeler	Michael Pittman Jr.	Cameron Dicker	Joshua Kelley	105990			59500	61.155	39.5	86.5	2.7662	58.57	6	3	0.47%	0.77	4.921005939
5	Nick Foles	Nick Foles	Nick Foles	Nick Foles	Nick Foles		Austin Ekeler	Nick Foles	Michael Pittman Jr.	Cameron Dicker	Joshua Kelley	49290			59500	63.515	42	94.5	2.76085	61.785	6	3	0.47%	0.77	4.994285159
6	Mike Williams	Mike Williams	Mike Williams	Mike Williams	Mike Williams		Mike Williams	Justin Herbert	Nick Foles	Cameron Dicker	Gerald Everett	133391			60000	69.11	46	103	2.7577	66.25	6	3	1.30%	1.52	5.616107096
7	Deon Jackson	Deon Jackson	Deon Jackson	Deon Jackson	Deon Jackson		Nick Foles	Justin Herbert	Mike Williams	Cameron Dicker	Gerald Everett	100631			60000	69.225	47.5	102	2.7668	66.2	6	3	1.30%	1.52	5.570064239
8	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.	Michael Pittman Jr.		Nick Foles	Justin Herbert	Michael Pittman Jr.	DeAndre Carter	Cameron Dicker	101350			59500	63.835	47.5	94.5	2.7693	63.64	8	3	0.02%	1.91	5.044947497
9			Zack Moss	Zack Moss	Zack Moss		Nick Foles	Justin Herbert	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	101349			60000	64.635	41.5	95	2.7633	61.59	8	3	0.02%	1.91	5.076409485
10			Chase McLaughlin	Chase McLaughlin	Chase McLaughlin		Mike Williams	Justin Herbert	Nick Foles	Cameron Dicker	Alec Pierce	133394			59500	67.07	47	100.5	2.7431	66.1	8	3	1.04%	1.91	5.415886508
11			Cameron Dicker	Cameron Dicker	Cameron Dicker		Nick Foles	Justin Herbert	Mike Williams	Cameron Dicker	Alec Pierce	100634			59500	67.185	48.5	100.5	2.7522	66.05	8	3	1.04%	1.91	5.369843651
12			Gerald Everett	Gerald Everett	Gerald Everett		Mike Williams	Justin Herbert	Nick Foles	Chase McLaughlin	Alec Pierce	133379			60000	67.87	42	104	2.7371	64.05	8	3	1.20%	1.91	5.447348496
13			Joshua Palmer	Joshua Palmer	Joshua Palmer		Nick Foles	Justin Herbert	Mike Williams	Chase McLaughlin	Alec Pierce	100619			60000	67.985	40.5	100.5	2.7462	64	8	3	1.20%	1.91	5.401305639
14			Parris Campbell	Parris Campbell	Parris Campbell		Justin Herbert	Nick Foles	Michael Pittman Jr.	DeAndre Carter	Cameron Dicker	16300			59500	67.77	54	98.5	2.76645	68.335	8	3	0.02%	1.91	5.233340354
15			Alec Pierce	Alec Pierce	Alec Pierce		Justin Herbert	Nick Foles	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	16299			60000	68.57	50	98.5	2.76045	66.285	8	3	0.02%	1.91	5.264802342
16			Indianapolis Colts	Joshua Kelley	Joshua Kelley		Nick Foles	Keenan Allen	Michael Pittman Jr.	Chase McLaughlin	Cameron Dicker	110695			59000	61.805	36.5	85.5	2.7734	56.49	5	3	0.05%	0.89	5.197576152
17				DeAndre Carter			Keenan Allen	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Cameron Dicker	82345			59000	61.87	38.5	86.5	2.7701	56.76	5	3	0.05%	0.89	5.219441231
18				Donald Parham Jr.			Mike Williams	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Cameron Dicker	148495			58000	61.33	30	90.5	2.7527	56.1	6	3	0.04%	0.89	5.291974565
19				Jelani Woods			Nick Foles	Mike Williams	Michael Pittman Jr.	Chase McLaughlin	Cameron Dicker	120145			58000	61.445	33	87.5	2.7618	56.05	6	3	0.04%	0.89	5.245931707
20				Los Angeles Chargers			Mike Williams	Justin Herbert	Michael Pittman Jr.	Chase McLaughlin	Cameron Dicker	134320			60000	69.2	42	100	2.747	65.49	6	3	0.05%	0.89	5.668760279
21				Indianapolis Colts			Nick Foles	Keenan Allen	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	110711			58000	59.435	40.5	89.5	2.7805	55.71	5	3	0.55%	1.07	5.04036253
22				Ashton Dulin			Keenan Allen	Nick Foles	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	82361			58000	59.5	39.5	85.5	2.7772	55.98	5	3	0.55%	1.07	5.062227609
23							Nick Foles	Keenan Allen	Michael Pittman Jr.	Chase McLaughlin	Gerald Everett	110696			58500	60.235	37	90.5	2.7745	53.66	5	3	0.63%	0.89	5.071824518
24							Keenan Allen	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Gerald Everett	82346			58500	60.3	36.5	88	2.7712	53.93	5	3	0.63%	0.89	5.093689597
25							Mike Williams	Nick Foles	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	148511			57000	58.96	34	92	2.7598	55.32	6	3	0.49%	1.07	5.134760942
26	Entries	150					Nick Foles	Mike Williams	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	120161			57000	59.075	31.5	89.5	2.7689	55.27	6	3	0.49%	1.07	5.088718085
27							Mike Williams	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Gerald Everett	148496			57500	59.76	29.5	88	2.7538	53.27	6	3	0.57%	0.89	5.166222931
28	MVP	NumberOfLineUps	#OfLineUpsToTake	%OfLineUpsToTake			Nick Foles	Mike Williams	Michael Pittman Jr.	Chase McLaughlin	Gerald Everett	120146			57500	59.875	32.5	90.5	2.7629	53.22	6	3	0.57%	0.89	5.120180073
29	Justin Herbert	228	30	0.2			Mike Williams	Justin Herbert	Michael Pittman Jr.	Chase McLaughlin	Gerald Everett	134321			59500	67.63	39.5	100	2.7481	62.66	6	3	0.62%	1.12	5.543008645
30	Austin Ekeler	223	30	0.2			Keenan Allen	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	68186			60000	67.37	50.5	96.5	2.7715	65.37	5	3	0.60%	1.15	5.439013323
31	Keenan Allen	255	30	0.2			Mike Williams	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	134336			59000	66.83	44	103	2.7541	64.71	6	3	0.54%	1.15	5.511546657
32	Nick Foles	266	30	0.2			Justin Herbert	Keenan Allen	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	11486			60000	71.24	55	101.5	2.77195	69.795	5	3	0.60%	1.15	5.605541101
33	Mike Williams	290	30	0.2			Justin Herbert	Mike Williams	Michael Pittman Jr.	Cameron Dicker	Gerald Everett	20936			59000	70.88	52	106.5	2.76035	69.355	6	3	0.54%	1.15	5.653896657
34	Deon Jackson	0	30	0.2			Nick Foles	Keenan Allen	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	110799			57500	56.895	36	88.5	2.7624	52.74	8	3	0.01%	0.87	4.74335393
35	Michael Pittman Jr.	0	30	0.2			Keenan Allen	Nick Foles	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	82449			57500	56.96	35.5	83.5	2.7591	53.01	8	3	0.01%	0.87	4.765219009
36							Mike Williams	Nick Foles	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	148599			56500	56.42	28	91	2.7417	52.35	9	3	0.01%	0.87	4.837752342
37	FLEX	NumberOfLineUps	#OfLineUpsToTake	%OfLineUpsToTake			Nick Foles	Keenan Allen	Michael Pittman Jr.	Chase McLaughlin	Joshua Palmer	110697			60000	60.015	33.5	84.5	2.768	53.2	7	3	0.20%	0.87	4.91835393
38	Justin Herbert	282	30	0.2			Nick Foles	Mike Williams	Michael Pittman Jr.	DeAndre Carter	Chase McLaughlin	120249			56500	56.535	29.5	89.5	2.7508	52.3	9	3	0.01%	0.87	4.791709485
39	Austin Ekeler	228	30	0.2			Keenan Allen	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Joshua Palmer	82347			60000	60.08	35.5	85.5	2.7647	53.47	7	3	0.20%	0.87	4.940219009
40	Keenan Allen	339	30	0.2			Mike Williams	Nick Foles	Michael Pittman Jr.	Chase McLaughlin	Joshua Palmer	148497			59000	59.54	25	90	2.7473	52.81	8	3	0.18%	0.87	5.012752342
41	Nick Foles	354	30	0.2			Nick Foles	Mike Williams	Michael Pittman Jr.	Chase McLaughlin	Joshua Palmer	120147			59000	59.655	29.5	88.5	2.7564	52.76	8	3	0.18%	0.87	4.966709485
42	Mike Williams	379	30	0.2			Mike Williams	Keenan Allen	Nick Foles	Chase McLaughlin	Parris Campbell	142828			58500	60.51	29	94	2.7447	55.13	7	3	0.56%	0.87	5.081376274
43	Deon Jackson	92	30	0.2			Nick Foles	Keenan Allen	Mike Williams	Chase McLaughlin	Parris Campbell	110068			58500	60.625	32	91.5	2.7538	55.08	7	3	0.56%	0.87	5.035333417
44	Michael Pittman Jr.	513	30	0.2			Keenan Allen	Nick Foles	Mike Williams	Chase McLaughlin	Parris Campbell	81718			58500	60.69	36	94	2.7505	55.35	7	3	0.56%	0.87	5.057198496
45	Zack Moss	62	30	0.2			Mike Williams	Keenan Allen	Nick Foles	Chase McLaughlin	Alec Pierce	142829			57500	60.13	33.5	95	2.7362	55.2	8	3	0.62%	0.87	5.114292941
46	Chase McLaughlin	419	30	0.2			Nick Foles	Keenan Allen	Mike Williams	Chase McLaughlin	Alec Pierce	110069			57500	60.245	30.5	89.5	2.7453	55.15	8	3	0.62%	0.87	5.068250084
47	Cameron Dicker	453	30	0.2			Keenan Allen	Nick Foles	Mike Williams	Chase McLaughlin	Alec Pierce	81719			57500	60.31	36	91	2.742	55.42	8	3	0.62%	0.87	5.090115163
48	Gerald Everett	128	30	0.2			Mike Williams	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Parris Campbell	134338			59500	65.17	40	103.5	2.748	64.49	7	3	0.39%	0.87	5.278409402
49	Joshua Palmer	71	30	0.2			Keenan Allen	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Alec Pierce	68189			59500	65.33	52	96.5	2.7569	65.22	7	3	0.48%	0.87	5.238792735
50	Parris Campbell	102	30	0.2			Mike Williams	Justin Herbert	Michael Pittman Jr.	Cameron Dicker	Alec Pierce	134339			58500	64.79	45	101.5	2.7395	64.56	8	3	0.43%	0.87	5.311326068
51	Alec Pierce	153	30	0.2			Justin Herbert	Keenan Allen	Michael Pittman Jr.	Cameron Dicker	Alec Pierce	11489			59500	69.2	55	101.5	2.75735	69.645	7	3	0.48%	0.87	5.405320513
52	Joshua Kelley	170	30	0.2			Justin Herbert	Mike Williams	Michael Pittman Jr.	Cameron Dicker	Parris Campbell	20938			59500	69.22	51	106.5	2.75425	69.135	7	3	0.39%	0.87	5.420759402
53	DeAndre Carter	96	30	0.2			Justin Herbert	Mike Williams	Michael Pittman Jr.	Cameron Dicker	Alec Pierce	20939			58500	68.84	53	104.5	2.74575	69.205	8	3	0.43%	0.87	5.453676068
54	Donald Parham Jr.	117	30	0.2			Nick Foles	Justin Herbert	Keenan Allen	Chase McLaughlin	Joshua Kelley	99990			60000	67.515	43.5	93.5	2.7622	61.56	6	3	1.56%	1.13	5.330985798
55	Jelani Woods	72	30	0.2			Keenan Allen	Justin Herbert	Nick Foles	Chase McLaughlin	Joshua Kelley	67230			60000	67.58	44.5	93	2.7589	61.83	6	3	1.56%	1.13	5.352850877
56	Los Angeles Chargers	264	30	0.2			Mike Williams	Justin Herbert	Nick Foles	Chase McLaughlin	Joshua Kelley	133380			59000	67.04	36.5	97	2.7415	61.17	7	3	1.40%	1.13	5.425384211
57	Indianapolis Colts	691	30	0.2			Nick Foles	Justin Herbert	Mike Williams	Chase McLaughlin	Joshua Kelley	100620			59000	67.155	38.5	94	2.7506	61.12	7	3	1.40%	1.13	5.379341353
58	Ashton Dulin	63	30	0.2			Nick Foles	Justin Herbert	Keenan Allen	Cameron Dicker	Joshua Kelley	100005			59500	66.715	46.5	91.5	2.7682	63.61	6	3	1.35%	1.37	5.29952381
Worksheet

Cell Formulas
Range	Formula
M2:M58	M2	=IF(Y2<=AGGREGATE(15,6,$Y$2:$Y$1263/($G$2:$G$1263=G2),VLOOKUP(G2,$A$29:$C$35,3,0)),"x","")
N2	N2	=IF(Y2<=AGGREGATE(15,6,$Y$2:$Y$1500/($H$2:$K$1500=H2),VLOOKUP(H2,$A$38:$C$58,3,0)),"x","")
N3:N58	N3	=IF(Y3<=AGGREGATE(15,6,$Y$2:$Y$1500/($H$2:$K$1500=H3),VLOOKUP($H$2,$A$38:$C$58,3,0)),"x","")
B29	B29	=COUNTIF(G2:G$1263,A29)
B30	B30	=COUNTIF(G2:G$1263,A30)
B31	B31	=COUNTIF(G2:G$1263,A31)
B32	B32	=COUNTIF(G2:G$1263,A32)
B33	B33	=COUNTIF(G2:G$1263,A33)
B34	B34	=COUNTIF(G2:G$1263,A34)
B35	B35	=COUNTIF(G2:G$1263,A35)
D29	D29	=IFERROR(C29/B26,0)
D30	D30	=IFERROR(C30/B26,0)
D31	D31	=IFERROR(C31/B26,0)
D32	D32	=IFERROR(C32/B26,0)
D33	D33	=IFERROR(C33/B26,0)
D34	D34	=IFERROR(C34/B26,0)
D35	D35	=IFERROR(C35/B26,0)
B38	B38	=COUNTIF(H2:$K$1263,A38)
B39	B39	=COUNTIF(H2:$K$1263,A39)
B40	B40	=COUNTIF(H2:$K$1263,A40)
B41	B41	=COUNTIF(H2:$K$1263,A41)
B42	B42	=COUNTIF(H2:$K$1263,A42)
B43	B43	=COUNTIF(H2:$K$1263,A43)
B44	B44	=COUNTIF(H2:$K$1263,A44)
B45	B45	=COUNTIF(H2:$K$1263,A45)
B46	B46	=COUNTIF(H2:$K$1263,A46)
B47	B47	=COUNTIF(H2:$K$1263,A47)
B48	B48	=COUNTIF(H2:$K$1263,A48)
B49	B49	=COUNTIF(H2:$K$1263,A49)
B50	B50	=COUNTIF(H2:$K$1263,A50)
B51	B51	=COUNTIF(H2:$K$1263,A51)
B52	B52	=COUNTIF(H2:$K$1263,A52)
B53	B53	=COUNTIF(H2:$K$1263,A53)
B54	B54	=COUNTIF(H2:$K$1263,A54)
B55	B55	=COUNTIF(H2:$K$1263,A55)
D38	D38	=IFERROR(C38/B26,0)
D39	D39	=IFERROR(C39/B26,0)
D40	D40	=IFERROR(C40/B26,0)
D41	D41	=IFERROR(C41/B26,0)
D42	D42	=IFERROR(C42/B26,0)
D43	D43	=IFERROR(C43/B26,0)
D44	D44	=IFERROR(C44/B26,0)
D45	D45	=IFERROR(C45/B26,0)
D46	D46	=IFERROR(C46/B26,0)
D47	D47	=IFERROR(C47/B26,0)
D48	D48	=IFERROR(C48/B26,0)
D49	D49	=IFERROR(C49/B26,0)
D50	D50	=IFERROR(C50/B26,0)
D51	D51	=IFERROR(C51/B26,0)
D52	D52	=IFERROR(C52/B26,0)
D53	D53	=IFERROR(C53/B26,0)
D54	D54	=IFERROR(C54/B26,0)
D55	D55	=IFERROR(C55/B26,0)
B56	B56	=COUNTIF(H2:$K$1263,A56)
C56:C58	C56	=IF(A56="","",30)
D56	D56	=IFERROR(C56/B26,0)
B57	B57	=COUNTIF(H2:$K$1263,A57)
D57	D57	=IFERROR(C57/B26,0)
B58	B58	=IF(COUNTIF(H2:$K$1263,A58)=0,"",COUNTIF(H2:$K$1263,A58))
D58	D58	=IFERROR(C58/B26,0)

 

[cspengel]

So adding COUNTIF seems to do what I want for 1 player.

=IF(COUNTIF(H2:K2,$A$42)=1,IF(Y2<=AGGREGATE(15,6,$Y$2:$Y$1500/($H$2:$K$1500=$A$42),VLOOKUP($A$42,$A$38:$C$58,3,0)),"x",""),"")

How can I add onto this formula to go through each player in the list? I tried using + at the end then doing formula again, but that does not seem to be the proper method. Thanks!

 

[cspengel]

This is the formula I ended up with.

=IFERROR(IF(COUNTIF(H2:K2,$A$38)=1,IF(X2<=AGGREGATE(15,6,$X$2:$X$6206/($H$2:$K$6206=$A$38),VLOOKUP($A$38,$A$38:$C$58,3,0)),"x","1"),""),"LINES SET TO 0")

I was wondering if it's possible to also hide hidden rows for the AGGREGATE function? I have it set for 6 which is to ignore error values, which is neccessary. I tried using number 7 aswell, but then it isn't updating to put an x in the visible rows.