Get the Row number for Matched cell value

[gtd526]

Hello,
Want to modify the formula regarding 9:9 in N6. The Row (9:9) needs to be modified due to sorting, the A9 value will change rows and may not be 9:9.
I have the following formula =MATCH(N3,A:A,0)&":"&MATCH(N3,A:A,0) that will result 9:9, but I have trouble inserting it into the formula.
Thanks

The Whole Enchilada.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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1												Max	Min	First	Last	Max	Min	First	Last	Max	Min	First	Last	Max	Min	First	Last	Max	Min	First	Last	Max	Min	First	Last
2												111.8%	66.7%	61.2%	111.8%	-0.2%	-18.5%	-15.3%	-1.0%	7.4%	-12.9%	-12.9%	7.4%	5.2%	0.0%	0.4%	5.2%	6.7%	0.0%	6.7%	5.4%	0.0%	-4.1%	-2.0%	-4.1%
3												TECL		TECL		FNGG		FNGG		WEBL		WEBL		QTUM		QTUM		SRUUF		SRUUF		UEC		UEC
4														50.63%				14.30%				20.30%				4.80%				-1.36%				-2.03%
5														Weekly Diff				Weekly Diff				Weekly Diff				Weekly Diff				Weekly Diff				Weekly Diff
6														10.8%				4.2%				4.2%				0.0%				-1.1%				-2.0%
7	SCHWAB
8	Symbol	Sector	Type	Qty	Price	CURRENT	Cost	Balance	G/L $	G/L %	8/19/2023	8/26/2023	9/2/2023	9/9/2023	9/16/2023	9/23/2023	9/30/2023	10/7/2023	10/14/2023	10/21/2023	10/28/2023	11/4/2023	11/11/2023
9	TECL	Tech 3x	Long	45	$24.10	$51.04	$1,084.50	$2,296.80	$1,212.30	111.78%	61.2%	71.4%	94.1%	82.6%	69.8%	56.8%	54.8%	66.5%	92.9%	76.5%	66.7%	101.0%	111.8%
10	WEBL	Internet 3x	Long	62	$9.36	$10.05	$580.32	$623.10	$42.78	7.37%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	-12.9%	3.2%	7.4%
11	SRUUF	Sprott Physical Uranium	Long	48	$16.40	$17.43	$794.15	$836.64	$42.49	5.35%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	6.7%	6.6%	6.4%	5.4%
12	QTUM	Defiance Quantum	Long	11	$44.85	$47.18	$493.35	$518.98	$25.63	5.20%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.00%	0.4%	5.2%	5.2%
13	FNGG	FANGs 2x	Long	15	$75.13	$74.81	$1,127.01	$1,116.18	-$10.83	-0.96%	-15.3%	-10.8%	-0.2%	-1.6%	-3.9%	-13.0%	-12.1%	-6.1%	-7.7%	-14.0%	-18.5%	-5.1%	-1.0%
14	UEC	Uranium Energy Corp	Long	130	$5.90	$5.66	$767.00	$735.80	-$31.20	-4.07%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	-2.0%	-4.1%
15
16
17																					Row for "TECL" (N3)
18																					9:9	MATCH(N3,A:A,0)&":"&MATCH(N3,A:A,0)
19
Sheet1

