SORTBY, XMATCH & SEQUENCE practice exercises
5 graded problems with realistic data, all medium.
Three modern array functions: sort one range by the values in another, find a position with more control than MATCH, and generate a run of numbers without typing them in. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the SORTBY, XMATCH & SEQUENCE guide first: syntax, a worked example and the mistakes people make.
In this set
- Rank players by score, highest first
- List products from cheapest to most expensive
- Find a product's position in a list
- Find the last time a status code appears in a log
- Generate a run of week numbers for a planning grid
The exercises
Sort the list of player names by their score, from highest to lowest, without touching the score column itself.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Player | Score | ||
| 2 | Mia | 82 | ? | |
| 3 | Leo | 95 | ||
| 4 | Owen | 71 | ||
| 5 | Zara | 88 | ||
| 6 | Finn | 90 |
Enter a formula in D2.
Show a hint
SORTBY(array, by_array, order) sorts one range using the values in another: =SORTBY(A2:A6,B2:B6,-1), with -1 for descending.
SORTBY is what SORT can't do alone: sorting one column (names) by the values in a completely different column (scores) instead of by its own values.
Sort the list of product names by price, from lowest to highest.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | ||
| 2 | Widget | 24 | ? | |
| 3 | Gadget | 9 | ||
| 4 | Gizmo | 41 | ||
| 5 | Doohickey | 15 |
Enter a formula in D2.
Show a hint
Leave off the 3rd argument for ascending order (the default): =SORTBY(A2:A5,B2:B5).
This is the everyday version of SORTBY: reordering a names-and-prices list by price, which plain SORT can't do since it only sorts the range you point it at.
Find which position "Gizmo" sits at in the product list below.
| A | B | |
|---|---|---|
| 1 | Widget | Looking for |
| 2 | Gadget | Gizmo |
| 3 | Gizmo | ? |
| 4 | Doohickey |
Enter a formula in B3.
Show a hint
XMATCH(lookup_value, lookup_array) returns the position of a match, same idea as MATCH: =XMATCH(B2,A1:A4).
XMATCH is MATCH's modern replacement: same basic job, finding a position, but with cleaner defaults and extra search options like searching from the end.
A status log repeats the code "RETRY" several times. Find the position of its last occurrence, not its first.
| A | B | |
|---|---|---|
| 1 | OK | Looking for |
| 2 | RETRY | RETRY |
| 3 | OK | ? |
| 4 | RETRY | |
| 5 | FAIL | |
| 6 | RETRY |
Enter a formula in B3.
Show a hint
XMATCH's 4th argument, search_mode, set to -1 searches from the last row backward instead of from the first row forward: =XMATCH(B2,A1:A6,0,-1).
Plain MATCH always finds the first match and stops. XMATCH's search_mode is how a formula can deliberately ask for the last one instead, with no helper column or array trick.
Build a list of 6 consecutive week numbers for a planning calendar, starting at week 14.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Week numbers start here | |||
| 2 | ? |
Enter a formula in D2.
Show a hint
SEQUENCE(rows, columns, start, step) generates a run of numbers without typing each one: =SEQUENCE(6,1,14,1) makes 6 numbers starting at 14, counting up by 1.
SEQUENCE replaces the old drag-to-fill trick for building a run of numbers: week numbers, invoice numbers, row labels, directly inside a formula.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the SORTBY, XMATCH & SEQUENCE exercises →