Search code examples
arraysgoogle-sheetsgoogle-sheets-formulamingoogle-query-language

Query min column header while excluding blanks and handling duplicates


I have the following table.

Name Score A Score B Score C
Bob 8 6
Sue 9 12 9
Joe 11 2
Susan 7 9 10
Tim 10 12 4
Ellie 9 8 7

In my actual table there are about 2k rows.

I am trying to get the min score (excluding blanks & handles duplicate scores) for each person into another column using the QUERY formula or ARRAYFORMULA, really to avoid entering a formula for each row.

As I do currently have this

=INDEX($B$1:$D$1,MATCH(MIN(B2:D2),B2:D2,0))

But that involves dragging down through each cell, as I do this on a few sheets that have circa 2k rows, it's very slow when inputting new data.

This should be the end result

Name Score A Score B Score C Min Score
Bob 8 6 Score C
Sue 9 12 9 Score A
Joe 11 2 Score B
Susan 7 9 10 Score A
Tim 10 12 4 Score C
Ellie 9 8 7 Score C

Solution

  • use:

    =INDEX(SORTN(SORT(SPLIT(QUERY(FLATTEN(
     IF(B2:D="",,B1:D1&"×"&B2:D&"×"&ROW(B2:D))), 
     "where Col1 is not null", ), 
     "×"), 3, 1, 2, 1), 9^9, 2, 3, 1),, 1)
    

    enter image description here