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 |
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)