Dynamic Arrays
Sort by a custom list order (Critical, High, Medium, Low) with SORTBY & XMATCH
Sort tickets, sizes (XS, S, M, L, XL), or pipeline stages in business-defined order instead of alphabetical A–Z order.
Why alphabetical SORT scrambles severity levels and garment sizes
Sorting incident priorities A–Z puts "Critical", "High", "Low", "Medium" (Low ahead of Medium!), and sorting apparel sizes A–Z yields "L, M, S, XL, XS"!
Mapping each row's label to its position in an explicit Custom Order range ($E$2:$E$5 = Critical, High, Medium, Low) via XMATCH(B2, $E$2:$E$5) converts arbitrary text labels into clean numeric ranks 1, 2, 3, 4. Passing that rank column to SORTBY(A2:B5, C2:C5, 1) spills your table in exact business priority order—and wrapping IFNA(..., 999) pushes unexpected labels safely to the bottom!
| Incident Ticket (A) | Severity (B) | Custom Rank =IFNA(XMATCH(B2,$E$2:$E$5),999) | Sorted Output Position |
|---|---|---|---|
| Fix footer typo | Low | 4 | #4 (Bottom) |
| Checkout DB outage | Critical | 1 | #1 (Top!) |
| Slow search API | High | 2 | #2 |
| Button alignment | Medium | 3 | #3 |
Sort rows by custom rank column with SORTBY
Fill the rank helper from C2 through C5, then enter the SORTBY formula in an empty four-row by two-column area. It returns full records and leaves the source unchanged.
For this four-item priority list, 999 sorts unknown labels after all known ranks. Use a unique custom list and extend the helper and output ranges together when adding records.
When multiple records have equal ranks, this formula specifies no secondary ordering rule. Add an explicit timestamp or ticket-ID sort key if the order within a priority must be defined.
=SORTBY(A2:B5,C2:C5,1)Inspect the last ticket after custom priority sorting
Confirms "Low" lands in row 4 rather than row 3 (where alphabetical A–Z would misplace it).
=INDEX(SORTBY(A2:B5, C2:C5, 1), 4, 1)Try the example
Use the original values or change them to test your understanding.