# 'Large' 'If' Formula Help

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

• 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 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"))
`````` # 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)
`````` 