Index and Match but exclude if on a list

[rex759]

Hello,
I am using Index and match but for multiple entries (Aggregate function) and works great. I want to adjust the formula to exclude numbers (under site column) if it matches a list (List to exclude). I tried working a Countif into the mix but nothing successful.

Any help is appreciated

Index match and exclude.xlsx
	A	B	C	D	E	F	G	H	I
1	Site	Date						List to exclude
2	101	03/03/23		03/03/23	08/03/23	09/10/23		108
3	103	03/03/23		101	102	117		102
4	109	03/03/23		103	106	118		117
5	104	07/07/23		109	108	119		122
6	105	07/14/23			114	120
7	107	07/21/23
8	102	08/03/23
9	106	08/03/23
10	108	08/03/23
11	114	08/03/23	*
12	110	08/19/23
13	111	09/09/23
14	117	09/10/23
15	118	09/10/23
16	119	09/10/23
17	120	09/10/23
18	112	09/15/23
19	113	09/16/23
20	115	09/29/23
21	116	10/07/23
22	121	12/01/23
23	122	12/08/23
24	123	12/09/23
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=IFERROR(INDEX($A$2:$A$24,AGGREGATE(15,6,(ROW($B$2:$B$24)-ROW($B$2)+1)/($B$2:$B$24=$D$2),ROWS($C$2:C2))),"")
E3:E9	E3	=IFERROR(INDEX($A$2:$A$24,AGGREGATE(15,6,(ROW($B$2:$B$24)-ROW($B$2)+1)/($B$2:$B$24=$E$2),ROWS($C$2:D2))),"")
F3:F9	F3	=IFERROR(INDEX($A$2:$A$24,AGGREGATE(15,6,(ROW($B$2:$B$24)-ROW($B$2)+1)/($B$2:$B$24=$F$2),ROWS($C$2:E2))),"")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Site	Date						List to exclude
2	101	03/03/2023		03/03/2023	03/08/2023	10/09/2023		108
3	103	03/03/2023		101	106	118		102
4	109	03/03/2023		103	114	119		117
5	104	07/07/2023		109		120		122
6	105	14/07/2023
7	107	21/07/2023
8	102	03/08/2023
9	106	03/08/2023
10	108	03/08/2023
11	114	03/08/2023
12	110	19/08/2023
13	111	09/09/2023
14	117	10/09/2023
15	118	10/09/2023
16	119	10/09/2023
17	120	10/09/2023
18	112	15/09/2023
19	113	16/09/2023
20	115	29/09/2023
21	116	07/10/2023
22	121	01/12/2023
23	122	08/12/2023
24	123	09/12/2023
25
Data

Cell Formulas
Range	Formula
D3:D5,F3:F5,E3:E4	D3	=FILTER($A$2:$A$100,($B$2:$B$100=D2)*(ISNA(XMATCH($A$2:$A$100,$H$2:$H$10))))
Dynamic array formulas.

 

[rex759]

That was quick! I have never used xmatch! Always learning something new from this site. Thank you very much. Works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.