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 has to be associated with a number 1 in column A. Problem is, column A does have some blank cells, which I can not fill in.

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

2 Answers 2

  • 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




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


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.