Log In
Register
Home
Active Member
November 5, 2021
Offline
I'm using the Filter function to select some data from a table, but whilst it returns the data just fine, I cannot get it to return the table headers.
I recall seeing some shortcut to do this (although it may have been on a different Dynamic Array function)
Can anyone enlighten me as to the correct syntax?
July 16, 2010
Offline
Hi Peter,
The FILTER function doesn't return headers, as you've discovered. You would need to use a different formula for this e.g.:
=Table1[#Headers]
This will spill the headers where you want them.
Mynda
Trusted Members
Moderators
November 1, 2018
Offline
The FILTER function will return headers (they are just data as far as it's concerned) but only if they are part of the data range you specify and if they match your criteria. Since that last part is rarely true, you tend not to get the headers back. 😉
You can make it true of course by adding something appropriate to the filter criteria but it's usually easier to return them separately as Mynda suggested.
New Member
August 19, 2022
Offline
This seems to work:
=FILTER(Table1[#All],Table1[#Headers]="<column-name>")
To get multiple results
=FILTER(Table1[#All],(Table1[#Headers]="<column-name>")+Table1[#Headers]="<column-name2>")
To get one of the columns from a multiple returned list, e.g. if the 2 above are returned to H1
=INDEX(H1#,,2)
Seems tricky to use the returned values in VLOOKUP/XLOOKUP though (as a spill range)...
but might just be a case of getting the references correct.
December 23, 2020
Offline
There is a fairly easy to use solution to this problem.
Create your worksheet as you have done so far and then add the column headers to your FILTER() output.
Put a number 1 in the cell immediately above every column in your input database to nominate the columns for which you want to see a header and leave the other cells blank. Actually, it doesn't have to be 1, it could be any value but why not keep it simple! Please note, it makes sense to put 1 in the columns that are going to be returned by your original FILTER() function.
Now, put your cursor in the left most cell of the range where you want to headers to appear and enter this: =FILTER(A9:G9, A8:G8) and press enter, where row 9 contains your database headers and row 8 contains your chosen columns.
My screenshot shows how I just did this to answer a question from another forum and it also illustrates one way of suppressing the zeroes that can appear with the FILTER() function. I show the solution with and without zero suppression!
Best wishes
Duncan
July 16, 2010
Offline
Now that we have the VSTACK function we can return headers like so:
=VSTACK({"Header1","Header2","Header3"...},FILTER(....your filter formula))
There are proper examples in the VSTACK function tutorial here.
Mynda
December 23, 2020
Offline
I don't wish to sound patronising in any way but that is something I hadn't thought of, Mynda, using VSTACK() Append the headers to the FILTER() output.
I worked on it to make sure I could do it and I did it and I know it's not part of the OP's question but I'd like to add that I found I still had to wrap around FILTER(...) if I wanted to return just the columns that meet any criteria I might set. In my example, I only need to see the three columns, Name, Maths and Statistics ...
1 Guest(s)
