CODEWITHSUNDEEP
google-sheets
David
David

Reputation: 39

Second Largest Cell Value (ignoring duplicates)

to put it in short words, I need the string "Jordan" as a result (cell G3). Please see my sheet below. 

Row 1:   A     | B     | C      | D      | E     | F      | G      | H     | I
Row 2:   NAME1 | TEXT1 | VALUE1 | NAME2  | TEXT2 | VALUE3 | NAME3  | TEXT3 | VALUE3
Row 3:   Jack  | KCC   | 500    | Marvin | KCD   | 100    | Jordan | KCE   | 500
Row 4:   ...

More precise: I am able to retrieve "JACK" being the name with the largest value, seen from the left. But I need the name string of the second highest value ("JORDAN"), even if the both highest values may be identical and equal 500.

Any help with Google Sheets would be appreciated!!

Best, David

Upvotes: 1

Views: 68

Answers (1)

user11982798
user11982798

Reputation: 1908

This the formula, must be modified if any change of your columns data:

= array_constrain(Sort({A3:C3;D3:F3;G3:I3},3,false, 1, false),1,3)

enter image description here

or as follow (Col1 First, Col2 Second and Col3 Last Largest)

= transpose(query(transpose(Sort({A3:C3;D3:F3;G3:I3},3,false)),"select Col2",0))

enter image description here

Upvotes: 1

Related Questions