Top numbers for two columns? Intersection?

[cspengel]

What I am trying to do is the following:

If Column L does not contain "LINES SET TO 0" then select the highest from column M and Column N where they intersect. By intersect I mean where they are the highest for both categories respectively. In the example I show in the xl2bb, I do this with helper columns in AO, AP, and AQ using the top 25 percent for each column. If it is true for both columns, then that is where the intersection lies. My issue with using percentage is that the number of rows being selected changes based on percentage used. I would like the number of rows that result from the intersection to always be static. So for example, If I have the number "40" in range C29, then it will select the 40 highest rows that intersect. Any suggestions would be appreciated!

REVISION36.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	MVP	FLEX	FLEX	FLEX	FLEX	MVP Selection	Ceiling Projection↑	Ownership L|U	Dak Prescott	Tom Brady	CeeDee Lamb	Ezekiel Elliott	Tony Pollard	Mike Evans	Chris Godwin	Leonard Fournette	Brett Maher	Russell Gage	Dalton Schultz	Michael Gallup	Ryan Succop	Dallas Cowboys	Rachaad White	Julio Jones	Tampa Bay Buccaneers	Noah Brown	Cade Otton	T.Y. Hilton	Jake Ferguson	Malik Davis				Lines Set To 0
2	Dak Prescott	Tom Brady	Brett Maher	Tampa Bay Buccaneers	Dalton Schultz	1	116.3812066	0.46%	0	1	0	0	0	0	0	0	1	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	FALSE	FALSE	0
3	Dak Prescott	Tom Brady	Brett Maher	Cade Otton	Dallas Cowboys	LINES SET TO 0	102.2808661	0.31%	0	1	0	0	0	0	0	0	1	0	0	0	0	1	0	0	0	0	LINES SET TO 0	0	0	0	0	0	0	1	FALSE	FALSE	0
4	Dak Prescott	Tom Brady	Brett Maher	Dalton Schultz	Dallas Cowboys	1	116.3309874	0.50%	0	1	0	0	0	0	0	0	1	0	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	FALSE	FALSE	0
5	Dak Prescott	Tom Brady	Brett Maher	Cade Otton	Dalton Schultz	LINES SET TO 0	103.9211787	1.01%	0	1	0	0	0	0	0	0	1	0	1	0	0	0	0	0	0	0	LINES SET TO 0	0	0	0	0	0	0	1	FALSE	FALSE	0
6	Dak Prescott	Tom Brady	Brett Maher	Tampa Bay Buccaneers	Rachaad White	1	104.9772537	0.31%	0	1	0	0	0	0	0	0	1	0	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	FALSE	FALSE	0
7	Dak Prescott	Tom Brady	Leonard Fournette	Ryan Succop	Dallas Cowboys	1	125.3902108	0.36%	0	1	0	0	0	0	0	1	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	TRUE	FALSE	0
8	Dak Prescott	Tom Brady	Leonard Fournette	Tampa Bay Buccaneers	Brett Maher	1	123.7967263	0.76%	0	1	0	0	0	0	0	1	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	TRUE	FALSE	0
9	Dak Prescott	Tom Brady	Leonard Fournette	Tampa Bay Buccaneers	Ryan Succop	1	124.9535338	0.33%	0	1	0	0	0	0	0	1	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	TRUE	FALSE	0
10	Dak Prescott	Tom Brady	Brett Maher	Cade Otton	Rachaad White	LINES SET TO 0	95.53929114	0.69%	0	1	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	LINES SET TO 0	0	0	0	0	0	0	1	FALSE	FALSE	0
11	Dak Prescott	Tom Brady	Leonard Fournette	Dalton Schultz	Ryan Succop	1	125.8302523	1.18%	0	1	0	0	0	0	0	1	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	TRUE	FALSE	0
12	Dak Prescott	Tom Brady	Tony Pollard	Cade Otton	Brett Maher	LINES SET TO 0	105.1938994	0.89%	0	1	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	LINES SET TO 0	0	0	0	0	0	0	1	FALSE	FALSE	0
13	Dak Prescott	Tom Brady	Leonard Fournette	Cade Otton	Brett Maher	LINES SET TO 0	117.508351	1.66%	0	1	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	LINES SET TO 0	0	0	0	0	0	0	1	FALSE	FALSE	0
Worksheet

