Filter function that must exclude specific data

[Brentsa]

I'm using Filter function. lets say my table is as follows:

Name	Hex	Red	Green	Blue	Hue	Satur.	Light	Satur.	Value
(RGB)	(RGB)	(RGB)	(RGB)	(HSL/HSV)	(HSL)	(HSL)	(HSV)	(HSV)
Absolute Zero	#0048BA	0%	28%	73%	217°	100%	37%	100%	73%
Acid green	#B0BF1A	69%	75%	10%	65°	76%	43%	76%	43%
Aero	#7CB9E8	49%	73%	91%	206°	70%	70%	47%	91%
Aero blue	#C0E8D5	75%	91%	84%	151.5°	47%	83%	17.20%	91%
African violet	#B284BE	70%	52%	75%	288°	31%	63%	31%	75%
Air superiority blue	#72A0C1	45%	63%	76%	205°	39%	60%	41%	76%
Alabaster	#EDEAE0	93%	92%	88%	50°	27%	90%	6%	93%
Alice blue	#F0F8FF	94%	97%	100%	208°	100%	97%	6%	100%
Alloy orange	#C46210	77%	38%	6%	27°	85%	42%	92%	77%
Almond	#EFDECD	94%	87%	80%	30°	52%	87%	14%	94%
Amaranth	#E52B50	90%	17%	31%	348°	78%	53%	81%	90%
Amaranth (M&P)	#9F2B68	62%	17%	41%	328°	57%	40%	73%	62%
Amaranth pink	#F19CBB	95%	61%	73%	338°	75%	78%	35%	95%
Amaranth purple	#AB274F	67%	15%	31%	342°	63%	41%	77%	67%
Amaranth red	#D3212D	83%	13%	18%	356°	73%	48%	84%	83%
Amazon	#3B7A57	23%	48%	34%	147°	35%	36%	52%	48%
Amber	#FFBF00	100%	75%	0%	45°	100%	50%	100%	100%
Amber (SAE/ECE)	#FF7E00	100%	49%	0%	30°	100%	50%	100%	100%
Amethyst	#9966CC	60%	40%	80%	270°	50%	60%	50%	80%
Android green	#A4C639	64%	78%	22%	74°	55%	50%	71%	78%
Antique brass	#CD9575	80%	58%	46%	22°	47%	63%	43%	80%
Antique bronze	#665D1E	40%	36%	12%	53°	55%	26%	71%	40%
Antique fuchsia	#915C83	57%	36%	51%	316°	22%	46%	37%	57%
Antique ruby	#841B2D	52%	11%	18%	350°	66%	31%	80%	52%
Antique white	#FAEBD7	98%	92%	84%	34°	78%	91%	14%	98%
Ao (English)	#008000	0%	50%	0%	120°	100%	25%	100%	50%
Apple green	#8DB600	55%	71%	0%	74°	100%	36%	100%	71%
Apricot	#FBCEB1	98%	81%	69%	24°	90%	84%	29%	98%
Aqua	#00FFFF	0%	100%	100%	180°	100%	50%	100%	100%
Aquamarine	#7FFFD4	50%	100%	83%	160°	100%	75%	50%	100%
Arctic lime	#D0FF14	82%	100%	8%	72°	100%	54%	92%	100%
Army green	#4B5320	29%	33%	13%	69°	44%	23%	61%	33%
Artichoke	#8F9779	56%	59%	47%	76°	13%	53%	20%	59%
Arylide yellow	#E9D66B	91%	84%	42%	51°	74%	67%	54%	91%
Ash gray	#B2BEB5	70%	75%	71%	135°	8%	72%	6%	75%
Asparagus	#87A96B	53%	66%	42%	93°	26%	54%	37%	66%
Atomic tangerine	#FF9966	100%	60%	40%	20°	100%	70%	60%	100%
Auburn	#A52A2A	65%	16%	16%	0°	59%	41%	75%	65%
Aureolin	#FDEE00	99%	93%	0%	56°	100%	50%	100%	99%
Avocado	#568203	34%	51%	1%	81°	95%	26%	98%	51%
Azure	#007FFF	0%	50%	100%	210°	100%	50%	100%	100%
Azure (X11/web color)	#F0FFFF	94%	100%	100%	180°	100%	97%	6%	100%

I want to filter this list but I do not want the following to appear in my data:
Antique bronze
Avocado
Absolute Zero
Amber
Amethyst

How can I still use the filter function?

 

[Peter_SSs]

See if this would suit you.

