## Excel: Find the smallest difference between the two closest values

*Article updated on February 26, 2009*

Problem: Find the smallest difference between the two closest values in a list?

Solution:

Let us break this formula to pieces: =SMALL(SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))+1)-SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))), 1) + Ctrl + Shift + Enter (Matrix formula)

SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))+1)-SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1)))

COUNT(A1:A10) counts the number of cells in range A1:A10.

INDEX($A:$A, COUNT(A1:A10)-1) returns the reference of the range $A:$A (row 9)

ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1)) creates a list of row numbers {2,3,4,5,6,7,8,9,10}

SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A,COUNT(A1:A10)-1))+1) takes all the cell values, except the smallest, and sorts them smallest to largest.

SMALL($A$1:$A$10, ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))) takes all the cell values, except the largest, and sorts them smallest to largest.

15-14=1

18-15=3

22-18=4

35-22=13

49-35=14

55-49=6

77-55=22

80-77=3

88-80=8

=SMALL({1,3,4,13,14,6,22,3,8},1)

1 is the smallest difference between the two closest values.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article