Filter out words using the Filter Function with Table References

[Excel2021]

Hello,

I am using the filter function to pull data from a table in excel using table references. I need to filter out any row containing 2 specific words in a specific column. I am having difficulty doing this with table references as all the examples online show this done with cell ranges not table references/ranges. I have attached an image with my filtered results/formula in Cell I4 as I am unable to add the workbook. I would like to filter out any row in the "Customer Name" column with the words "Lemon" or "Pear".

 

[Fluff]

Can you post your existing formula.
Also is this in addition to your existing criteria.

 

[dreid1011]

Give this a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	Customer Name	No Date/Past Due/OK	Current Month	GL Account	Amount				Customer Name	No Date/Past Due/OK	Current Month	GL Account	Amount
5	Apple Co	OK	TRUE	2000	35000				Apple Co	Past Due	FALSE	3000	35000
6	Orange Co	OK	TRUE	2000	100				Orange Co	Past Due	FALSE	3000	100
7	Lemon Co	OK	TRUE	2000	500				Apple LLC	Past Due	FALSE	3000	1500
8	Pear Co	OK	TRUE	2000	7000				Orange LLC	Past Due	FALSE	3000	2000
9	Apple LLC	OK	TRUE	2000	1500
10	Orange LLC	OK	TRUE	2000	2000
11	Lemon LLC	OK	TRUE	2000	2500
12	Pear LLC	OK	TRUE	2000	3000
13	Apple Co	Past Due	FALSE	3000	35000
14	Orange Co	Past Due	FALSE	3000	100
15	Lemon Co	Past Due	FALSE	3000	500
16	Pear Co	Past Due	FALSE	3000	1000
17	Apple LLC	Past Due	FALSE	3000	1500
18	Orange LLC	Past Due	FALSE	3000	2000
19	Lemon LLC	Past Due	FALSE	3000	2500
20	Pear LLC	Past Due	FALSE	3000	5000
Sheet1

Cell Formulas
Range	Formula
I5:M8	I5	=FILTER(Table1[#All],(Table1[[#All],[GL Account]]=3000)*(Table1[[#All],[No Date/Past Due/OK]]="Past Due")*(Table1[[#All],[Current Month]]=FALSE)*(ISNUMBER(SEARCH("Lemon",Table1[[#All],[Customer Name]]))=FALSE)*(ISNUMBER(SEARCH("Pear",Table1[[#All],[Customer Name]]))=FALSE))
Dynamic array formulas.

 

[RoryA]

I also suggest you update your profile as you clearly can't be using 2019.

 

[Fluff]

Another option is

=FILTER(Table1,(Table1[GL Account]=3000)*(Table1[No Date/Past Due/OK]="Past Due")*(Table1[Current Month]=FALSE)*(ISERR(SEARCH("Lemon",Table1[Customer Name])))*(ISERR(SEARCH("Pear",Table1[Customer Name]))))

 

[Excel2021]

Can you post your existing formula.
Also is this in addition to your existing criteria.

=FILTER(Table1[#Data],(Table1[[#Data],[GL Account]]=3000)*(Table1[[#Data],[No Date/Past Due/OK]]="Past Due")*(Table1[[#Data],[Current Month (T/F)]]=FALSE),"")

Yes, I need the existing criteria as well please.

 

[Excel2021]

Give this a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	Customer Name	No Date/Past Due/OK	Current Month	GL Account	Amount				Customer Name	No Date/Past Due/OK	Current Month	GL Account	Amount
5	Apple Co	OK	TRUE	2000	35000				Apple Co	Past Due	FALSE	3000	35000
6	Orange Co	OK	TRUE	2000	100				Orange Co	Past Due	FALSE	3000	100
7	Lemon Co	OK	TRUE	2000	500				Apple LLC	Past Due	FALSE	3000	1500
8	Pear Co	OK	TRUE	2000	7000				Orange LLC	Past Due	FALSE	3000	2000
9	Apple LLC	OK	TRUE	2000	1500
10	Orange LLC	OK	TRUE	2000	2000
11	Lemon LLC	OK	TRUE	2000	2500
12	Pear LLC	OK	TRUE	2000	3000
13	Apple Co	Past Due	FALSE	3000	35000
14	Orange Co	Past Due	FALSE	3000	100
15	Lemon Co	Past Due	FALSE	3000	500
16	Pear Co	Past Due	FALSE	3000	1000
17	Apple LLC	Past Due	FALSE	3000	1500
18	Orange LLC	Past Due	FALSE	3000	2000
19	Lemon LLC	Past Due	FALSE	3000	2500
20	Pear LLC	Past Due	FALSE	3000	5000
Sheet1

Cell Formulas
Range	Formula
I5:M8	I5	=FILTER(Table1[#All],(Table1[[#All],[GL Account]]=3000)*(Table1[[#All],[No Date/Past Due/OK]]="Past Due")*(Table1[[#All],[Current Month]]=FALSE)*(ISNUMBER(SEARCH("Lemon",Table1[[#All],[Customer Name]]))=FALSE)*(ISNUMBER(SEARCH("Pear",Table1[[#All],[Customer Name]]))=FALSE))
Dynamic array formulas.

I didn't have any luck with this. Did you mean to change the table from Data to All? All would include the headers I believe.

 

[Excel2021]

I also suggest you update your profile as you clearly can't be using 2019.

Thanks, I changed to 365

 

[dreid1011]

I didn't have any luck with this. Did you mean to change the table from Data to All? All would include the headers I believe.

I did not mean to change anything. I don't usually use tables, so that is what Excel populated when I selected the columns.

Try this then, or Fluff's suggestion.

=FILTER(Table1,(Table1[GL Account]=3000)*(Table1[No Date/Past Due/OK]="Past Due")*(Table1[Current Month]=FALSE)*(ISNUMBER(SEARCH("Lemon",Table1[Customer Name]))=FALSE)*(ISNUMBER(SEARCH("Pear",Table1[Customer Name]))=FALSE))

 

[Fluff]

You're headers are slightly different, try

=FILTER(Table1,(Table1[GL Account]=3000)*(Table1[No Date/Past Due/OK]="Past Due")*(Table1[Current Month (T/F)]=FALSE)*(ISERR(SEARCH("Lemon",Table1[Customer Name])))*(ISERR(SEARCH("Pear",Table1[Customer Name]))))