0

I was wondering if anyone had a method for finding the second or third largest numbers in a row across multiple columns using the large function in Google Sheets using an arrayformula to account for a automatically growing list.

1
  • The large() function is an aggregating function and cannot give row-by-row results in an array formula context. Apr 23 at 5:12
0

With your data in the range A2:F, put this in cell G2:

=arrayformula( 
  vlookup( 
    row(A2:F) & "µ" & { 0, 1, 2 }, 
    { 
      row(A2) + int(sequence(rows(A2:F) * columns(A2:F), 1, 0) / columns(A2:F)) 
      & "µ" & 
      mod(sequence(rows(A2:F) * columns(A2:F), 1, 0), columns(A2:F)), 
      sort(split(flatten(row(A2:F) & "µ" & A2:F), "µ"), 1, true, 2, false) 
    }, 
    3, 
    false 
  ) 
)

The formula returns three columns, with the largest value row-by-row in the first column, the second largest in the second, and the third largest in the third. Make space for the results by choosing Insert > Column.

It is a bit on the heavy side, so please delete any unneeded blank rows at the bottom of the sheet before applying.

Hat tip to Ed (Ohio).

4
  • Just saw that in the 3rd row the 3rd 4th and 5th highest numbers were counted and not the 3 highest numbers. I am using 8 columns to search for the highest 3 numbers if that helps Apr 28 at 9:22
  • If your data does not span columns A2:F but instead spans A2:H, replace A2:F in the formula with A2:H. Apr 28 at 9:26
  • That fixed it and it works perfectly. Thank you so much for that May 3 at 2:28
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? May 3 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.