Excel 2021 or laterDynamic arrays

SORTBY

SORTEREN.OP in Dutch Excel

Sorts a range by the values of one or more other ranges or arrays, each with its own direction, and spills the sorted copy.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDE
1Invoice no.CustomerDue dateOldest first
2F-25-0311Jansen Metaal BV14 Nov 2025F-25-0254=SORTBY(A2:A7, C2:C7)=SORTBY(A2:A7; C2:C7)
3F-25-0287Smit Logistiek BV30 Sep 2025F-25-0287
4F-25-0342De Boer Techniek BV19 Dec 2025F-25-0301
5F-25-0254Kuiper Bouw BV22 Aug 2025F-25-0311
6F-25-0301Hendriks Groep BV28 Oct 2025F-25-0329
7F-25-0329Mulder Transport BV5 Dec 2025F-25-0342
=SORTBY(A2:A7, C2:C7)
=SORTBY(A2:A7; C2:C7)

What SORTBY does and when to use it

What it does: it returns a sorted copy of a list, ordered by a column or calculation you supply separately, for example open invoices with the oldest due date first, without showing that date.

When to use it: when the sort key should stay out of the result, when you need a second key to break ties, or when the order comes from a calculation. For one visible column, SORT is shorter.

Three main gains:

  • Keys out of sight: sort on a column you do not return.
  • Several keys: add a second and third key, each ascending or descending, to break ties.
  • Calculated keys: order by a length, a surname or a position in a custom list.

How Excel evaluates it

Excel works in three steps:

  • it evaluates every by_array and checks its size against array
  • it orders the positions by by_array1 in the direction of sort_order1; a tie goes to by_array2, and so on, and positions that tie on every key keep their source order
  • it returns the rows of array in that order, as a spilled block of the same size as array

Watch out: Each key must be one column (or row) with exactly as many cells as array has rows (or columns); otherwise SORTBY returns #VALUE!.

Mental model

A deck of cards with a sticky note on each one: you sort by the notes, peel them off, and hand over only the cards.

Terms used on this page: spill, array

Syntax

Official SORTBY documentation
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)
SORTBY(array; by_array1; [sort_order1]; [by_array2; sort_order2]; ...)
ArgumentTypeMeaningDefault
arrayRequiredWhat you want back, sorted; the keys do not have to be part of it.
by_array1RequiredThe values that decide the order, one per row of array.
sort_order1Optional1 ascending (default), -1 descending, for this key only.1

Return value:The rows of array, ordered by by_array1 and any further keys

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Oldest open invoices first

You received the receivables list of Veldhuis Installatie BV in the order the invoices were booked: invoice number, customer and due date. For your aging analysis you want only the invoice numbers, the oldest due date first, so you can start with the invoices that are most overdue. The dates stay on the specification.

ABCDEF
1Invoice no.CustomerDue dateOldest first
2F-25-0311Jansen Metaal BV14 Nov 2025F-25-0254=SORTBY(A2:A7, C2:C7)=SORTBY(A2:A7; C2:C7)
3F-25-0287Smit Logistiek BV30 Sep 2025F-25-0287
4F-25-0342De Boer Techniek BV19 Dec 2025F-25-0301
5F-25-0254Kuiper Bouw BV22 Aug 2025F-25-0311
6F-25-0301Hendriks Groep BV28 Oct 2025F-25-0329
7F-25-0329Mulder Transport BV5 Dec 2025F-25-0342
8
Formula cell (E2)InputSpilled result

Formula in E2

=SORTBY(A2:A7, C2:C7)
=SORTBY(A2:A7; C2:C7)

Result: F-25-0254 / F-25-0287 / F-25-0301 / F-25-0311 / F-25-0329 / F-25-0342F-25-0254 / F-25-0287 / F-25-0301 / F-25-0311 / F-25-0329 / F-25-0342

