Filter with Sort and multiple criteria.

[jd yort]

Hi everyone,

I have a minor hurdle that I have been slamming my hand in the desk drawer over and hoping an Excel Yoda can assist....
I want to Filter the below table and return the records based on the number sold for a region.

I can get it to work without restricting the region (formula in W3), however when I want to factor in the Region I can't quite get it to work (formula in W13).
I thought I would need to do it with an INDEX but maybe that is where I am going wrong and overly complicating it.

Any idea's legends?

Thanks

JD

=INDEX(SORT(FILTER($H$5:$K$18,($K$5:$K$18>=LARGE($K$5:$K$18,$K$2))*($J$5:$J$18=$I$2)),4,-1),,1)

 

[jd yort]

Legend!!!!!That was it. (yep descending was all good in the order)

Thanks again very much appreciated.

 

[djclements]

Alternatively, if you want to use the same LARGE construct as your original formula, try doing it in 2 steps and filter by region first. For example:

=LET(
   a, FILTER(H5:K18,J5:J18=I2),
   v, TAKE(a,,-1),
   SORT(FILTER(a,v>=LARGE(v,MIN(K2,ROWS(v)))),4,-1)
)

Also, use TAKE, INDEX or CHOOSECOLS to return the first column only:

=LET(
   a, FILTER(H5:K18,J5:J18=I2),
   v, TAKE(a,,-1),
   TAKE(SORT(FILTER(a,v>=LARGE(v,MIN(K2,ROWS(v)))),4,-1),,1)
)

Change the [sort_index] and/or [sort_order] as desired. Cheers!

 

[jd yort]

I have never used either LET or TAKE before. Works perfectly too. I will need to pull those formulas apart and understand them.
Added these also to my book of knowledge