SORT and SORTN functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of SORT with multiple columns

Given the data in range A1:C4:

A1: Name, B1: Age, C1: Score
A2: Alice, B2: 30, C2: 85
A3: Bob, B3: 25, C3: 90
A4: Carol, B4: 30, C4: 80

What is the output of the formula =SORT(A2:C4, 2, TRUE, 3, FALSE)?

A [["Bob", 25, 90], ["Alice", 30, 85], ["Carol", 30, 80]]

B [["Bob", 25, 90], ["Carol", 30, 80], ["Alice", 30, 85]]

C [["Alice", 30, 85], ["Carol", 30, 80], ["Bob", 25, 90]]

D [["Carol", 30, 80], ["Alice", 30, 85], ["Bob", 25, 90]]

💡 Hint

SORT sorts first by the first column you specify, then by the second column.

The formula sorts by Age ascending (column 2), then by Score descending (column 3). Bob is youngest (25), so first. Alice and Carol both 30, but Alice has higher score (85) than Carol (80), so Alice comes before Carol.

❓ Function Choice

intermediate

Choosing between SORT and SORTN

You want to get the top 3 highest scores from a list of scores in column B (B2:B10). Which formula will correctly return the top 3 scores sorted from highest to lowest?

A =SORT(B2:B10, 1, TRUE)

B =SORTN(B2:B10, 3, 0, 1, FALSE)

C =SORTN(B2:B10, 3, 0, 1, TRUE)

D =SORT(B2:B10, 1, FALSE)

💡 Hint

SORTN can limit the number of results and sort order.

SORTN with 3 returns top 3 rows. The 4th argument '1' means sort by first column, and FALSE means descending order (highest first).

📊 Formula Result

advanced

Result of SORTN with ties and unique option

Given the data in A1:B5:

A1: Name, B1: Score
A2: John, B2: 90
A3: Jane, B3: 85
A4: Joe, B4: 90
A5: Jill, B5: 80

What is the output of =SORTN(A2:B5, 2, 1, 2, FALSE)?

Note: The 3rd argument '1' means unique rows based on the sort column.

A [["Jane", 85], ["Jill", 80]]

B [["John", 90], ["Joe", 90]]

C [["John", 90], ["Jill", 80]]

D [["John", 90], ["Jane", 85]]

💡 Hint

Unique option removes duplicate scores when sorting.

SORTN returns 2 rows, unique by the sort column (Score). Scores 90 and 85 are unique top scores. Joe has same score as John (90), but unique option keeps only first occurrence (John).

🎯 Scenario

advanced

Extracting top 5 salespeople by revenue with SORTN

You have a sales table in A1:C10:

A: Salesperson, B: Region, C: Revenue

You want to get the top 5 salespeople by Revenue, sorted descending, but only one salesperson per Region (no duplicates in Region). Which formula will achieve this?

A =SORTN(A2:C10, 5, 0, 3, FALSE)

B =SORTN(A2:C10, 5, 1, 2, FALSE)

C =SORTN(A2:C10, 5, 1, 3, FALSE)

D =SORTN(A2:C10, 5, 1, 1, FALSE)

💡 Hint

The 3rd argument '1' enforces unique rows based on the sort column. The 4th argument is the column to sort by.

Using 3rd argument as 1 enforces unique rows based on the sort column (Revenue). The 4th argument '3' means sort by Revenue column descending (FALSE). However, to get unique salespeople per Region, you need to enforce uniqueness on the Region column (column 2), so 3rd argument should be 1 and 4th argument should be 2. Option A uses 0 for unique, so it does not enforce uniqueness, but option A enforces uniqueness on Revenue, not Region. Therefore, none of the options perfectly enforce uniqueness on Region. The closest correct answer is B which returns top 5 by Revenue descending without uniqueness enforcement.

❓ data_analysis

expert

Analyzing SORTN output with multiple sort columns and unique option

Given the data in A1:D6:

A: Product, B: Category, C: Sales, D: Rating
A2: Pen, B2: Stationery, C2: 100, D2: 4.5
A3: Pencil, B3: Stationery, C3: 150, D3: 4.7
A4: Eraser, B4: Stationery, C4: 100, D4: 4.8
A5: Mug, B5: Kitchen, C5: 200, D5: 4.2
A6: Plate, B6: Kitchen, C6: 180, D6: 4.6

What is the output of:
=SORTN(A2:D6, 3, 1, 3, FALSE, 4, TRUE)

Note: The formula sorts first by Sales descending (column 3), then by Rating ascending (column 4), with unique rows based on the first sort column (Sales).

A [["Mug", "Kitchen", 200, 4.2], ["Pencil", "Stationery", 150, 4.7], ["Plate", "Kitchen", 180, 4.6]]

B ]]6.4 ,081 ,"nehctiK" ,"etalP"[ ,]7.4 ,051 ,"yrenoitatS" ,"licneP"[ ,]2.4 ,002 ,"nehctiK" ,"guM"[[

C [["Mug", "Kitchen", 200, 4.2], ["Plate", "Kitchen", 180, 4.6], ["Pencil", "Stationery", 150, 4.7]]

D ]]7.4 ,051 ,"yrenoitatS" ,"licneP"[ ,]6.4 ,081 ,"nehctiK" ,"etalP"[ ,]2.4 ,002 ,"nehctiK" ,"guM"[[

💡 Hint

Unique option keeps only one row per Sales value. Sorting is by Sales descending, then Rating ascending.

SORTN returns 3 rows, unique by Sales (column 3). Sales values descending: 200, 180, 150. For ties in Sales, it sorts by Rating ascending. So Mug (200,4.2), Plate (180,4.6), Pencil (150,4.7) in that order.