asked on

Spreadsheet formula: Dynamic sorting of top 5 value in an array

I might be tired or clueless but I can't figure out why my formula is failing in not finding a value match.

I have 3 columns:
(A) is the position (1 - 5)
(B) lists the name of one of the top 5
Formula:
=ArrayFormula(INDEX(A$26:A$175,MATCH(C2,(Round($D$26:$D$175)+ROW($D$26:$D$175)/10000),0)))
(C) is the top 5 values picked from the data. The top five is calculated with the formula: =TRUNC(ArrayFormula((LARGE((Round($D$26:$D$175,2)+ROW($D$26:$D$175)/10000),A2))),2)

The data is in the lower rows of A21:A and D21:D

I'm using Google Sheets, but it shouldn't greatly differ from an Excel solution. I think "ArrayFormula" might be the only difference.

Here is the test sheet (feel free to edit it):
https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing

Thank you so much for your help.

ASKER CERTIFIED SOLUTION

Norie

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Doug Van

ASKER

Hello Norie,

Thank you so much. Your solution is so much more elegant than mine. :)

Doug Van

ASKER

Thanks again, Norie.

I have a follow-up question if you're available.

I want to also want to automatically provide the current week stats. Using your formula, I can manually do this with:
=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIt 5")
Where (I+J) is update each week. But I am wondering if this can be automatically updated so that each week the query is reading the current week's columns?

If you are able to assist, I have created a new EE question so that you can get proper credit.
https://www.experts-exchange.com/questions/29218219/Calculating-top-5-weekly-stats-automatically.html#questionAdd