Step by step
  1. Excel reads array, A2:A7: the six invoice numbers. It is one column wide, so only invoice numbers will come back.
  2. It reads by_array1, C2:C7: the due dates 14-11-2025, 30-09-2025, 19-12-2025, 22-08-2025, 28-10-2025 and 05-12-2025. Six dates for six invoices, so the sizes match.
  3. sort_order1 is left out, so the dates are ordered ascending, earliest first: 22-08-2025, 30-09-2025, 28-10-2025, 14-11-2025, 05-12-2025, 19-12-2025.
  4. Each date drags its invoice along: 22-08-2025 is the fourth position, F-25-0254; 30-09-2025 is the second, F-25-0287; then F-25-0301, F-25-0311, F-25-0329 and F-25-0342.
  5. The six invoice numbers spill from E2 to E7, without dates. Correct a due date, say C2 from 14-11-2025 to 01-08-2025, and F-25-0311 moves to the top.

Watch out: If a due date arrives from the client's export as text, for example 14-11-2025 in a cell Excel did not convert, it is sorted after all real dates, and that invoice silently ends up last instead of in November.

More on this example: why it works, 1 variation

Why it works

array and by_array1 are linked only by position: the fourth date belongs to the fourth invoice. That is why the output can contain the invoice numbers alone while the order still comes from the due dates.

Variations

=SORT(A2:C7, 3)
=SORT(A2:C7; 3)

With SORT you sort on the third column, but the customers and due dates come back too, because the key has to be inside the array.

Intermediate

Journal lines by journal, then by amount

For journal entry testing you have six lines from the XAF auditfile: entry number, journal and amount. You want them grouped by journal (BNK, INK, MEM, VRK) and, within a journal, the largest amount first, so the three manual MEM entries show their biggest posting at the top of the group.

ABCDEFGH
1Entry no.JournalAmountTest order
225-0412VRK€12,450.00€ 12.450,0025-0291=SORTBY(A2:C7, B2:B7, 1, C2:C7, -1)=SORTBY(A2:C7; B2:B7; 1; C2:C7; -1)BNK€3,200.50€ 3.200,50
325-0388MEM€48,000.00€ 48.000,0025-0377INK€8,120.00€ 8.120,00
425-0291BNK€3,200.50€ 3.200,5025-0388MEM€48,000.00€ 48.000,00
525-0415MEM€5,000.00€ 5.000,0025-0420MEM€15,250.75€ 15.250,75
625-0377INK€8,120.00€ 8.120,0025-0415MEM€5,000.00€ 5.000,00
725-0420MEM€15,250.75€ 15.250,7525-0412VRK€12,450.00€ 12.450,00
8
Formula cell (E2)InputSpilled result

Formula in E2

=SORTBY(A2:C7, B2:B7, 1, C2:C7, -1)
=SORTBY(A2:C7; B2:B7; 1; C2:C7; -1)

Result: 25-0291 | BNK | €3,200.50 / 25-0377 | INK | €8,120.00 / 25-0388 | MEM | €48,000.00 / 25-0420 | MEM | €15,250.75 / 25-0415 | MEM | €5,000.00 / 25-0412 | VRK | €12,450.0025-0291 | BNK | € 3.200,50 / 25-0377 | INK | € 8.120,00 / 25-0388 | MEM | € 48.000,00 / 25-0420 | MEM | € 15.250,75 / 25-0415 | MEM | € 5.000,00 / 25-0412 | VRK | € 12.450,00

Step by step
  1. The first pair, B2:B7 with 1, orders by journal ascending, alphabetically: BNK (row 4), INK (row 6), the three MEM lines (rows 3, 5 and 7), then VRK (row 2).
  2. BNK, INK and VRK have one line each, so nothing is tied there. The three MEM lines tie, so Excel looks at the second pair, C2:C7 with -1, amounts descending: 48,000.0048.000,00 (25-0388), 15,250.7515.250,75 (25-0420), 5,000.005.000,00 (25-0415).
  3. The six rows spill from E2 to G7 in the order BNK 3,200.503.200,50, INK 8,120.008.120,00, MEM 48,000.0048.000,00, MEM 15,250.7515.250,75, MEM 5,000.005.000,00, VRK 12,450.0012.450,00, with all three columns.
  4. Without the second pair, SORTBY(A2:C7, B2:B7) would keep the MEM lines in source order: 25-0388 (48,000.0048.000,00), 25-0415 (5,000.005.000,00), 25-0420 (15,250.7515.250,75), so the order within the group would be arbitrary.