20 10 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Name	Hex	Red	Green	Blue	Hue	Satur.	Light	Satur.	Value
2	(RGB)	(RGB)	(RGB)	(RGB)	(HSL/HSV)	(HSL)	(HSL)	(HSV)	(HSV)			Exclude
3	Absolute Zero	#0048BA	0%	28%	73%	217°	100%	37%	100%	73%		Antique bronze		Acid green	#B0BF1A	69%	75%	10%	65°	76%	43%	76%	43%
4	Acid green	#B0BF1A	69%	75%	10%	65°	76%	43%	76%	43%		Avocado		Aero	#7CB9E8	49%	73%	91%	206°	70%	70%	47%	91%
5	Aero	#7CB9E8	49%	73%	91%	206°	70%	70%	47%	91%		Absolute Zero		Aero blue	#C0E8D5	75%	91%	84%	151.5°	47%	83%	17%	91%
6	Aero blue	#C0E8D5	75%	91%	84%	151.5°	47%	83%	17.20%	91%		Amber		African violet	#B284BE	70%	52%	75%	288°	31%	63%	31%	75%
7	African violet	#B284BE	70%	52%	75%	288°	31%	63%	31%	75%		Amethyst		Air superiority blue	#72A0C1	45%	63%	76%	205°	39%	60%	41%	76%
8	Air superiority blue	#72A0C1	45%	63%	76%	205°	39%	60%	41%	76%				Alabaster	#EDEAE0	93%	92%	88%	50°	27%	90%	6%	93%
9	Alabaster	#EDEAE0	93%	92%	88%	50°	27%	90%	6%	93%				Alice blue	#F0F8FF	94%	97%	100%	208°	100%	97%	6%	100%
10	Alice blue	#F0F8FF	94%	97%	100%	208°	100%	97%	6%	100%				Alloy orange	#C46210	77%	38%	6%	27°	85%	42%	92%	77%
11	Alloy orange	#C46210	77%	38%	6%	27°	85%	42%	92%	77%				Almond	#EFDECD	94%	87%	80%	30°	52%	87%	14%	94%
12	Almond	#EFDECD	94%	87%	80%	30°	52%	87%	14%	94%				Amaranth	#E52B50	90%	17%	31%	348°	78%	53%	81%	90%
13	Amaranth	#E52B50	90%	17%	31%	348°	78%	53%	81%	90%				Amaranth (M&P)	#9F2B68	62%	17%	41%	328°	57%	40%	73%	62%
14	Amaranth (M&P)	#9F2B68	62%	17%	41%	328°	57%	40%	73%	62%				Amaranth pink	#F19CBB	95%	61%	73%	338°	75%	78%	35%	95%
15	Amaranth pink	#F19CBB	95%	61%	73%	338°	75%	78%	35%	95%				Amaranth purple	#AB274F	67%	15%	31%	342°	63%	41%	77%	67%
16	Amaranth purple	#AB274F	67%	15%	31%	342°	63%	41%	77%	67%				Amaranth red	#D3212D	83%	13%	18%	356°	73%	48%	84%	83%
17	Amaranth red	#D3212D	83%	13%	18%	356°	73%	48%	84%	83%				Amazon	#3B7A57	23%	48%	34%	147°	35%	36%	52%	48%
18	Amazon	#3B7A57	23%	48%	34%	147°	35%	36%	52%	48%				Amber (SAE/ECE)	#FF7E00	100%	49%	0%	30°	100%	50%	100%	100%
19	Amber	#FFBF00	100%	75%	0%	45°	100%	50%	100%	100%				Android green	#A4C639	64%	78%	22%	74°	55%	50%	71%	78%
20	Amber (SAE/ECE)	#FF7E00	100%	49%	0%	30°	100%	50%	100%	100%				Antique brass	#CD9575	80%	58%	46%	22°	47%	63%	43%	80%
21	Amethyst	#9966CC	60%	40%	80%	270°	50%	60%	50%	80%				Antique fuchsia	#915C83	57%	36%	51%	316°	22%	46%	37%	57%
22	Android green	#A4C639	64%	78%	22%	74°	55%	50%	71%	78%				Antique ruby	#841B2D	52%	11%	18%	350°	66%	31%	80%	52%
23	Antique brass	#CD9575	80%	58%	46%	22°	47%	63%	43%	80%				Antique white	#FAEBD7	98%	92%	84%	34°	78%	91%	14%	98%
24	Antique bronze	#665D1E	40%	36%	12%	53°	55%	26%	71%	40%				Ao (English)	#008000	0%	50%	0%	120°	100%	25%	100%	50%
25	Antique fuchsia	#915C83	57%	36%	51%	316°	22%	46%	37%	57%				Apple green	#8DB600	55%	71%	0%	74°	100%	36%	100%	71%
26	Antique ruby	#841B2D	52%	11%	18%	350°	66%	31%	80%	52%				Apricot	#FBCEB1	98%	81%	69%	24°	90%	84%	29%	98%
27	Antique white	#FAEBD7	98%	92%	84%	34°	78%	91%	14%	98%				Aqua	#00FFFF	0%	100%	100%	180°	100%	50%	100%	100%
28	Ao (English)	#008000	0%	50%	0%	120°	100%	25%	100%	50%				Aquamarine	#7FFFD4	50%	100%	83%	160°	100%	75%	50%	100%
29	Apple green	#8DB600	55%	71%	0%	74°	100%	36%	100%	71%				Arctic lime	#D0FF14	82%	100%	8%	72°	100%	54%	92%	100%
30	Apricot	#FBCEB1	98%	81%	69%	24°	90%	84%	29%	98%				Army green	#4B5320	29%	33%	13%	69°	44%	23%	61%	33%
31	Aqua	#00FFFF	0%	100%	100%	180°	100%	50%	100%	100%				Artichoke	#8F9779	56%	59%	47%	76°	13%	53%	20%	59%
32	Aquamarine	#7FFFD4	50%	100%	83%	160°	100%	75%	50%	100%				Arylide yellow	#E9D66B	91%	84%	42%	51°	74%	67%	54%	91%
33	Arctic lime	#D0FF14	82%	100%	8%	72°	100%	54%	92%	100%				Ash gray	#B2BEB5	70%	75%	71%	135°	8%	72%	6%	75%
34	Army green	#4B5320	29%	33%	13%	69°	44%	23%	61%	33%				Asparagus	#87A96B	53%	66%	42%	93°	26%	54%	37%	66%
35	Artichoke	#8F9779	56%	59%	47%	76°	13%	53%	20%	59%				Atomic tangerine	#FF9966	100%	60%	40%	20°	100%	70%	60%	100%
36	Arylide yellow	#E9D66B	91%	84%	42%	51°	74%	67%	54%	91%				Auburn	#A52A2A	65%	16%	16%	0°	59%	41%	75%	65%
37	Ash gray	#B2BEB5	70%	75%	71%	135°	8%	72%	6%	75%				Aureolin	#FDEE00	99%	93%	0%	56°	100%	50%	100%	99%
38	Asparagus	#87A96B	53%	66%	42%	93°	26%	54%	37%	66%				Azure	#007FFF	0%	50%	100%	210°	100%	50%	100%	100%
39	Atomic tangerine	#FF9966	100%	60%	40%	20°	100%	70%	60%	100%				Azure (X11/web color)	#F0FFFF	94%	100%	100%	180°	100%	97%	6%	100%
40	Auburn	#A52A2A	65%	16%	16%	0°	59%	41%	75%	65%
41	Aureolin	#FDEE00	99%	93%	0%	56°	100%	50%	100%	99%
42	Avocado	#568203	34%	51%	1%	81°	95%	26%	98%	51%
43	Azure	#007FFF	0%	50%	100%	210°	100%	50%	100%	100%
44	Azure (X11/web color)	#F0FFFF	94%	100%	100%	180°	100%	97%	6%	100%
Filter

