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?
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().