EXCEL: Check for Multiple Values-CodePudding

I'm trying to check for multiple values using the FILTER function. Here is my code:

=FILTER(Projects!$A$2:$P$51,Projects!$E$2:$E$51="Completed")

I would like to check for additional values besides Completed. Like, Canceled, or Review.

Additionally, if I could check for a portion of case-insensitive text like, "rev" that could help too.

CodePudding user response：

Use ISNUMBER(MATCH(...))

=FILTER(Projects!$A$2:$P$51,ISNUMBER(MATCH("*"&Projects!$E$2:$E$51&"*",{"Completed","Canceled","Review"},0)))

CodePudding user response：

FILTER used to return multiple criteria

In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region:

=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")

Also you can have a detail look at the given link

https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759