Dynamic arrays › TAKE, SORT105 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeTAKESORT

TAKE + SORT: top 3 best sellers

Lestari sells bottled sambal on a marketplace. At the start of every month she puts up a "Top 3 Sambal" banner in her shop, and she wants that list to fill itself from last month's sales.

Task

In D2, write one formula that sorts A2:B9 by units sold (the 2nd column) from the highest with SORT, then keeps the top 3 rows with TAKE. The result spills into D2:E4.

Cells to fillD2:E4

Required functionsTAKESORT

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Work from the inside out: sort first with SORT, then keep some of the rows with TAKE.
Hint 2
TAKE(array, rows) keeps a number of rows from the top. A negative number takes them from the bottom.
Hint 3
Pattern: =TAKE(SORT(A2:B9, …, …), 3).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

D2

=TAKE(SORT(A2:B9,2,-1),3)

Explanation

=TAKE(SORT(A2:B9,2,-1),3) sorts every product from the best seller down and keeps the first 3 rows: Sambal ijo, Sambal bawang and Sambal cumi. The partner of TAKE is DROP, which removes rows instead: =DROP(SORT(A2:B9,2,-1),3) shows the products outside the top 3. TAKE(…,-3) keeps the bottom 3 rows, handy for spotting the slowest sellers.

Starting worksheet: Sheet1
Row	A	B	D	E
1	Product	Units sold	Product	Units sold
2	Sambal bawang	420
3	Sambal matah	315
4	Sambal roa	188
5	Sambal ijo	512
6	Sambal teri	276
7	Sambal cumi	349
8	Sambal terasi	233
9	Sambal dabu-dabu	157
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result