## Sort numeric values based on proximity to a given number

The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are from the number in cell C22. Example, 9 and 11 are closest to 10 and are extracted first.

Array formula in C25:

### How to create an array formula

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

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

### Explaining array formula

**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 *.xlsx file

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Create a unique distinct sorted list containing both numbers text removing blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Sort dates within a date range

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

Sort column based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Sort text cells alphabetically from two columns

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Sort values in an Excel table programmatically [VBA]

This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

Sort values in a cell based on a delimiting character [VBA]

This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form