SORTBY / XMATCH / SEQUENCE
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.
SORTBY's key difference from a plain SORT is that the column being sorted by doesn't have to be the column being returned: it can sort a list of names by a separate score column sitting right next to it. XMATCH is MATCH's modern replacement, with the same basic job of finding a position, but with a search_mode argument that can search backward through a list, something plain MATCH has no way to do at all. SEQUENCE generates a list of numbers on the fly, a week's worth of day numbers, a running row index, instead of needing them typed in or dragged down by hand.
Syntax
| Argument | Description |
|---|---|
| array, by_array | SORTBY: Required. The values to return, and the (often different) values to sort them by. |
| sort_order | SORTBY: Optional. 1 (default) for ascending, -1 for descending. |
| lookup_value, lookup_array | XMATCH: Required. What to find, and where to look for it. |
| search_mode | XMATCH: Optional. 1 (default) searches first-to-last; -1 searches last-to-first, useful for finding the most recent match. |
| rows, columns, start, step | SEQUENCE: rows required; columns/start/step optional (default 1 column, starting at 1, counting up by 1). |
A real example: ranking names by a score without touching the score column
A short leaderboard where names should sort by score, descending, while the score column itself stays untouched:
SORTBY returns the names, reordered by whatever order the matching score cells fall into; a plain SORT can't do this, since it only ever sorts the exact range it's given, never a separate one.
Worked examples
| A | B | |
|---|---|---|
| 1 | Ren | 72 |
| 2 | Mika | 95 |
| 3 | Owen | 60 |
| A | |
|---|---|
| 1 | Status |
| 2 | Open |
| 3 | Closed |
| 4 | Open |
Common mistakes
- Assuming SORTBY sorts the same column it returns. It can sort by any same-sized array, which is exactly its advantage over plain SORT.
- Leaving search_mode at its default when the goal is specifically "find the last match," and getting the first occurrence instead: XMATCH only searches backward when told to.
- Typing out a list of sequential numbers by hand when SEQUENCE would generate the exact same list from four small arguments, with no risk of a typo partway through.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice SORTBY / XMATCH / SEQUENCE →