Cell Formulas
Range	Formula
O2:O13	O2	=IF(COUNTIF(H2:K2,$A$38)>0,IF(COUNTIF($C$38,">0"),"1","LINES SET TO 0"),0)
P2:P13	P2	=IF(COUNTIF(H2:K2,$A$39)>0,IF(COUNTIF($C$39,">0"),"1","LINES SET TO 0"),0)
Q2:Q13	Q2	=IF(COUNTIF(H2:K2,$A$40)>0,IF(COUNTIF($C$40,">0"),"1","LINES SET TO 0"),0)
R2:R13	R2	=IF(COUNTIF(H2:K2,$A$41)>0,IF(COUNTIF($C$41,">0"),"1","LINES SET TO 0"),0)
S2:S13	S2	=IF(COUNTIF(H2:K2,$A$42)>0,IF(COUNTIF($C$42,">0"),"1","LINES SET TO 0"),0)
T2:T13	T2	=IF(COUNTIF(H2:K2,$A$43)>0,IF(COUNTIF($C$43,">0"),"1","LINES SET TO 0"),0)
U2:U13	U2	=IF(COUNTIF(H2:K2,$A$44)>0,IF(COUNTIF($C$44,">0"),"1","LINES SET TO 0"),0)
V2:V13	V2	=IF(COUNTIF(H2:K2,$A$45)>0,IF(COUNTIF($C$45,">0"),"1","LINES SET TO 0"),0)
W2:W13	W2	=IF(COUNTIF(H2:K2,$A$46)>0,IF(COUNTIF($C$46,">0"),"1","LINES SET TO 0"),0)
X2:X13	X2	=IF(COUNTIF(H2:K2,$A$47)>0,IF(COUNTIF($C$47,">0"),"1","LINES SET TO 0"),0)
Y2:Y13	Y2	=IF(COUNTIF(H2:K2,$A$48)>0,IF(COUNTIF($C$48,">0"),"1","LINES SET TO 0"),0)
Z2:Z13	Z2	=IF(COUNTIF(H2:K2,$A$49)>0,IF(COUNTIF($C$49,">0"),"1","LINES SET TO 0"),0)
AA2:AA13	AA2	=IF(COUNTIF(H2:K2,$A$50)>0,IF(COUNTIF($C$50,">0"),"1","LINES SET TO 0"),0)
AB2:AB13	AB2	=IF(COUNTIF(H2:K2,$A$51)>0,IF(COUNTIF($C$51,">0"),"1","LINES SET TO 0"),0)
AC2:AC13	AC2	=IF(COUNTIF(H2:K2,$A$52)>0,IF(COUNTIF($C$52,">0"),"1","LINES SET TO 0"),0)
AD2:AD13	AD2	=IF(COUNTIF(H2:K2,$A$53)>0,IF(COUNTIF($C$53,">0"),"1","LINES SET TO 0"),0)
AE2:AE13	AE2	=IF(COUNTIF(H2:K2,$A$54)>0,IF(COUNTIF($C$54,">0"),"1","LINES SET TO 0"),0)
AF2:AF13	AF2	=IF(COUNTIF(H2:K2,$A$55)>0,IF(COUNTIF($C$55,">0"),"1","LINES SET TO 0"),0)
AG2:AG13	AG2	=IF(COUNTIF(H2:K2,$A$56)>0,IF(COUNTIF($C$56,">0"),"1","LINES SET TO 0"),0)
AH2:AH13	AH2	=IF(COUNTIF(H2:K2,$A$57)>0,IF(COUNTIF($C$57,">0"),"1","LINES SET TO 0"),0)
AI2:AI13	AI2	=IF(COUNTIF(H2:K2,$A$58)>0,IF(COUNTIF($C$58,">0"),"1","LINES SET TO 0"),0)
AJ2:AJ13	AJ2	=IF(COUNTIF(H2:K2,$A$59)>0,IF(COUNTIF($C$59,">0"),"1","LINES SET TO 0"),0)
AK2:AK13	AK2	=IF(COUNTIF(H2:K2,$A$60)>0,IF(COUNTIF($C$60,">0"),"1","LINES SET TO 0"),0)
AL2:AL13	AL2	=IF(COUNTIF(H2:K2,$A$61)>0,IF(COUNTIF($C$61,">0"),"1","LINES SET TO 0"),0)
AM2:AM13	AM2	=IF(COUNTIF(H2:K2,$A$62)>0,IF(COUNTIF($C$62,">0"),"1","LINES SET TO 0"),0)
AN2:AN13	AN2	=COUNTIF(O2:AM2,"LINES SET TO 0")
AO2:AO13	AO2	=IF(L2<>"LINES SET TO 0",M2>=PERCENTILE($M$2:$M$358,0.75),FALSE)
AP2:AP13	AP2	=IF(L2<>"LINES SET TO 0",N2>=PERCENTILE($N$2:$N$358,0.75),FALSE)
AQ2:AQ13	AQ2	=IF(AO2=TRUE,IF(AP2=TRUE,1,0),0)
L2:L13	L2	=IF(AN2>0,"LINES SET TO 0",IFERROR(IF(N2>=AGGREGATE(14,6,$N$2:$N$1075/(($G$2:$G$1075=G2)*($AN$2:$AN$1075=0)),VLOOKUP(G2,$A$29:$C$35,3,0)),"x","1"),"LINES SET TO 0"))

 

