Index Aggregate help

[hajiali]

Hello everyone need some help with the below.

I want to be able to pull all E# that have FHL in Decld Column N and that Leave in column K is not equal to Leave list in column P.

Results in for the example below is in Column R. the formula in column S does what I need however its not effective as if I need to add to my Leave list I have to keep updating the formula. I had plans to make an Dynamic Name range for the List and reference that in my Formula if possible.

any help is greatly appreciated. I'm not restricted to Formula if there is an VBA approach that would be ok.

Book1
	I	J	K	L	M	N	O	P	Q	R	S
1	E#	FIRST NAME	LEAVE	STARTS	ENDS	DECLD		LEAVE LIST		RESULT	Formula
2	133114	KELVIN	MLA	6/15/22	12/6/23	FHL		FMLA		133444	133444
3	133928	CRISTOPHER	MLA	11/15/22	12/31/23	FHL		MLA		133560	133560
4	133444	BRIAN				FHL		OJI		133683	133683
5	133560	KATE				FHL		MILL		133519	133519
6	133135	NATHAN	OJI	6/30/23	12/31/23	FHL		MILR		133632	133632
7	133683	PHILLIP				FHL		MED/SCK		133356	133356
8	133519	LEO	SIDA	6/20/23	6/20/23	FHL		MED		133040	133040
9	133632	JOSHUA	SIDA	6/14/23	6/14/23	FHL		SUSP-PAID		133930	133930
10	133356	DEADERA				FHL		SUSP-UNPAID		133486	133486
11	133240	KIARA	FHL	6/17/23	6/18/23	FTO		NH-TRN		133741	133741
12	133040	JACOB				FHL		TERM
13	133930	LAURENTH	TD	6/19/23	12/31/23	FHL		TRVL
14	133486	DASHEAN				FHL		TEMP
15	133623	CARY	RESIGNED	6/27/23	12/31/23	FHL		EXPIRED BADGE
16	133741	JENNIFER				FHL		TRSFR
17	133083	JORDAN	FNL	6/17/23	6/18/23	FTO		RESIGNED
18	133314	THOMAS	OJI	3/24/23	7/25/23	FTO		NH-TRN-DAL
19	133065	BRADLEY	NH-TRN-DAL	6/19/23	9/7/23	FHL		NH-TRN
20	133527	MATT	NH-TRN-DAL	6/19/23	9/7/23	FHL
21	133750	TIM	NH-TRN	7/10/23	9/27/23	FHL
22	133483	PAUL	NH-TRN	7/10/23	9/27/23	FTO
23	133559	ANTHONY	NH-TRN	7/10/23	9/27/23	FHL
Sheet1

Cell Formulas
Range	Formula
S2:S11	S2	=INDEX($I$2:$I$23,AGGREGATE(15,6,(ROW($I$2:$I$23)-ROW($I$2)+1)/(($N$2:$N$23="FHL")*($K$2:$K$23<>$P$3)*($K$2:$K$23<>$P$4)*($K$2:$K$23<>$P$5)*($K$2:$K$23<>$P$6)*($K$2:$K$23<>$P$7)*($K$2:$K$23<>$P$8)*($K$2:$K$23<>$P$9)*($K$2:$K$23<>$P$10)*($K$2:$K$23<>$P$11)*($K$2:$K$23<>$P$12)*($K$2:$K$23<>$P$13)*($K$2:$K$23<>$P$14)*($K$2:$K$23<>$P$15)*($K$2:$K$23<>$P$16)*($K$2:$K$23<>$P$17)*($K$2:$K$23<>$P$18)*($K$2:$K$23<>$P$19)*($K$2:$K$23<>$P$2)),ROWS($S$2:S2)))

 

[Domenic]

You can still use a formula and dynamic named range to return the desired result. First either convert the range for your leave list into a Table or create a dynamic named range. Then try the following...

Book2
	I	J	K	L	M	N	O	P	Q	R	S
1	E#	FIRST NAME	LEAVE	STARTS	ENDS	DECLD		LEAVE LIST		RESULT	Formula
2	133114	KELVIN	MLA	44727	45266	FHL		FMLA		133444	133444
3	133928	CRISTOPHER	MLA	44880	45291	FHL		MLA		133560	133560
4	133444	BRIAN				FHL		OJI		133683	133683
5	133560	KATE				FHL		MILL		133519	133519
6	133135	NATHAN	OJI	45107	45291	FHL		MILR		133632	133632
7	133683	PHILLIP				FHL		MED/SCK		133356	133356
8	133519	LEO	SIDA	45097	45097	FHL		MED		133040	133040
9	133632	JOSHUA	SIDA	45091	45091	FHL		SUSP-PAID		133930	133930
10	133356	DEADERA				FHL		SUSP-UNPAID		133486	133486
11	133240	KIARA	FHL	45094	45095	FTO		NH-TRN		133741	133741
12	133040	JACOB				FHL		TERM
13	133930	LAURENTH	TD	45096	45291	FHL		TRVL
14	133486	DASHEAN				FHL		TEMP
15	133623	CARY	RESIGNED	45104	45291	FHL		EXPIRED BADGE
16	133741	JENNIFER				FHL		TRSFR
17	133083	JORDAN	FNL	45094	45095	FTO		RESIGNED
18	133314	THOMAS	OJI	45009	45132	FTO		NH-TRN-DAL
19	133065	BRADLEY	NH-TRN-DAL	45096	45176	FHL		NH-TRN
20	133527	MATT	NH-TRN-DAL	45096	45176	FHL
21	133750	TIM	NH-TRN	45117	45196	FHL
22	133483	PAUL	NH-TRN	45117	45196	FTO
23	133559	ANTHONY	NH-TRN	45117	45196	FHL
Sheet1

Cell Formulas
Range	Formula
S2:S11	S2	=INDEX($I$2:$I$23,AGGREGATE(15,6,(ROW($I$2:$I$23)-ROW($I$2)+1)/(($N$2:$N$23="FHL")*(ISNA(MATCH($K$2:$K$23,LeaveList,0)))),ROWS($S$2:S2)))

Named Ranges
Name	Refers To	Cells
LeaveList	=Sheet1!$P$2:INDEX(Sheet1!$P:$P,MATCH("z",Sheet1!$P:$P))	S2:S11

Hope this helps!

 

[hajiali]

Thanks Domenic that works

​