I'm using FILTER to extract rows from a range, and want to take only certain columns. For example, I filter by D, but want only columns B,C in reverse order. I tried to use QUERY:
=QUERY(filter(B:D,D:D>=2), "select C,B") - Error: can't analyze query string for function QUERY parameter 2: NO_COLUMNC
=QUERY(filter(B:D,D:D>=2), "select *") - shows me all columns, so QUERY should work...
How do I QUERY the results of FILTER? Any other way to achieve this?
When you are QUERYing a computed array, you need to use the Colx notation rather than column letters:
=QUERY(FILTER(B:D,D:D>=2),"select Col2, Col1")
which incidentally can be achieved by just using the QUERY:
=QUERY(B:D,"select C, B where D >= 2")
or just FILTER:
=FILTER({C:C,B:B},D:D>=2)
There is a more straightforward way.
=CHOOSECOLS(FILTER(B:D,D:D>=2), 1,3) Where 1 and 3 are column indexes
Although using the Query function is simple and straight-forward. There's another way to achieve this output using the Filter function
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.
=Filter( FILTER(B2:E6,D2:D6>10900) , {1,0,0,1} ) How about using arrays on the go?
=FILTER({B:B, D:D},D:D>=2)
In this way you select columns in place, and they won't change if new column would be added.
