I'm trying to do a FILTER considering to include a match on several columns (whose number is not predefined at start). Explaining...

Consider the following table:

I want to FILTER when COL1 OR COL2 OR COL3 contain, for instance, "XXXX". The result here would be:

I know I can do the following, and it works:

• =FILTER(A1:A4;(B1:B4="XXXX") (C1:C4="XXXX") (D1:D4="XXXX");"")

The question is that the number of columns COL can grow/shrink and one would need to add many OR conditions on the include which is non practical.

I was expecting to be able to do something like the following:

• =FILTER(A1:A4;B1:D4="XXXX";"")

But this does not work as the FILTER include parameter needs to have size 1 column per 4 rows in order to operate.

I understand that, to do this, I might "just" need to create an array with TRUEs/FALSEs that can then be used in the include of the FILTER function. But my Excel skills do not go so deep :)

Question: how to I do this in one Excel formula ?

CodePudding user response：

I think you were headed in the right direction. B1:D4="XXXX" gets you to a 3 column by 4 row array of TRUE and FALSE values. What you're wanting to do is then perform a "row-wise OR of all the values in each row, one row at a time." So... Let's actually do just that, a row-wise OR of each row:

=BYROW(B1:D4="XXXX",LAMBDA(r,OR(r)))

This will result in a column array like {TRUE;TRUE;FALSE;FALSE} where TRUE means one or more matches and FALSE means no matches. In this case, rows 1 and 2 had matches, row 3 and 4 did not.

BYROW steps through each row of a two-dimensional array, and passes the row to a lambda function. (Note that in the BYROW/BYCOL functions, the LAMBDA is passed the entire row content as an array. This is in contrast to a function like MAKEARRAY where the "r" value passed to the LAMBDA is simply the current row number and not the content.)

Then:

=FILTER(A1:A4,BYROW(B1:D4="XXXX",LAMBDA(r,OR(r))))

Now you can just edit A4 and D4 to be what you need and you're off and running.

If this filter is going to become part of a more complicated formula, consider:

=LET(
matchingRows, BYROW(B1:D4="XXXX",LAMBDA(r,OR(r))),
matchingValues, FILTER( A1:A4, matchingRows ),
// then use "matchingValues" in another formula //
)

CodePudding user response：

I think you're trying to filter out the values if any of the columns contain value "XXXX" in the same row. In that case you could use:

=FILTER(A2:A4,MMULT(--(B2:D4="XXXX"),SEQUENCE(ROWS(B2:D4),,1,0)))

Or using BYROW:

=FILTER(A2:A4,BYROW(B2:D4,LAMBDA(x,COUNTIF(x,"XXXX"))))

Or more dynamically;

=LET(range,A2:D4,
     key,INDEX(range,,1), 
     cols,INDEX(range,SEQUENCE(ROWS(range)),SEQUENCE(1,3,2)),
FILTER(key,MMULT(--(cols="XXXX"),SEQUENCE(ROWS(cols),,1,0))))