Filter function with multiple variables google sheets

Question

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T DATE that meets the same specified range.

This is my ignorant attempt at trying to do this but it, of course, creates an Error.

=FILTER(A2:D6,C2:C6 <B10+D2:D6,"Y",B2:B6<B10)

Apologies if this is too easy to be asking; I've be reading for over 3 hours and haven't found how to do this or if this can be done.

Thank you

Answer 1

Filters values in A2:A6 if either condition is true:

=FILTER(A2:A6,
   (B2:B6<B10)+ 
   ((C2:C6<B10)*(D2:D6="Y")))

In the FILTER function conditions:

• + is equivalent to OR
• * is equivalent to AND

Answer 2

Instead of FILTER you can use the QUERY function as in this formula:

=QUERY(A2:D6,"SELECT A WHERE D='Y' AND C <= date '"&TEXT(B10,"yyyy-MM-dd")&"'")

By referencing the cell B10, you can change the date in it to any date you like and it will be picked in the formula.

Functions used:

• QUERY
• TEXT