[cspengel]

Basically I need to combine these two formulas

=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),0))

=IF(L2<>"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0))

 

[Sufiyan97]

Basically I need to combine these two formulas

=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),0))

=IF(L2<>"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0))

See if this helps

=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0)))

 

[cspengel]

Unfortunately, I am not getting the results I am expecting. Maybe I am thinking about this the wrong way. I have 304 rows in this test. For columns P and S I have the top 25 percent of values highlighted. Only 27 overlap as shown by conditional formatting. However, with your formula in column T which is supposed to select the highest 30, only 13 are matching what the conditional formatting has.

REVISION36.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	MVP	FLEX	FLEX	FLEX	FLEX	MVP Selection	Salary	PROJ	Floor Projection	Ceiling Projection↑	Target	Depth Total	Ownership L|U	MREXCEL
2	Daniel Jones	Saquon Barkley	Kirk Cousins	Graham Gano	Daniel Bellinger	1	60000	79.88	45.33281785	102.786812	11.185	5	2.41%	0
3	Daniel Jones	Saquon Barkley	Kirk Cousins	New York Giants	Daniel Bellinger	1	60000	73.14	46.02183533	101.7197788	5.915	5	4.88%	0
4	Daniel Jones	Kirk Cousins	Dalvin Cook	Irv Smith Jr.	Minnesota Vikings	1	57000	70.52	53.08487147	106.4242347	6.415	6	2.68%	0
5	Daniel Jones	Saquon Barkley	Kirk Cousins	Irv Smith Jr.	Daniel Bellinger	x	56500	58.87	43.00592554	101.6944003	8.705	6	10.26%	0
6	Daniel Jones	Justin Jefferson	Kirk Cousins	Irv Smith Jr.	Minnesota Vikings	1	60000	93.24	55.20809197	111.2139506	8.275	6	2.07%	1
7	Daniel Jones	Kirk Cousins	Dalvin Cook	Graham Gano	K.J. Osborn	1	59500	70.17	50.79009771	108.7816964	11.465	7	1.79%	1
8	Daniel Jones	Kirk Cousins	Dalvin Cook	New York Giants	K.J. Osborn	1	59500	68.97	55.83735341	108.5229886	6.195	7	3.63%	1
9	Daniel Jones	Kirk Cousins	Dalvin Cook	K.J. Osborn	Minnesota Vikings	1	60000	77.28	54.73885876	107.344174	7.595	7	3.54%	0
10	Daniel Jones	Kirk Cousins	Dalvin Cook	Adam Thielen	Daniel Bellinger	x	59500	80.33	48.67213225	103.5526149	6.055	6	9.39%	0
11	Daniel Jones	Saquon Barkley	Kirk Cousins	Irv Smith Jr.	Darius Slayton	x	60000	61.93	44.03645885	109.5740094	8.895	6	8.22%	1
12	Daniel Jones	Saquon Barkley	Kirk Cousins	Irv Smith Jr.	Richie James Jr.	x	58000	64.7	47.59534323	111.5259949	12.345	8	16.67%	1
13	Daniel Jones	Kirk Cousins	Dalvin Cook	Irv Smith Jr.	Adam Thielen	1	58500	75.16	55.10502261	115.2645108	6.075	7	4.14%	1
14	Daniel Jones	Kirk Cousins	Dalvin Cook	Irv Smith Jr.	K.J. Osborn	x	56000	72.67	49.20245624	107.7045698	8.985	8	7.64%	1
15	Daniel Jones	Saquon Barkley	Richie James Jr.	T.J. Hockenson	Minnesota Vikings	1	59500	56.4	37.1882506	102.053119	4.545	7	1.63%	0
16	Daniel Jones	Justin Jefferson	Kirk Cousins	Irv Smith Jr.	K.J. Osborn	1	59000	78.88	54.73132932	110.9566555	10.845	8	5.90%	1
17	Daniel Jones	Kirk Cousins	Dalvin Cook	Richie James Jr.	Graham Gano	1	59000	67.99	49.07278693	104.5542804	13.905	7	4.99%	0
18	Daniel Jones	Kirk Cousins	Dalvin Cook	New York Giants	Richie James Jr.	x	59000	77.68	51.98325461	103.5382962	8.635	7	10.12%	0
19	Daniel Jones	Saquon Barkley	Dalvin Cook	Irv Smith Jr.	Isaiah Hodgins	1	59000	78.56	41.66823369	113.1732413	5.965	7	3.94%	1
20	Daniel Jones	Saquon Barkley	Dalvin Cook	Irv Smith Jr.	Darius Slayton	1	58500	69.49	38.71571106	110.8753374	7.595	6	5.74%	1
21	Daniel Jones	Saquon Barkley	Dalvin Cook	Irv Smith Jr.	Richie James Jr.	x	56500	82	39.83369851	107.849967	11.045	8	11.64%	1
22	Daniel Jones	Justin Jefferson	Saquon Barkley	Irv Smith Jr.	K.J. Osborn	1	60000	61.76	45.93490565	114.9484435	10.465	8	3.23%	1
23	Daniel Jones	Kirk Cousins	Dalvin Cook	Richie James Jr.	Minnesota Vikings	x	59500	73.74	53.90918857	104.3233693	10.035	7	9.85%	0
24	Daniel Jones	Justin Jefferson	Saquon Barkley	Irv Smith Jr.	Daniel Bellinger	1	58000	65.04	38.92843187	106.6683544	9.265	6	5.54%	0
25	Daniel Jones	Kirk Cousins	Adam Thielen	Irv Smith Jr.	Isaiah Hodgins	1	55000	70.13	49.95045459	103.1546499	2.265	8	1.78%	0
26	Daniel Jones	Kirk Cousins	Richie James Jr.	T.J. Hockenson	Adam Thielen	1	60000	74.93	47.51824433	102.0431424	4.585	8	4.61%	0
27	Daniel Jones	Kirk Cousins	Dalvin Cook	Richie James Jr.	K.J. Osborn	x	58500	81.59	49.07979804	103.1656502	12.605	9	28.12%	0
28	Daniel Jones	Justin Jefferson	Saquon Barkley	Irv Smith Jr.	Richie James Jr.	x	59500	61.67	40.86843157	115.9683468	12.905	8	8.99%	1
Worksheet

Cell Formulas
Range	Formula
L2:L28	L2	=IF(AZ2>0,"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/(($G$2:$G$827=G2)*($AZ$2:$AZ$827=0)),VLOOKUP(G2,$A$29:$C$35,3,0)),"x","1"),"LINES SET TO 0"))
T2:T28	T2	=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S:S	Cell Value	top 25% values	text	NO
P:P	Cell Value	top 25% values	text	NO