2

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
player0
  • 124,011
  • 12
  • 67
  • 124
mcclosa
  • 943
  • 7
  • 29
  • 59

2 Answers2

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Bravo Player0 ... but I think also an illustration of my point that LAMBDA will reduce the complexity of problems like this when it's generally available. – The God of Biscuits Sep 11 '22 at 21:00
  • `SORTN(SORT` is key, It was such an uncommon situation, however I remember applying this approach, looking forward for what we can do `LAMBDA` . – Osm Sep 11 '22 at 21:43
  • 1
    @TheGodofBiscuits it surely will but without full rollout it's still questionable to show ppl if half of them cant use it yet – player0 Sep 11 '22 at 21:44
0

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 Biscuits
  • 2,029
  • 2
  • 3
  • 10
  • It didn't work for me in Google sheets android, can you share more info? – Osm Sep 11 '22 at 18:46
  • I'm getting a `Unknown function: 'BYROW'` error with that formula – mcclosa Sep 11 '22 at 18:51
  • Looks like you don't have access to some/all of the new functions yet - like I said, they're not fully rolled out yet. More info here: https://workspaceupdates.googleblog.com/2022/08/named-functions-google-sheets.html?m=1 – The God of Biscuits Sep 11 '22 at 18:57