1

I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but only for rows that also contain the number 1 in column A. Problem is, column A does have some blank cells, which I can not fill in.

A B
1 1 $1,000
2 $2,000
3 1 $3,000
4 1 $4,000

3 Answers 3

0
  • Use FILTER to capture only the values in Column B that have an associated number in Column A.
  • Use LARGE with the filtered data to get the 1st, 2nd and 3rd biggest numbers

e.g.: This formula in cell E2 =Large(filter($B$2:$B$5,$A$2:$A$5<>""),D2) where cell D2 contains a value of 1


SAMPLE

snapshot

0

I prefer to use a query and get all 3 results directly with:

=ifna(query(A2:B,"select B where A = 1 order by B desc limit 3"))

query

0

SORTN

The SORTN function returns the first n items in a data set after performing a sort.
SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

SORTN Formula with FILTER

SORTN is applied to the array returned by a FILTER function.

=SORTN(FILTER(B:B,A:A=1),3,,1,0)

Nested SORTN Formula

SORTN is applied to an array returned by a nested SORTN function.

=SORTN(SORTN(B:B,1,1,A:A,-1),3,,1,0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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