Find nearest value in column

Question

Using Excel, in column A I have a list of numbers

2156
2029
1990
1975
1874

and in cell B1 I have the value 2001.

How do I find the nearest value that matches it in column A? ie 1990 which is A3

Answer 1

You can do this with an array formula:

=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-B1)),ABS(A1:A5-B1),0))

Note that as this is an array formula, you must press Ctrl + Shift + Enter to make this work.