Dynamic arrays
Custom sort order with formula: rank Critical, High, Medium, Low using SORTBY
Map non-alphabetical status labels to a custom priority table with XMATCH and sort rows automatically via SORTBY.
Why alphabetical sorting scrambles severity and shirt sizes
Sorting status labels A-to-Z puts "Critical", "High", "Low", "Medium" in the wrong operational order (Low before Medium) and scrambles S, M, L, XL sizes.
Mapping each row's Priority against a custom PriorityOrder list ($E$2:$E$5) via XMATCH produces numeric sort keys (1..4) that SORTBY orders cleanly.
| Ticket (A) | Priority (B) | RankKey (C) | CustomList (E) |
|---|---|---|---|
| T-101 | High | 2 | Critical |
| T-102 | Critical | 1 | High |
| T-103 | Low | 4 | Medium |
| T-104 | Medium | 3 | Low |
Map priority rank with IFNA(XMATCH(...), 99) and sort
Wrapping XMATCH(B2, $E$2:$E$5) in IFNA(..., 99) sends any unexpected status label safely to the bottom instead of breaking SORTBY with #N/A.
=TEXTJOIN(", ", TRUE, CHOOSECOLS(SORTBY(A2:B5, C2:C5, 1), 1))Check unlisted priority fallback
Changing T-101 to "Backlog" (not in E2:E5) assigns RankKey 99 and moves T-101 to last place.
=IFNA(XMATCH(B2, $E$2:$E$5), 99)Try the example
Use the original values or change them to test your understanding.