allow duplicate entries using Lookup 2/1((Countif(...

[gtd526]

Hello,
Want to add allowing duplicate entries and original entries to G:G using the existing formula.
thank you

The Whole Enchilada.xlsm
	F	G	H	I	J
27	6/14/24	SOXL	$30.30	$59.01	$114.84
28	6/18/24	QQQU	$33.40	$34.05	$2.28
29	6/18/24	AIBU	$27.20	$28.05	$3.66
30	6/21/24	WEBL	$14.28	$16.54	$19.89
31
32
33
34
Capital Gains Tax

Cell Formulas
Range	Formula
F30:F34	F30	=IF(G30="","",TODAY())
G30:G34	G30	=IFERROR(LOOKUP(2, 1/((COUNTIF(G$26:$G29,'Roth IRA'!$A$9:$A$15)=0)*('Roth IRA'!$A$9:$A$15<>"")*('Roth IRA'!$T$9:$T$15="sold")),'Roth IRA'!$A$9:$A$15),"")
H30:H34	H30	=IF(G30<>"",AVERAGEIF('Roth IRA'!$A$9:$A$15,G30,'Roth IRA'!$E$9:$E$15),"")
I30:I34	I30	=IF(G30<>"",AVERAGEIF('Roth IRA'!$A$9:$A$15,G30,'Roth IRA'!$F$9:$F$15),"")
J30:J34	J30	=IF(G30="","",IF(VLOOKUP(G30,'Roth IRA'!$A$9:$K$15,11,0)<0,VLOOKUP(G30,'Roth IRA'!$A$9:$K$15,11,0),VLOOKUP(G30,'Roth IRA'!$A$9:$R$15,18,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gtd526]

Here is the worksheet associated with the above formula.

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
8	Symbol	Sector	Type	Qty	Price	CURRENT	MAX Value	MIN Value	Cost	Balance	G/L $	G/L %	Weight(%)	Buy Date	Days	Long Term	Cap Gain %	Cap Ga Cost	Adj Gain	Status
9	WEBL	Internet 3x	Long	88	$14.28	$16.54	$19.80	$16.41	$1,256.64	$1,455.52	$198.88	15.83%	26.13%	12/14/23	190	Short	10%	$19.89	$178.99	sold
10	TECL	Tech 3x	Long	18	$65.04	$100.98	$104.66	$58.15	$1,170.72	$1,817.64	$646.92	55.26%	24.35%	12/14/23	190	Short	10%	$64.69	$582.23	open
11	SOXL	Semis 3x	Long	12	$65.00	$60.20	$65.24	$60.20	$780.00	$722.40	-$57.60	-7.38%	16.22%	6/20/24	1	Short	10%	$0.00	-$57.60	sold
12	UEC	Uranium Energy Corp	Long	128	$6.29	$6.38	$6.38	$6.24	$805.12	$816.64	$11.52	1.43%	16.74%	6/20/24	1	Short	10%	$1.15	$10.37	open
13	SRUUF	Sprott Physical Uranium	Long	39	$20.42	$20.03	$20.45	$20.03	$796.38	$781.17	-$15.21	-1.91%	16.56%	6/20/24	1	Short	10%	$0.00	-$15.21	open
14
15
Roth IRA