1

In Google Sheets, I have the following filter working which gives me a column range of values:

=FILTER(INPUT!$E$2:$E, INPUT!$A$2:$A=B2)

And a second (temporarily separate for testing) filter working which gives me a column range of values:

=FILTER(PAIRS!$B2:$B, PAIRS!$A$2:$A=B2)

I want the first filter's Condition 1 to reference the output of the second filter within the same equation, as generating a new list for every row seems excessive. However, when I try to combine the two, I'm getting "no matches are found in filter evaluation."

=FILTER(INPUT!$E$2:$E, INPUT!$A$2:$A=(FILTER(PAIRS!$B2:$B, PAIRS!$A$2:$A=B2)))

How do I have the first filter condition = the output of a second filter so that everything within it's output (a column range) is considered (instead of the current working single B2)?

Another way to phrase this would be, how do I have a filter condition = everything within a range?

1
  • 2
    Keep in mind that, while you can see your entire spreadsheet and you know your own goal here, we can't see anything. So, unfortunately, as clear as your post seems to you (who can see everything in front of you), from out here... it's quite unclear what you're trying to do. The only thing that is clear at a glance is that you aren't using these functions correctly. Rather than explaining, share a link to the spreadsheet and hand-enter the result(s) you want a formula to produce. That way, we'll see what you see, and we'll know what you know as a starting point.
    – Erik Tyler
    Apr 5, 2022 at 1:18

1 Answer 1

0

Difficult to show an example without giving away what it is I'm working on. I had to use an or statement in the first filter by using a "+" symbol. A bit messy, but my working result.

=FILTER(INPUT!$E$2:$E,INPUT!$E$2:$E<>0,(INPUT!$A$2:$A=(FILTER(PAIRS!$B$2:$B, PAIRS!$A$2:$A=G1, PAIRS!$B$2:$B<>0)))+(INPUT!$A$2:$A=(FILTER(PAIRS!$C$2:$C, PAIRS!$A$2:$A=G1, PAIRS!$C$2:$C<>0)))+(INPUT!$A$2:$A=(FILTER(PAIRS!$D$2:$D, PAIRS!$A$2:$A=G1, PAIRS!$D$2:$D<>0)))+(INPUT!$A$2:$A=(FILTER(PAIRS!$E$2:$E, PAIRS!$A$2:$A=G1, PAIRS!$E$2:$E<>0))))

https://yagisanatode.com/2020/02/15/google-sheets-how-to-use-or-inside-a-filter/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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