Watch out: Swapping the pairs, SORTBY(A2:C7, C2:C7, -1, B2:B7, 1), sorts by amount first: 48,000.0048.000,00 (MEM), 15,250.7515.250,75 (MEM), 12,450.0012.450,00 (VRK)... and the journals are mixed instead of grouped.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Each key has its own sort_order, so the first can go up while the second goes down. The second key is consulted only where the first one ties; that is why the journal groups stay intact and only their inner order changes.

More to watch out for

  • An amount that arrived as text from the export, such as '5000, is ranked after all the real numbers, so with -1 it lands at the wrong end of its group without a warning. Check the column with ISNUMBER.

Variations

=SORT(A2:C7, {2,3}, {1,-1})
=SORT(A2:C7; {2\3}; {1\-1})

SORT accepts arrays of column numbers and orders as well. It works, but SORTBY names the key ranges, which stays readable when columns are added.

Advanced

Largest postings first, whatever the sign

From the XAF auditfile you pulled seven lines with a signed amount: debits positive, credits negative. For your selection of large items you want the biggest postings at the top, whether they are debit or credit.

Sorting on the amount itself would put every large credit at the bottom, and you do not want an extra column of absolute values on the client's sheet.

ABCDEFGH
1Entry no.AccountAmountLargest first
225-010180008000-€52,000.00€ -52.000,0025-0104=SORTBY(A2:C8, ABS(C2:C8), -1)=SORTBY(A2:C8; ABS(C2:C8); -1)11001100€75,000.00€ 75.000,00
325-010213001300€18,150.00€ 18.150,0025-010180008000-€52,000.00€ -52.000,00
425-010345004500-€2,400.00€ -2.400,0025-010540004000-€31,800.50€ -31.800,50
525-010411001100€75,000.00€ 75.000,0025-010213001300€18,150.00€ 18.150,00
625-010540004000-€31,800.50€ -31.800,5025-010616001600-€9,200.00€ -9.200,00
725-010616001600-€9,200.00€ -9.200,0025-010718001800€6,300.00€ 6.300,00
825-010718001800€6,300.00€ 6.300,0025-010345004500-€2,400.00€ -2.400,00
9
Formula cell (E2)InputSpilled result

Formula in E2

=SORTBY(A2:C8, ABS(C2:C8), -1)
=SORTBY(A2:C8; ABS(C2:C8); -1)

Result: 25-0104 | 1100 | €75,000.00 / 25-0101 | 8000 | -€52,000.00 / 25-0105 | 4000 | -€31,800.50 / 25-0102 | 1300 | €18,150.00 / 25-0106 | 1600 | -€9,200.00 / 25-0107 | 1800 | €6,300.00 / 25-0103 | 4500 | -€2,400.0025-0104 | 1100 | € 75.000,00 / 25-0101 | 8000 | € -52.000,00 / 25-0105 | 4000 | € -31.800,50 / 25-0102 | 1300 | € 18.150,00 / 25-0106 | 1600 | € -9.200,00 / 25-0107 | 1800 | € 6.300,00 / 25-0103 | 4500 | € -2.400,00

Step by step
  1. The inner function runs first. ABS(C2:C8) removes the sign from each of the seven amounts: 52,00052.000, 18,15018.150, 2,4002.400, 75,00075.000, 31,800.5031.800,50, 9,2009.200 and 6,3006.300.
  2. That gives a key array of 7 values, one per row of A2:C8. It exists only inside the formula and never appears on the sheet.
  3. SORTBY orders those keys descending because sort_order1 is -1: 75,00075.000 (25-0104), 52,00052.000 (25-0101), 31,800.5031.800,50 (25-0105), 18,15018.150 (25-0102), 9,2009.200 (25-0106), 6,3006.300 (25-0107), 2,4002.400 (25-0103).
  4. It returns the full rows of A2:C8 in that order, with the original signs: 75,000.0075.000,00, -52,000.0052.000,00, -31,800.5031.800,50, 18,150.0018.150,00, -9,200.009.200,00, 6,300.006.300,00, -2,400.002.400,00. They spill from E2 to G8.
  5. Sorting on C2:C8 itself, descending, would give 75,000.0075.000,00, 18,150.0018.150,00, 6,300.006.300,00, -2,400.002.400,00, -9,200.009.200,00, -31,800.5031.800,50, -52,000.0052.000,00: the second largest posting would end up at the bottom.

