Filter Dynamic Array by Range, Differently Sized Arrays

[TheMacroNoob]

I have a spill array that is pulling IDs from another sheet.

I already have a list of IDs on one side of my visual, and I want to avoid duplicates.

The list of my array formula is longer than the existing list I want to compare, and that's tripping me up.

My formula with the spill array: =SORT(FILTER(IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],""),IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],"")<>"",""),,1)

Sale Forecast Comparison.xlsx
	B	C	D	E	F	G	H	I	J	K
14		Comparing to			Problem				Desired Result
15	12/29/2020	100	Added to 2020	-$2,091,410	100	-$2,091,410	$137,502,863		254
16		442	Moved to 2021	-$3,602,847	254	-$80,494	$133,819,521		330
17		503	Moved to 2021	-$13,302,174	330	$98,346	$120,615,693		411
18		512	Moved to 2021	-$7,910,367	411	$177,180	$112,882,506		412
19		518	Moved to 2021	-$4,133,363	412	$98,000	$108,847,142		413
20		521	Moved to 2021	-$1,049,427	413	$85,750	$107,883,465		458
21		522	Moved to 2021	-$2,465,580	458	$147,000	$105,564,885		459
22		527	Moved to 2021	-$4,452,789	459	$334,780	$101,446,876		509
23		603	Moved to 2021	-$6,936,462	503	-$135,000	$94,375,414		576
24		604	Moved to 2021	-$3,566,107	509	-$269,471	$90,539,837		597
25		608	Moved to 2021	-$7,678,027	512	$1,440,896	$84,302,707		605
26		623	Added to 2020	$3,380,511	521	-$843,483	$86,839,735		606
27		647	Added to 2020	$4,072,970	576	-$17,587	$90,895,118		613
28		648	Added to 2020	-$821,904	597	-$504,000	$89,569,214		614
29		920	Moved to 2021	-$2,552,504	605	-$161,467	$86,855,243		621
30	3/24/2021				606	$180,775			635
31					613	$258,000			904
32					614	-$341,125			943
33					621	$1,805,739			946
34					623	$3,380,511
35					635	-$492,855
36					647	$4,072,970
37					648	-$821,904
38					904	$2,139,446
39					920	$48,223
40	5/6/2021				943	$665,746
41					946	-$147,000
42
Summary

Cell Formulas
Range	Formula
D14:D42	D14	=IFNA(IF(XLOOKUP(C14,Year_Changes[PID],Year_Changes[Changes D1-D2])=1,"Moved to 2021","Added to 2020"),"")
E14	E14	=IF(COUNTIFS(D14,"*Moved*"),-XLOOKUP(C14,Data[PID],Data[P1],""),XLOOKUP(C14,Data[PID],Data[P1],""))
C15:C29	C15	=SORT(FILTER(IFS(Year_Changes[Changes D1-D2]=1,Year_Changes[PID],Year_Changes[Changes D1-D2]="Property Added",Year_Changes[PID],TRUE,""),IFS(Year_Changes[Changes D1-D2]=1,Year_Changes[PID],Year_Changes[Changes D1-D2]="Property Added",Year_Changes[PID],TRUE,"")<>"",""),,1)
E15:E42	E15	=IF(COUNTIFS(D15,"*Moved*"),-XLOOKUP(C15,Data[PID],Data[P2],""),XLOOKUP(C15,Data[PID],Data[P2],""))
F15:F41	F15	=SORT(FILTER(IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],""),IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],"")<>"",""),,1)
H15	H15	=$H$12+E15+G15
H16:H29	H16	=H15+E16+G16
G15:G42	G15	=XLOOKUP(F15,Data[PID],Proceeds_Changes[Changes P1-P2],"")
Dynamic array formulas.

 

[Peter_SSs]

Untested as I do not have your data table, but try

=LET(L,SORT(FILTER(IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],""),IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],"")<>"",""),,1),FILTER(L,ISNA(MATCH(L,C15#,0))))

 

[TheMacroNoob]

Untested as I do not have your data table, but try

=LET(L,SORT(FILTER(IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],""),IF(Proceeds_Changes[Changes P1-P2]<>0,Proceeds_Changes[PID],"")<>"",""),,1),FILTER(L,ISNA(MATCH(L,C15#,0))))

Peter you are a lifesaver, this works perfectly despite being untested. I haven't used functions like MATCH with the FILTER formula; I clearly have some work to do.

Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.