Excel Filter Function - choose certain columns as output

Question

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?

Answer 1

TL;DR

1. Option1:

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

2. Option2: - Reference

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

3. Option3: - Answered by Rory

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

4. Option4: - Commented by P.b

   =FILTER(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),(COLUMN(A:G)=COLUMN(B:B))+(COLUMN(A:G)=COLUMN(D:D)))
   

---

Explanation

Option 1

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.

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})

Pros & Cons - Option1

• This formula is the simplest of all and easy to understand
• You can NOT change the order of output. You can only hide/unhide in the original sequence
• You can apply this on a Range of multiple columns without much change

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)

Pros & Cons - Option2

• This formula looks complex, but is straight-forward once you understand the logic
• You can change the order of output columns as required
• You can apply this on a Range of multiple columns without much change

Options 3

This is actually the answer provided by Rory

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

Pros & Cons - Option3

• This formula is complex, especially for returning a range of continuous columns
• You need to explicitly mention each output column individually
• You can change the order of output columns as required
• Applying this on to output multiple continuous ranges gets tricky (For Example you cannot replace B1:B7 with B1:C7 in above formula)

Options 4

Based on comment from P.b below

=FILTER(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),(COLUMN(A:G)=COLUMN(B:B))+(COLUMN(A:G)=COLUMN(D:D)))

Pros & Cons - Option4

• This formula is the simple and somewhat similar to option 1.
• You can NOT change the order of output. You can only hide/unhide in the original sequence
• You can apply this on a Range of multiple columns without much change

---

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