Example 1

This example demonstrates how to sort values with sort on right click menu.

Formula in C5:

copied down as far as needed.

Sort selection B5:C17 by column C, order smallest to largest (Excel 2007)

1. Select B5:C17
2. Right click on selection
3. Click "Sort"
4. Click "Custom Sort.."
5. Click "Sort by" Column C
6. Click Sort on "values"
7. Click "smallest to largest"
8. Click OK!
   

Example 2

This example shows you how to sort values using an array formula.

Array formula in C25:

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Copy cell C25 and paste it down as far as needed.

Explaining array formula

=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))

Step 1 - Find k-th smallest difference to the value in C22

=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))

SMALL(ABS($B$25:$B$37-$C$22), ROW(A1))

becomes

SMALL(ABS({1;3;4;7;8;11;11;14;15;17;18;19;20}-10), ROW(A1))

becomes

SMALL({9;7;6;3;2;1;1;4;5;7;8;9;10}, 1)

and returns 1.

Step 2 - Calculate valid numbers

=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))

(COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22)

becomes

(COUNTIF({1;3;4;7;8;11;11;14;15;17;18;19;20}, {1;3;4;7;8;11;11;14;15;17;18;19;20})<>COUNTIF("Sorted closest to farthest to 10", {1;3;4;7;8;11;11;14;15;17;18;19;20}))*ABS({1;3;4;7;8;11;11;14;15;17;18;19;20}-10)

becomes

({1;1;1;1;1;2; 2;1;1;1;1;1;1}<>{0;0;0;0;0; 0;0;0;0;0;0;0;0})*ABS({1;3;4;7;8;11;11; 14;15;17;18;19;20}-10)

becomes

({1;1;1;1;1;2; 2;1;1;1;1;1;1}<>{0;0;0;0;0; 0;0;0;0;0;0;0;0})*ABS({1;3;4;7;8;11;11; 14;15;17;18;19;20}-10)

becomes

({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE})*{9;7;6;3;2;1;1;4;5;7;8;9;10}

and returns

{9;7;6; 3;2;1; 1;4;5; 7;8;9; 10}

Step 3 - Match k-th smallest difference to valid numbers

=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))

MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0)

becomes

MATCH(1,{9;7;6; 3;2;1; 1;4;5; 7;8;9; 10}, 0)

returns 6.

Step 4 - Return a value of the cell at the intersection of a particular row and column

=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))

becomes

=INDEX($B$25:$B$37, 6)

becomes

=INDEX({1;3;4;7;8;11;11;14;15;17;18;19;20}, 6)

returns number 11.

Download excel sample file for this tutorial.

Values-sorted-by-diff.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

ABS(number)
Returns the absolute value of a number, anumber without its sign.

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

Related article:
Excel: Find closest value