Lookup
Putting a list in order
Definition
Returning the third value of a list sorted in ascending order means wrapping SORT in INDEX.
Syntax
=SORT(array, sort_index, sort_order)
Arguments
| Argument | Meaning |
|---|---|
array |
The list of numbers to sort. |
sort_index |
1 sorts by the first (and here only) column. |
sort_order |
-1 puts the largest value first. 1 puts the smallest value first. |
Worked example
A weigh-in list stores loads in A2:A4. The largest load should sit at the top.
| A: Load |
|---|
| 5 |
| 12 |
| 3 |
Formula: =SORT(A2:A4,1,-1)
Result: 12. Sorted largest first, the list begins with 12, then 5, then 3.
Practice
Cells A2:A4 hold 4, 1, and 7. In B2, write a formula that returns the third value of those numbers after they are sorted in ascending order.
- In B2, write an INDEX and SORT formula that returns the third value of A2:A4 sorted ascending.
- B2 should show the third value of A2:A4 sorted ascending.
Step 1 of 2
Enter =INDEX(SORT(A2:A4),3) in B2.
Sorted, 4, 1, and 7 end with 7.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses SORT.
B2 now shows 7.
The next lesson is Reading which row a cell sits in.
Related functions
SORT is the function that orders that list in ascending order by default.
Common mistake
Returning the first sorted value, as in =INDEX(SORT(A2:A4),1): ascending order starts with 1 instead of 7.