Search code examples
arrayssortinggoogle-sheetsgoogle-sheets-formulaspreadsheet

Google Spreadsheet MAX + Join of other cells


I have a spreadsheet like this - I can't figure out how to dynamically search for this.

I want to find the MAX Score Value + output the related name (no worries, in another column I've got the Score calculated as clean numbers without letters).

(QUERY doesn't really work because once I change a score, it doesn't update that output)

enter image description here


Solution

  • use:

    =SORTN(SORT({x2:x, y2:y}, 2, 0), 9^9, 2, 1, 1)
    

    where:

    x2:x  - column with names 
    y2:y  - column with values
    2     - sort values
    0     - in descending order
    9^9   - return all rows
    2     - 2nd mode of sortn eg. group by
    1     - names column
    1     - in ascending order