Watch out: A blank amount cell counts as 0 inside ABS, so that line lands at the bottom without a warning. Check that no amounts are missing before you rely on the order.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

by_array1 does not have to exist on the sheet; any calculation that returns one value per row will do. ABS turns the signed amounts into sizes, so the order follows the size of the posting while the rows still show their real sign.

More to watch out for

  • If one amount is text, ABS returns #VALUE! for that row, so the sort key contains an error and the order becomes unreliable or the result can fail. Convert the column to numbers first, or guard the key with IFERROR.
  • Absolute value ranks a debit and a credit of the same size as equal. If that matters for your selection, add a second key, for example the signed amount, so ties are ordered consistently.

Variations

=SORTBY(A2:C8, ABS(C2:C8), -1, C2:C8, -1)
=SORTBY(A2:C8; ABS(C2:C8); -1; C2:C8; -1)

A tie-breaker for equal sizes: when a debit and a credit have the same absolute amount, the debit comes first.

=SORTBY(A2:C8, RANDARRAY(ROWS(A2:C8)))
=SORTBY(A2:C8; RANDARRAY(ROWS(A2:C8)))

A random order for picking a sample: each line gets a random key and the list is shuffled on every recalculation. Take the first rows of the result as your selection.

Master

Confirmations in a custom follow-up order

You track the debtor confirmations of the 2025 audit. The follow-up list is ordered by status:

  • first the confirmations that came back with a difference
  • then those with no response
  • then those not yet sent
  • confirmed ones at the bottom

Within each status, the earliest follow-up date comes first. The status order lives in a small list on the sheet so the team can change it, and a misspelled status must not break the report.

ABCDEFGHIJK
1RelationCustomerStatusFollow up byStatus order
2D1001Jansen Metaal BVNo response20 Feb 2026DifferenceD1003=LET( confirmations, A2:C8, status, C2:C8, due, D2:D8, rank, IFNA(XMATCH(status, F2:F5), 99), SORTBY(confirmations, rank, 1, due, 1) )=LET( confirmations; A2:C8; status; C2:C8; due; D2:D8; rank; IFNA(XMATCH(status; F2:F5); 99); SORTBY(confirmations; rank; 1; due; 1) )De Boer Techniek BVDifference
3D1002Smit Logistiek BVConfirmed5 Feb 2026No responseD1006Mulder Transport BVDifference
4D1003De Boer Techniek BVDifference12 Feb 2026Not yet sentD1005Hendriks Groep BVNo response
5D1004Kuiper Bouw BVNot yet sent2 Mar 2026ConfirmedD1001Jansen Metaal BVNo response
6D1005Hendriks Groep BVNo response9 Feb 2026D1007Peters Agro BVNot yet sent
7D1006Mulder Transport BVDifference26 Feb 2026D1004Kuiper Bouw BVNot yet sent
8D1007Peters Agro BVNot yet sent16 Feb 2026D1002Smit Logistiek BVConfirmed
9
Formula cell (H2)InputSpilled result

Formula in H2

=LET(
  confirmations, A2:C8,
  status, C2:C8,
  due, D2:D8,
  rank, IFNA(XMATCH(status, F2:F5), 99),
  SORTBY(confirmations, rank, 1, due, 1)
)
=LET(
  confirmations; A2:C8;
  status; C2:C8;
  due; D2:D8;
  rank; IFNA(XMATCH(status; F2:F5); 99);
  SORTBY(confirmations; rank; 1; due; 1)
)

