**Question: **How to find closest number in a list?

**Answer**:

**Array formula in cell C2:**

**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

**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:**

**How to create an array formula**

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

**How to copy array formula**

- Select cell C4
- Copy cell c4 (Ctrl + c)
- Select cell range C4:C20
- Paste (Ctrl + v)

**Download excel file**

### How to find closest values and return adjacent values

**Array formula in cell D5:**

**Array formula in cell E5:**

**How to create an array formula**

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

**How to copy array formula**

- Select cell D5
- Copy (Ctrl + c)
- Select cell range D6:D28
- 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