New =Filter formula

[GB]

I have been using the =Filter formula in Office 365 which is great but I am stuck on whether it is possible to display non contiguous columns. For example, I have a table and need to display column A and C in the output, not column B. Normally I can enter a range like:

=Filter(Table[[A]:[C]],..) but how can I exclude column B in the output by expressing this in a different way?

Any ideas would be appreciated.

Regards
GB

 

[Chihiro]

Assuming table is in Column A to C, starting at A1.

=FILTER(Table[[A]:[C]],COLUMN(Table[[A]:[C]])<>2...)

 

[Peter Bartholomew]

You can either filter down to get a two-column array as in @Chihiro's formula or, using column headers,
= FILTER( Table, (Table[#Headers]="A")+(Table[#Headers]="C") )
or you can construct it with a formula such as
= CHOOSE( {1,2}, Table[A], Table[C] )

 

[GB]

Hi @Peter Bartholomew & @Chihiro much appreciated.
Regards
GB

 

[GB]

Following on from your help, I have a further problem which I have attempted to solve but can't find the correct method.

I am trying to filter a table where I want to find all dates >G1 and return these dates with data from another column that has a table header name = F1. You will some partially successful attempts but not the complete solution. Any help to solve this would be great. See attached
regards GB

 

[Chihiro]

Why try to do it in single formula? This should be done in 2 formula.
And since you are only returning specific columns of table... combine it with INDEX (if you want to use Filter).

1. In F4:=INDEX(FILTER(tbl,tbl[Date]>=G1),,1)
2. In G4:=INDEX(FILTER(tbl,tbl[Date]>=G1),,MATCH(F1,tbl[#Headers],0))

There are many ways to do this. But I can't think of single formula to generate what you are looking for.

 

[Peter Bartholomew]

= FILTER(
     CHOOSE( {1,2},
         tbl[Date],
         XLOOKUP( selectedHdr, tbl[#Headers], tbl ) ),
  tbl[Date] > startDate )

 

[Peter Bartholomew]

XLOOKUP returns the column;
CHOOSE creates a 2-column array
FILTER selects the relevant rows.
Paste the formula anywhere you choose, nothing depends on sheet location.

 

[GB]

Why try to do it in single formula? This should be done in 2 formula.
And since you are only returning specific columns of table... combine it with INDEX (if you want to use Filter).

1. In F4:=INDEX(FILTER(tbl,tbl[Date]>=G1),,1)
2. In G4:=INDEX(FILTER(tbl,tbl[Date]>=G1),,MATCH(F1,tbl[#Headers],0))

There are many ways to do this. But I can't think of single formula to generate what you are looking for.

Thanks for your help Chihiro. I antipated this could be solved in one formula. I can work with your suggestions. Cheers GB