9.3 SORT and SORTBY
=SORT(array, [sort_index], [sort_order], [by_col])– sort by a column number inside the array; order 1 = ascending, -1 = descending.=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)– sort by any array, even one not shown.
| Need | Formula |
|---|---|
| Orders by Amount, largest first | =SORT(tblMini, 7, -1) |
| By City A–Z, then Amount high–low | =SORTBY(tblMini, tblMini[City], 1, tblMini[Amount], -1) |
| Order IDs sorted by delivery time (show only IDs) | =SORTBY(tblMini[Order ID], tblMini[Mins], 1) |
| Pune orders sorted | =SORT(FILTER(tblMini, tblMini[City]="Pune"), 7, -1) |
Worked example. =SORTBY(tblMini[Order ID], tblMini[Mins], 1) starts with BLK-1006 (8 mins), BLK-1001 (9), AMN-1008 (10)…
Ravindra Bagale's Tip
In SORT, sort_index is the column number within the array, not on the sheet. If you apply SORT to 3 FILTERed columns, Amount will be column 3, not 7 – many students get #VALUE! here. If in doubt, use SORTBY – it sorts by the column itself.
Ravindra Bagale's Tip – मराठी
SORT मध्ये sort_index हा array मधला column number आहे, sheet चा नाही. FILTER केलेल्या 3 columns वर SORT लावला तर Amount column 3 असेल, 7 नाही – बरेच students इथे #VALUE! मिळवतात. शंका असेल तर SORTBY वापरा – column नावाने sort होतं.
Ravindra Bagale's Tip – हिंदी
SORT में sort_index array के अंदर का column number है, sheet का नहीं. FILTER किए 3 columns पर SORT लगाया तो Amount column 3 होगा, 7 नहीं – बहुत से students यहाँ #VALUE! पाते हैं. शक हो तो SORTBY इस्तेमाल करो – उसमें column के नाम से sort होता है.
Practice task
Show all orders sorted by Platform then Amount (high to low). Show Order IDs sorted by delivery time without displaying the time column.