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".

 

[Excel2021]

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]))))

This worked. Thanks. I just had to edit the Customer Name because the (T/F) was left out.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dreid1011]

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]))))

Oh I see, I did not include the "(T/F)" on current month.

 

[Excel2021]

Glad we could help & thanks for the feedback.

I am just wondering if you knew why leaving out the [#data] fixed this?

 

[Fluff]

There is no need to use [#data] as it simply returns what is in Table1

 

[RajeshKumar1313]

Hi Excel2021
use this one.

Filter_Words is the named range for the words which you want to filter.

=FILTER(Table1[#All],
(LET(a,Table1[[#All],[Customer Name]],b,Filter_Words,BYROW(a,LAMBDA(r,SUM(IF(ISNUMBER(FIND(b,r)),1,0)))))=1)*
(Table1[[#All],[GL Account]]=3000)*(Table1[[#All],[No Date/Past Due/OK]]="Past Due")*(Table1[[#All],[Current Month]]=FALSE)
)

 

[Excel2021]

There is no need to use [#data] as it simply returns what is in Table1

Thanks.

 

[Excel2021]

There is no need to use [#data] as it simply returns what is in Table1

How would I do this for multiple criteria (Using "+" in the filter function)? I would like to apply the same filter for multiple GL Accounts in the same table.

New Formula (See image below)

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

Thanks

 

[Fluff]

How about

=FILTER(Table1,((Table1[GL Account]=3000)+(Table1[GL Account]=4000))*(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]))))

 

[Excel2021]

How about

=FILTER(Table1,((Table1[GL Account]=3000)+(Table1[GL Account]=4000))*(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]))))

Thanks, that works exactly how I wanted it to in the example file.