# Arrayformula with large function for multiple columns in Google Sheets

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.

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

## 1 Answer

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

• 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