Excel practice · Dates & Arrays

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

  1. Rank players by score, highest first
  2. List products from cheapest to most expensive
  3. Find a product's position in a list
  4. Find the last time a status code appears in a log
  5. Generate a run of week numbers for a planning grid

The exercises

1. Rank players by score, highest first Medium

Sort the list of player names by their score, from highest to lowest, without touching the score column itself.

ABCD
1PlayerScore
2Mia82?
3Leo95
4Owen71
5Zara88
6Finn90

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.

2. List products from cheapest to most expensive Medium

Sort the list of product names by price, from lowest to highest.

ABCD
1ProductPrice
2Widget24?
3Gadget9
4Gizmo41
5Doohickey15

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.

3. Find a product's position in a list Medium

Find which position "Gizmo" sits at in the product list below.

AB
1WidgetLooking for
2GadgetGizmo
3Gizmo?
4Doohickey

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.

4. Find the last time a status code appears in a log Medium

A status log repeats the code "RETRY" several times. Find the position of its last occurrence, not its first.

AB
1OKLooking for
2RETRYRETRY
3OK?
4RETRY
5FAIL
6RETRY

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.

5. Generate a run of week numbers for a planning grid Medium

Build a list of 6 consecutive week numbers for a planning calendar, starting at week 14.

ABCD
1Week 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 →

More dates & arrays practice

All Excel practice exercises · Excel test prep