=TAKE(SORT(FILTER(A2:P43,(B2:B43="Arizona")*(A2:A43=2010)),8,-1),3)
This formula can be broken down into the following parts:
- FILTER(A2:P43,(B2:B43="Arizona")*(A2:A43=2010)): This part of the formula filters the data in the range A2:P43 to only include rows where the value in column B is "Arizona" and the value in column A is 2010.
- SORT(...,8,-1): This part of the formula sorts the filtered data in descending order by the value in the eighth column (column H).
- TAKE(...,3): This part of the formula takes the top three rows of the sorted data and returns them as the result of the formula.
The result of this formula is a range of three rows that contains the top three teams in Arizona for the year 2010, sorted by their ranking in the eighth column (column H).
Here is an example of how this formula can be used:
In this example, the formula is entered in cell C2. The formula filters the data in the range A2:P43 to only include rows where the value in column B is "Arizona" and the value in column A is 2010. The formula then sorts the filtered data in descending order by the value in the eighth column (column H), and finally takes the top three rows of the sorted data and returns them as the result of the formula.
The result of the formula is a range of three rows that contains the top three teams in Arizona for the year 2010, sorted by their ranking in the eighth column (column H).