Search code examples
google-sheets

How to use OR in FILTER function criteria?

I'm trying to use FILTER to filter (obviously) a single row down to a subset of cells based on the contents of a different row of data. Using two FILTERs, I am able to produce two halves of the data I am seeking, but cannot figure out how to create the complete row outright using a single FILTER. Simply using HSTACK to append the two halves is no good, because I need the output data to remain in the same order as in the original.

The first "half" is:

=filter($C5:$J5,filter(INDIRECT("Quals!$G$6:$N$100"),INDIRECT("Quals!$B6:$B100")=L$3)="Y")

which takes Table 1 (Quals!G6:N100), reduces it to Row 1 (such as Quals!G9:N9), then Row 2 (C5:J5) is filtered using Row 1 with the criterion of "the corresponding cell of Row 1 must contain "Y"".

The second "half" is:

=filter($C5:$J5,filter(INDIRECT("Quals!$G$6:$N$100"),INDIRECT("Quals!$B6:$B100")=L$3)="M")

which takes Table 1 (Quals!G6:N100), reduces it to Row 1 (such as Quals!G9:N9), then Row 2 (C5:J5) is filtered using Row 1 with the criterion of "the corresponding cell of Row 1 must contain "M"".

I tried combining them into

=filter($C5:$J5,OR(filter(INDIRECT("Quals!$G$6:$N$100"),INDIRECT("Quals!$B6:$B100")=L$3)="Y",filter(INDIRECT("Quals!$G$6:$N$100"),INDIRECT("Quals!$B6:$B100")=L$3)="M"))

where I tried to make use of OR such that Row 2 data would be kept if the corresponding Row 1 cell contains either "Y" or "M", but that function returns either nothing or Row 2 unaltered.

Any suggestions on how to get FILTER to work using OR to make "contains either" criteria?

Solution

  • The or() function is an aggregating function and thus won't get row-by-row results in an array formula context.

    Use Boolean arithmetic, like this:

    =filter( 
  $C5:$J5, 
  filter(Quals!$G$6:$N$100,Quals!$B6:$B100=L$3)="Y") 
  + 
  filter(Quals!$G$6:$N$100,Quals!$B6:$B100=L$3)="M")
)

    Alternatively, use regexmatch(), like this:

    =filter( 
  $C5:$J5, 
  regexmatch(
    filter(Quals!$G$6:$N$100,Quals!$B6:$B100=L$3), 
    "^(Y|M)$"
  ) 
)

    See regexmatch().