Result: D1003 | De Boer Techniek BV | Difference / D1006 | Mulder Transport BV | Difference / D1005 | Hendriks Groep BV | No response / D1001 | Jansen Metaal BV | No response / D1007 | Peters Agro BV | Not yet sent / D1004 | Kuiper Bouw BV | Not yet sent / D1002 | Smit Logistiek BV | ConfirmedD1003 | De Boer Techniek BV | Difference / D1006 | Mulder Transport BV | Difference / D1005 | Hendriks Groep BV | No response / D1001 | Jansen Metaal BV | No response / D1007 | Peters Agro BV | Not yet sent / D1004 | Kuiper Bouw BV | Not yet sent / D1002 | Smit Logistiek BV | Confirmed

Step by step
  1. LET names the pieces: confirmations is A2:C8 (what will be shown), status is C2:C8, due is D2:D8. The follow-up dates are a key only; they are not part of confirmations.
  2. rank looks up each status in the order list F2:F5 with XMATCH and returns its position: No response 2, Confirmed 4, Difference 1, Not yet sent 3, No response 2, Difference 1, Not yet sent 3. IFNA would turn a status that is not in the list into 99; there is none here.
  3. SORTBY orders by rank ascending: the two 1s (D1003, D1006), the two 2s (D1001, D1005), the two 3s (D1004, D1007), then the 4 (D1002).
  4. Each pair is a tie on rank, so the second key, due ascending, decides: D1003 (12 Feb 2026) before D1006 (26 Feb 2026); D1005 (9 Feb 2026) before D1001 (20 Feb 2026); D1007 (16 Feb 2026) before D1004 (2 Mar 2026).
  5. The result spills from H2 to J8: D1003, D1006, D1005, D1001, D1007, D1004, D1002, each with customer and status.
  6. Type Diference in C4 by mistake. XMATCH finds no match and returns #N/A, IFNA makes it 99, and D1003 sorts to the bottom, whereas an error value in the sort key would make the order unreliable. Swap F2 and F3 and the no-response confirmations move to the top.

Watch out: The mirror-image mistake is sorting on the status text itself. SORTBY(confirmations, status, 1) gives Confirmed, Difference, No response, Not yet sent in English, but Afwijking, Bevestigd, Geen reactie, Nog niet verzonden in a Dutch file: alphabetical order changes with the language and puts confirmed work above the open items, and it still looks sorted.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Text has only one built-in order, alphabetical, and it rarely matches an audit follow-up process. Turning each status into its position in a list replaces the alphabet with your own order, and because SORTBY takes keys as separate arrays, that rank never has to appear on the sheet.

Do not use this construction when the same custom order is needed in many working papers or by colleagues who do not read LET; a visible rank column next to the data (=XMATCH(C2, $F$2:$F$5) filled down) is simpler to review and can feed a plain SORT, at the cost of one more column to maintain.

More to watch out for

  • Without IFNA, a single misspelled status turns rank into an array with an #N/A in it, and the sort order can become unreliable or the report can fail instead of showing the list.
  • If the follow-up dates are text, they still sort, but as text. ISO dates such as 2026-02-12 happen to sort correctly as text; 12-2-2026 does not.

Variations

=SORTBY(A2:C8, XMATCH(C2:C8, F2:F5), 1, D2:D8, 1)
=SORTBY(A2:C8; XMATCH(C2:C8; F2:F5); 1; D2:D8; 1)

The same order without LET and without the safety net: shorter, and a typo in a status can make the order unreliable or the result fail, which IFNA prevents.

Common mistakes

SymptomCauseFix
#VALUE! when the key has a different sizeSORTBY(A2:C9, C2:C10) returns #VALUE!.Use the same rows in both: C2:C9.
An error after adding a second keyYou add a second key, but give its order without the key: SORTBY(A2:C9, B2:B9, 1, -1), and get an error.Keys and orders come in pairs: by_array2 first, then sort_order2. SORTBY(A2:C9, B2:B9, 1, C2:C9, -1).
#VALUE! with a two-column keyThe key is a whole block, SORTBY(A2:C9, B2:C9), and the result is #VALUE!.For two key columns, give two by_array arguments.

Used in combinations