Cell Formulas
Range	Formula
L2	L2	=MAX(INDEX(INDIRECT("U"&MATCH(L3,A:A,0)):$ZP$15,MATCH(L3,$A$9:$A$15,),))
M2	M2	=MIN(INDEX(INDIRECT("U"&MATCH(L$3,$A:$A,0)):$ZP$15,MATCH(L$3,$A$9:$A$15,),))
N2	N2	=INDEX(INDIRECT("K"&MATCH(N3,A:A,0)):$K$79,MATCH(N3,INDIRECT("A"&MATCH(N3,A:A,0)):$A$75,0))
O2	O2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(N3,A:A,0)):(INDIRECT("ZZ"&MATCH(N3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(N3,A:A,0)):(INDIRECT("ZZ"&MATCH(N3,A:A,0)))))
P2,AF2,AB2,X2,T2	P2	=MAX(INDEX($K$9:$ZP$15,MATCH(P3,$A$9:$A$15,),))
Q2,AG2,AC2,Y2,U2	Q2	=MIN(INDEX($K$9:$ZP$15,MATCH(P3,$A$9:$A$15,),))
R2	R2	=INDEX(INDIRECT("K"&MATCH(R3,A:A,0)):$K$15,MATCH(R3,INDIRECT("A"&MATCH(R3,A:A,0)):$A$15,0))
S2	S2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(R3,A:A,0)):(INDIRECT("ZZ"&MATCH(R3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(R3,A:A,0)):(INDIRECT("ZZ"&MATCH(N3,A:A,0)))))
V2	V2	=INDEX(INDIRECT("U"&MATCH(V3,A:A,0)):$U$15,MATCH(V3,INDIRECT("A"&MATCH(V3,A:A,0)):$A$15,0),0)
W2	W2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(V3,A:A,0)):(INDIRECT("ZZ"&MATCH(V3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(V3,A:A,0)):(INDIRECT("ZZ"&MATCH(V3,A:A,0)))))
Z2	Z2	=INDEX(INDIRECT("U"&MATCH(Z3,A:A,0)):$U$15,MATCH(Z3,INDIRECT("A"&MATCH(Z3,A:A,0)):$A$15,0),0)
AA2	AA2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(Z3,A:A,0)):(INDIRECT("ZZ"&MATCH(Z3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(Z3,A:A,0)):(INDIRECT("ZZ"&MATCH(Z3,A:A,0)))))
AD2	AD2	=INDEX(INDIRECT("T"&MATCH(AD3,A:A,0)):$T$15,MATCH(AD3,INDIRECT("A"&MATCH(AD3,A:A,0)):$A$15,0),0)
AE2	AE2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(AD3,A:A,0)):(INDIRECT("ZZ"&MATCH(AD3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(AD3,A:A,0)):(INDIRECT("ZZ"&MATCH(AD3,A:A,0)))))
AH2	AH2	=INDEX(INDIRECT("V"&MATCH(AH3,A:A,0)):$V$15,MATCH(AH3,INDIRECT("A"&MATCH(AH3,A:A,0)):$A$15,0),0)
AI2	AI2	=LOOKUP(2,1/(INDIRECT("A"&MATCH(AH3,A:A,0)):(INDIRECT("ZZ"&MATCH(AH3,A:A,0)))<>"")*(INDIRECT("A"&MATCH(AH3,A:A,0)):(INDIRECT("ZZ"&MATCH(AH3,A:A,0)))))
N4,AH4,AD4,Z4,V4,R4	N4	=O2-N2
N6	N6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(N3,A:A,0)):INDIRECT("ZZ"&MATCH(N3,A:A,0))<>""),INDIRECT("A"&MATCH(N3,A:A,0)):INDIRECT("ZZ"&MATCH(N3,A:A,0)))-INDEX(9:9, AGGREGATE(14, 6, COLUMN(A:ZZ)/(INDIRECT("A"&MATCH(N3,A:A,0)):INDIRECT("ZZ"&MATCH(N3,A:A,0))<>""), 2))
R6	R6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(R3,A:A,0)):INDIRECT("ZZ"&MATCH(R3,A:A,0))<>""),INDIRECT("A"&MATCH(R3,A:A,0)):INDIRECT("ZZ"&MATCH(R3,A:A,0)))-INDEX(13:13, AGGREGATE(14, 6, COLUMN(A:ZP)/(INDIRECT("A"&MATCH(R3,A:A,0)):INDIRECT("ZZ"&MATCH(R3,A:A,0))<>""), 2))
V6	V6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(V3,A:A,0)):INDIRECT("ZZ"&MATCH(V3,A:A,0))<>""),INDIRECT("A"&MATCH(V3,A:A,0)):INDIRECT("ZZ"&MATCH(V3,A:A,0)))-INDEX(10:10, AGGREGATE(14, 6, COLUMN(A:ZP)/(INDIRECT("A"&MATCH(V3,A:A,0)):INDIRECT("ZZ"&MATCH(V3,A:A,0))<>""), 2))
Z6	Z6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(Z3,A:A,0)):INDIRECT("ZZ"&MATCH(Z3,A:A,0))<>""),INDIRECT("A"&MATCH(Z3,A:A,0)):INDIRECT("ZZ"&MATCH(Z3,A:A,0)))-INDEX(12:12, AGGREGATE(14, 6, COLUMN(A:ZP)/(INDIRECT("A"&MATCH(Z3,A:A,0)):INDIRECT("ZZ"&MATCH(Z3,A:A,0))<>""), 2))
AD6	AD6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(AD3,A:A,0)):INDIRECT("ZZ"&MATCH(AD3,A:A,0))<>""),INDIRECT("A"&MATCH(AD3,A:A,0)):INDIRECT("ZZ"&MATCH(AD3,A:A,0)))-INDEX(11:11, AGGREGATE(14, 6, COLUMN(A:ZP)/(INDIRECT("A"&MATCH(AD3,A:A,0)):INDIRECT("ZZ"&MATCH(AD3,A:A,0))<>""), 2))
AH6	AH6	=LOOKUP(2, 1/(INDIRECT("A"&MATCH(AH3,A:A,0)):INDIRECT("ZZ"&MATCH(AH3,A:A,0))<>""),INDIRECT("A"&MATCH(AH3,A:A,0)):INDIRECT("ZZ"&MATCH(AH3,A:A,0)))-INDEX(14:14, AGGREGATE(14, 6, COLUMN(A:ZP)/(INDIRECT("A"&MATCH(AH3,A:A,0)):INDIRECT("ZZ"&MATCH(AH3,A:A,0))<>""), 2))
F9:F15	F9	=IF(A9<>"",VLOOKUP(A9,'All Tickers'!$I:$J,2,0),"")
G12:G15,G9:G10	G9	=IF($A9="","",D9*E9)
H14:H15,H9:H12	H9	=IF($A9="","",$D9*$F9)
I9:I15	I9	=IF($A9="","",H9-G9)
J9:J15	J9	=IF($A9="","",($H9-$G9)/$G9)
G11	G11	=IF($A11="","",D11*E11)+6.95
H13	H13	=IF($A13="","",$D13*$F13)-5.97
B9:B15	B9	=IF(COUNTIF(Sectors!$A$2:$ABY$47,$A9)=1,INDEX(Sectors!$A$2:$ABY$2,MAX((Sectors!$A$2:$ABY$47=$A9)*(COLUMN(Sectors!$A$2:$ABY$2)))),"")
W8	W8	=V8+7
W9:W15	W9	=$J9
U18	U18	=MATCH(N3,A:A,0)&":"&MATCH(N3,A:A,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RoryA]

You'd need to wrap that in INDIRECT:

INDIRECT(MATCH(N3,A:A,0)&":"&MATCH(N3,A:A,0))

I'd imagine all those volatile formulas will make your workbook pretty slow.

 

[gtd526]

You'd need to wrap that in INDIRECT:

INDIRECT(MATCH(N3,A:A,0)&":"&MATCH(N3,A:A,0))

I'd imagine all those volatile formulas will make your workbook pretty slow.

Thank you.
I don't see much difference in processing all those formulas. I purchased an upper-end laptop to take care of certain issues or slowness.