Filter function with multiple and|or criteria not working

JesusPM

New Member
Joined
Sep 2, 2024
Messages
3
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hi experts,
I'm having some issues when trying to create complex FILTER function criterias using AND plus OR.
They are working separately, but when I add two OR criterias it returns all of them (it should only select the light blue ones anbd NOT the yellow ones). The final formula will contain more ORs, so I wonder if using an array under search will work, becuase I have not managed to get it working on my side.
Here the function I use in F5, and the table:

Excel Formula:
=TEXTJOIN("#",TRUE,FILTER(B:B,(D:D=G2)*((ISNUMBER(SEARCH("Meraki",C:C))=FALSE)+(ISNUMBER(SEARCH("Voice",C:C))=FALSE)),""))

1725275911073.png
 

Attachments

  • 1725275876613.png
    1725275876613.png
    22.3 KB · Views: 1

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try change + to * :

Excel Formula:
=TEXTJOIN("#",TRUE,FILTER(B:B,(D:D=G2)*((ISNUMBER(SEARCH("Meraki",C:C))=FALSE)*(ISNUMBER(SEARCH("Voice",C:C))=FALSE)),""))
 
Upvote 1
Hi & welcome to MrExcel.
Another option is
Excel Formula:
=TEXTJOIN("#",TRUE,FILTER(B:B,(D:D=G2)*(ISERROR(SEARCH("Meraki",C:C)))*(ISERROR(SEARCH("Voice",C:C))),""))
As you are using not equals it needs to be And rather than Or
 
Upvote 1
Solution
I would avoid using whole column references (unless you have upwards of one million rows of data).
At least with my Excel I can see the delay after entering any of those formulas before the result is displayed.

The final formula will contain more ORs
Depends how many more but assuming that just means more words to exclude a structure like this might appeal a bit more as it would not grow in length as much - just add more values in the {...} array
Excel Formula:
=TEXTJOIN("#",1,BYROW(B2:D10000,LAMBDA(r,IF((COUNT(SEARCH({"Meraki","Voice"},INDEX(r,2)))=0)*(INDEX(r,3)=G2),INDEX(r,1),""))))
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,082
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top