SOLVED

FILTER and LARGE function.

Frequent Contributor

Hi,

I try to extract 10 largest values from a Pivot Table.

I write a formula: 

=INDEX(SORT(FILTER(A:I,(A:A=K2)*(G:G>=LARGE(G:G,10))),7,-1),SEQUENCE(10),{3,7})

 

The result shows  just 3 largest values:

small_village_0-1666423557218.png

Hope for your help.

Thank you.

 

5 Replies

@small_village 

Change > into <.

 

But you can also use the Top10 filter of the pivot table.

 

best response confirmed by small_village (Frequent Contributor)
Solution

@small_village Use TAKE() function.

=TAKE(CHOOSECOLS(SORT(FILTER(A:I,A:A=K2),7,-1),3,7),10)

Harun24HR_0-1666428481738.png

 

 

@Harun24HR 

Thank you for your help.

My office version: 2021, it has no TAKE and CHOOSECOLS functions.

I don't know exactly why FILTER and LARGE working incorectly with many dates. If the Pivot Table filter just only date 10, that functions will return the expected result.

 

Then just use =INDEX(SORT(FILTER(A:I,(A:A=$K$2)),7,-1),SEQUENCE(10),{3,7}). Omit LARGE() function because SEQUENCE(10) will only show 10 rows.

@Harun24HR 

I actually got it.
Thank you again for help.

Regards,

Tuan.