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 |
CodePudding user response：
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)
CodePudding user response：
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.