enter image description here

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


1 Answer 1


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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.