Cell Formulas
Range	Formula
N3:W39	N3	=FILTER(A3:J44,ISNA(MATCH(A3:A44,L3:L7,0)),"")
Dynamic array formulas.

 

[Brentsa]

Thanks,

Is there a way to include that formula in the following:

=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000)))
I have listed the data I want excluded in Teller sheet A4201:A4212

 

[Peter_SSs]

Try

=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$5000,'Teller Statistics (Historic)'!A4201:A4212,0)))

 

[Brentsa]

Try

=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$5000,'Teller Statistics (Historic)'!A4201:A4212,0)))

Unfortunately it did not work the data i want excluded is still in my filter results

 

[Peter_SSs]

Unfortunately it did not work the data i want excluded is still in my filter results

I don't have your whole data, your exclusion list or the contents of cell B4200 or know just what you are trying to do. I may have put something in the wrong place or misinterpreted your formula intent. However, see if this smaller sample is on the right track and, if so, if you can adapt it.

Brentsa.xlsm
	A	B	C	D
1	Absolute Zero	#0048BA	0%	28%
2	Acid green	#B0BF1A	69%	75%
3	Aero	#7CB9E8	49%	73%
4	Aero blue	#C0E8D5	75%	91%
5	African violet	#B284BE	70%	52%
6	Air superiority blue	#72A0C1	45%	63%
7	Alabaster	#EDEAE0	93%	92%
8	Alice blue	#F0F8FF	94%	97%
9	Alloy orange	#C46210	77%	38%
10	Almond	#EFDECD	94%	87%
11
12
13
14
15	Exclude
16	Aero
17	Alabaster
18	Alice blue
19
20
Teller Statistics (Historic)

I want to filter rows 1:10 (columns B:D) for everything that contains an "e" in column A (that is, everything except Almond) but also exclude anything in A16:A20
This seems to me to do that.

Brentsa.xlsm
	B	C	D	E
1
2
3	e	#0048BA	0%	28%
4		#B0BF1A	69%	75%
5		#C0E8D5	75%	91%
6		#B284BE	70%	52%
7		#72A0C1	45%	63%
8		#C46210	77%	38%
9
Filter

Cell Formulas
Range	Formula
C3:E8	C3	=FILTER('Teller Statistics (Historic)'!B$1:D$10,ISNUMBER(SEARCH(B3,'Teller Statistics (Historic)'!A$1:A$10))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$10,'Teller Statistics (Historic)'!A16:A20,0)))
Dynamic array formulas.

 

[Brentsa]

Thanks is seems as if when I tried to retype the formula I missed a bracket or two so that is why my formula was not working. it is working perfectly thanks for your time. it's much appreciated.

 

[Peter_SSs]

You're welcome. Glad you got it working.

Not sure if you are aware, but instead of retyping formulas from the forum you can copy/paste using this icon at the top right of formulas or vba code.

.. or even all values & formulas from one of the XL2BB screen shots: