Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets. How to create formula to get 10 highest values from one table and put to another one?

I have a table with 21 teams. Every team has their scores (from 0 to 10 values).

I need to take the 10 highest scores of all teams and put them in front of the scores owners.

Could anybody help to write the formula?

enter image description here

Table: https://docs.google.com/spreadsheets/d/1w4vRedVPt4ArFoWlHeQB_xwk9dHrBYdK1iaqLr70rkY/edit?usp=sharing

I tried to write the formula, but i couln't manage it. too complex for me...

Solution

  • Using Let, Byrow and Bycol to Process a Range.

    One way you can process a matrix of data in Google Sheet is by using Byrow and Bycol. I created a formula for you to use.

    Try this:

    =LET(range, C3:L25, lastnum, INDEX(SORT(TOCOL(range),1,FALSE),10,1),BYROW(range, LAMBDA(row, BYCOL(row, LAMBDA(column, IF(column >= lastnum, column, " "))))))

    Updated Formula in Case you are posting from different Sheet

    =LET(range, 'Team values'!C3:L25, lastnum, INDEX(SORT(TOCOL(range),1,FALSE),10,1),BYROW(range, LAMBDA(row, BYCOL(row, LAMBDA(column, IF(column >= lastnum, column, " "))))))

    Sample Output:

    Sample Output of the Formula

    Reference:

    LET

    BYROW

    BYCOL