I have some data in a table that I would like to filter and sort the top 10 from each column but not show all columns.
Sample data:
My current formula is as follows:
=SORT(FILTER(A3:I437,C3:C437>=LARGE(C3:C437,10)),3,-1)
This outputs the following:
Ideally what I would like is to output just columns R and T from this formula but am not sure how to go about this? I will also be running the same formula to output column R with each of the other columns separately (e.g. R&S, R&U, R&V etc).
Any advice on this would be greatly appreciated!
CodePudding user response：
Use the new CHOOSECOLS function if you have it:
=CHOOSECOLS(filtered_array,{1,3})
If not then you can use INDEX(filtered_array, 0, 1) to choose the entire first column, INDEX(filtered_array, 0, 3) to choose the 3rd
Alternatively to get a formula in a single cell, you can also use a second FILTER, filtering the COLS() of the array to match {1,3}, although this is a bit more involved.
N.B. Here filtered_array is your current formula
CodePudding user response：
You can also CHOOSE inside the FILTER
edit: like this