Notification texts go here Contact Us Buy Now!

Could you please sort out this Excel filter formula?

Approach 1: Utilizing SORT() and TAKE() Functions

=TAKE(SORT(FILTER(A2:P43,(B2:B43="Arizona")*(A2:A43=2010)),8,-1),3)
  • FILTER(): This function extracts data from the specified range (A2:P43) based on two conditions:
    • (B2:B43="Arizona"): Rows with "Arizona" in column B.
    • (A2:A43=2010): Rows with the year 2010 in column A.
  • SORT(): This function arranges the filtered data in ascending order based on the eighth column (column H, which contains the Wins data).
  • TAKE(): This function extracts the top three rows from the sorted data, effectively providing the top three teams with the most wins.

Approach 2: Leveraging Structured References (Tables)

=TAKE(SORT(FILTER(_master,(_master[Team]="Arizona")*(_master[Year]=2010)),8,-1),3)
  • This approach utilizes structured references, also known as tables, to simplify the formula.
  • The _master reference represents the table containing the data. In this case, it's assumed that the data is organized in a table format.
  • The formula follows the same logic as Approach 1, using FILTER(), SORT(), and TAKE() to extract the top three teams.

Post a Comment

Cookie Consent
We serve cookies on this site to analyze traffic, remember your preferences, and optimize your experience.
Oops!
It seems there is something wrong with your internet connection. Please connect to the internet and start browsing again.
AdBlock Detected!
We have detected that you are using adblocking plugin in your browser.
The revenue we earn by the advertisements is used to manage this website, we request you to whitelist our website in your adblocking plugin.
Site is Blocked
Sorry! This site is not available in your country.