Query min column header while excluding blanks and handling duplicates

mcclosa 0
Viewed 51

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
min google-query-language google-sheets-formula google-sheets arrays
2 Answers

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

player00

The following answer employs three of the newest set of functions that are still being rolled out by Google so you might not be able to use it right now, but in a few weeks when they're fully rolled out you definitely will (this worked using the Android version of Sheets just now for me):

=arrayformula(if(len(A2:A),byrow(B2:D,lambda(row,xlookup(min(row),row,B1:D1))),))

Assuming the names are in column A, this should give a result for every row which has a name in it. I'm sure there are other ways of doing this, but these 'row/column-wise' problems are really ideal use-cases for LAMBDA and its helper functions like BYROW.

The God of Biscuits0
Related