I would like to apply a Filter function on multiple columns ranging from A:G and only have columns B,D in the output. How can I do it?

For Example =FILTER($A$1:$G$7,$K$1:$K$7=$K$1) results in a spilled array of rows that match the condition, but the output will still have 7 columns(A:G). Can I choose to only output Column B & D?

CodePudding user response：

You could also use CHOOSE like this:

=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)

This also allows you to reorder columns in the output by changing their order in the CHOOSE function.

CodePudding user response：

You can nest the original FILTER function inside another FILTER function and specify an array of 1's and 0's mentioning which column you need and which you don't.

Option 1

For Example, in the above question if I want only Column B & D, I can do this:

=FILTER(FILTER(A1:G7,K1:K7=K1),{0,1,0,1,0,0,0})

Since B & D are the 2nd & 4th columns, you need to specify a 1 at that position in the array

Similarly if you want to filter columns from C:K and only output columns C, D & G, then your formula would be:

=FILTER(FILTER(C1:K7,M1:M7=M1),{1,1,0,0,1,0,0,0,0})

Option2

Another way to do this which is complex looking is this:
Note that this method allows you to change the order of output columns. You can refer to following site for detailed explanation on how this works.

=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

There's a similar question that's asked in reference to Google Sheet. But Google Sheet also has the Query function which explicitly supports choosing specific columns