To sort a range in Excel for Microsoft 365, use =SORT(A2:C6,3,-1) to sort its rows by the third column, from largest to smallest; to return a value at a particular position, use INDEX. You can also sort manually with Data > Sort. “Sort index” is not one Excel command: it usually refers either to the sort-index argument in SORT or to combining sorting with INDEX.
What “sort index” means in Excel
Excel has two related but distinct ideas here. In the SORT function, the optional sort_index tells Excel which row or column within the supplied array should determine the order. In the INDEX function, row and column numbers identify which value to return from a range. The functions can be used together, but neither is itself named “Sort Index.”
For example, imagine a small table in A1:C6 with headers ID, Name, and Score, followed by these records: 104, Ana, 84; 101, Ben, 98; 105, Cara, 72; 102, Dev, 91; and 103, Eli, 77. In the formula SORT(A2:C6,3,-1), the number 3 means “sort by the third column of this range,” which is Score. The -1 means descending order. The sort index is relative to the selected array, not necessarily the worksheet column number: if the array starts in column B, its first included column has index 1.
Sort a range with the SORT function
SORT creates a sorted result without rearranging the original cells. It is a dynamic array function available in Microsoft 365 and Excel 2021 or later. With the example records in A2:C6, enter this formula in an empty cell:
With the five records in A2:C6 and Score in the third column of that range, use =SORT(A2:C6,3,-1). The result spills into adjacent cells, with Ben (98), Dev (91), Ana (84), Eli (77), and Cara (72), in that order.
The arguments are the array to sort, an optional sort index, an optional sort order, and an optional by-column setting. A sort order of 1 is ascending; -1 is descending. If you leave out the sort index and order, Excel sorts by the first column in ascending order. That default can be surprising when the first column contains an ID rather than the field you want to compare.
- Use 1 for alphabetical A-to-Z or numeric smallest-to-largest order.
- Use -1 for Z-to-A or numeric largest-to-smallest order.
- Use a column number within the selected array to choose the sort key.
In the SORT function dialog-free formula, the fourth argument is normally omitted because the default is to sort rows. Setting it to TRUE sorts by columns instead, which is useful for data arranged horizontally, but unusual for a standard list.
Sort by multiple columns with SORTBY
When the main sort key can contain ties, SORTBY lets you specify a second key. It is also available in Microsoft 365 and Excel 2021 or later. For example, suppose the records remain in A2:C6 and you want the highest scores first, then names alphabetically when scores match:
With records in A2:C6, scores in C2:C6, and names in B2:B6, use =SORTBY(A2:C6,C2:C6,-1,B2:B6,1). Excel sorts by Score descending and then by Name ascending. The sample scores are all different, so the second key does not change this particular example; it matters when two records share a score.
Each sort range in SORTBY must line up with the rows or columns in the array being sorted. If the main array has five records but a sort-key range has four cells, Excel cannot match every record to a key and returns an error. Use SORTBY rather than trying to put several column numbers into the single sort-index argument of SORT.
Use INDEX to return a value by position
INDEX returns the value at a specified position inside a range. It does not sort that range by itself. For a one-column list, its row number counts from the top of the selected range, not from the worksheet’s row 1.
With names Ana, Ben, Cara, Dev, and Eli in B2:B6, enter =INDEX(B2:B6,3) to return Cara, the third name in that range. If the range has multiple columns, specify both a row and a column position. For instance, with the complete records in A2:C6, =INDEX(A2:C6,2,2) returns Ben: the second row and second column within the selected range.
This relative-position behavior is useful when you need the value in a particular sorted position, such as the name associated with the second-highest score. In Microsoft 365 or Excel 2021 and later, you can sort the records inside INDEX and then select from that sorted result.
With the records in A2:C6, Score in the third column, and names in the second column, use =INDEX(SORT(A2:C6,3,-1),2,2). The formula returns Dev because Dev’s 91 is the second-highest score and the requested column is Name. The original data stays in its original order.
Choose a formula or a manual sort
Use a formula when you want a separate sorted view that updates as source values change. Use the ribbon sort when you want to rearrange the source records themselves. For a manual sort in Excel for Microsoft 365 on Windows, select a cell in the list, then choose Data > Sort. In the dialog, choose the column under “Sort by,” select the relevant sort order, and select OK.
If your list has several columns, Excel may ask whether to expand the selection. Choose Expand the selection to keep each row’s fields together. Sorting just the Score column while leaving names and IDs in place would assign scores to the wrong people. If the data has headers, make sure the dialog recognizes that the list has headers so the header row is not mixed into the records.
For a one-level quick sort, select a cell in the key column and use the A-to-Z or Z-to-A controls on the Data tab. For more control, such as sorting first by department and then by date, use Data > Sort and add levels in the dialog. Excel for Mac and Excel for the web also provide sorting controls on the Data tab, though the layout can differ from Windows. The formula examples work in supported Microsoft 365 editions across platforms.
Common problems and how to fix them
- The result shows #SPILL!: A dynamic formula such as
SORTneeds a clear area for its output. Remove or move anything blocking the intended spill range, including cells that appear blank but contain spaces. - The wrong field determines the order: Check the sort index relative to the selected array. If you select B2:D20, the index 1 means column B, not worksheet column A.
- Rows become mismatched: When sorting manually, expand the selection so Excel moves the whole record together. In formulas, make sure the array and each
SORTBYkey cover matching rows. - Numbers sort like text: Values stored as text can sort unexpectedly, for example “100” appearing before “20.” Convert the entries to numbers consistently before sorting if numeric order is intended.
- Ties seem to return the wrong match: A lookup using a score alone may not uniquely identify a record. If duplicate scores are possible, use a secondary key or add a unique ID to the matching logic.
- The formula name is not recognized:
SORTandSORTBYrequire Microsoft 365 or Excel 2021 and later. Older Excel versions need a manual sort or a compatible alternative.
A spilled formula result is a view, not a rearrangement of the source list. If you need to send or print a fixed order, copy the sorted output and use Paste Special > Values in the destination. Conversely, do not sort the source manually if another formula depends on its current row positions unless you have checked those references.
Older Excel: an INDEX and MATCH approach
In versions without SORT, you can build a sorted-position lookup with older functions, but it takes more care. One approach is to use LARGE to find the score at a particular rank, then MATCH to find a row with that score, and INDEX to return the corresponding name. This approach has a limitation: if scores are duplicated, MATCH returns the first matching score each time, so separate records may not be returned as expected.
With scores in C2:C6, names in B2:B6, and the desired rank number in E2, use =INDEX($B$2:$B$6,MATCH(LARGE($C$2:$C$6,E2),$C$2:$C$6,0)). If E2 contains 2, the formula returns Dev, whose score of 91 is second-highest in the sample. For repeated scores, add a unique tie-breaker or use a method designed to handle duplicates. In older Excel, array formulas and helper columns may be needed for a robust multi-key result.
Frequently Asked Questions
What does sort_index mean in Excel?
In SORT, sort_index is the position of the row or column used to order the selected array. For a vertical table, it usually identifies a column within that array; it is not automatically the worksheet’s column letter or number.
How do I sort a column but keep its rows together?
Select a cell in the list, choose Data > Sort, and expand the selection when Excel asks. For a formula-based sorted copy, apply SORT to the entire record range rather than to the key column alone.
Can INDEX sort data by itself?
No. INDEX returns a value at a specified position in a range. Sort the range first, or nest INDEX around a sorted array such as SORT when your Excel version supports dynamic arrays.
What is the difference between SORT and SORTBY?SORT uses a position number to choose a key column or row inside the array. SORTBY accepts one or more separate sort-key ranges, making it more direct for sorting by multiple fields or by a key outside the returned array.
Why does my SORT formula not work?
Check that your Excel version supports the function, that the sort index is valid for the selected array, and that the output area is unobstructed. Also verify that numbers are stored as numbers and that any separate sort-key ranges have the same dimensions as the data.
Related
- Excel Max Columns: The 16,384-Column Limit Explained
- Excel Mini Calendar and Date Picker: Your Options
- Excel Min Sec Format: Display Minutes and Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Excel Without Duplicates: Formulas That WorkTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- SUMIF in Excel: Step-by-Step Formula, Examples, and TipsTo total values that match one condition in Excel, use SUMIF. If categories are in A2:A7 and amounts…
- Aggregate Excel Columns with Formulas and PivotTablesTo aggregate Excel columns, use a function such as SUM, AVERAGE, COUNT, MIN, or MAX across the cells…
- Lower Quartile Formula in Excel: Find Q1 CorrectlyIn Excel, calculate the lower quartile (Q1) with =QUARTILE.INC(A2:A9,1), replacing A2:A9 with your data range. This returns the…
- How to Sort Alphabetically in Excel Without Mixing RowsTo sort alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column, then choose…
- Average Except 0 in Excel: Formula and ExamplesTo average values in Excel while excluding zeros, use =AVERAGEIF(A2:A10,"<>0"), replacing A2:A10 with your data range. The formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.