SOLVED

# Excel query with "sort" & "Filter"

Occasional Contributor

# Excel query with "sort" & "Filter"

Hi All, I'm hoping I can get some help with the following query.
I have been using the sort and filter function in the attached workbook to work out the top 5 results from a list of names and scores.
Instead of finding the top 5 results I want to be able to show the results for people who achieve a score of 5 or more including any duplicates who score 5.
Have been struggling with this one for a couple of days and would appreciate some help.
Cheers

This is the formula I am using, what do I need to change to be able to see all records who scored 5 or above.

=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, E2)), 2, -1)

5 Replies
best response confirmed by robwill100 (Occasional Contributor)
Solution

# Re: Excel query with "sort" & "Filter"

@robwill100 See attached. Two working formulae, though I feel that there must be better ways to solve this. But I can't thinks of anything else right now.

``=SORTBY(FILTER(Table1,Table1[Score]>=E2),FILTER(FILTER(Table1,Table1[Score]>=E2),{0,1}),-1)``

Or, same as above but wrapped in LET:

``````=LET(
fltr,FILTER(Table1,Table1[Score]>=E2),
return,SORTBY(fltr,FILTER(fltr,{0,1}),-1),
return
)``````

# Re: Excel query with "sort" & "Filter"

Thanks for your help, thats doing exactly what I wanted it to do.
Cheers

# Re: Excel query with "sort" & "Filter"

@robwill100 Great! Glad I could help.

# Re: Excel query with "sort" & "Filter"

@Riny_van_Eekelen I hope I am not stretching my luck in asking another question.

I would like to add a 3rd column to be displayed when i apply the formula you provided to me. The additonal column contains values related to the person in the "name" column and the results in the "Score Column".