Excel: Finding closest value

Question: How to find closest number in a list?

Answer:

Array formula in cell C2:

=INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

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

Explaining array formula

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

Step 1 – Return absolute values of numbers in an array, numbers without its sign

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

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

ABS(A2:A26-C1)

becomes

ABS({3, 77, 7, 62, 66, 48, 83, 57, 69, 10, 86, 42, 71, 90, 34, 17, 73, 77, 20, 37, 59, 50, 99} – 43)

becomes

ABS{-40, 34, -36, 19, 23, 5, 40, 14, 26, -33, 43, -1, 28, 47, -9, -26, 30, 34, -23, -6, 16, 7, 56})

and returns

{40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

Step 2 – Return the smallest number

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

MIN(number1,[number2]) returns the smallest number in a set of values. Ignores logical values and text

MIN(ABS(A2:A26-C1))

MIN({40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

returns 1.

Step 3 – Return the relative position of an item in an array

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

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

MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0)

becomes

MATCH(1, {40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}, 0)

returns 12.

Step 4 – Return a value or reference of the cell at the intersection of a particular row and column

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

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

becomes

INDEX(A2:A26,12)

and returns 42.

EDIT: Added excel workbook to this blog post

Download excel example file

find-closest-value.xls
(Excel 97-2003 Workbook *.xls)

How to find closest values

Array formula in cell C4:

=INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($D$4:D4, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), “A”), 0))

How to create an array formula

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

How to copy array formula

1. Select cell C4
2. Copy cell c4 (Ctrl + c)
3. Select cell range C4:C20
4. Paste (Ctrl + v)

Download excel file

find-closest-values.xls

How to find closest values and return adjacent values

Array formula in cell D5:

=INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($D$4:D4, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), “A”), 0))

Array formula in cell E5:

=INDEX($B$2:$B$24, MIN(IF((D5=$A$2:$A$24)*(COUNTIFS($D$4:D4, $A$2:$A$24, $E$4:E4, $B$2:$B$24)<COUNTIFS($A$2:$A$24, $A$2:$A$24, $B$2:$B$24, $B$2:$B$24)), MATCH(ROW($A$2:$A$24), ROW($A$2:$A$24)), “A”)))

How to create an array formula

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

How to copy array formula

1. Select cell D5
2. Copy (Ctrl + c)
3. Select cell range D6:D28
4. Paste (Ctrl + v)

Download excel example file

find-closest-values-return-adjacent-value.xlsx

Functions:

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

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

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

NSLog boolean

NSLog (@”Value of my BOOL = %@”, someBool ? @”YES” : @”NO”);