Cloud Training (Online)




Finding the nearest value in Microsoft Excel

Assume that you have a range of numbers in a column and you are required to find out the nearest value or the closest value to the specified number from the range.

How to find the nearest value in Microsoft Excel ?

You can use the array formula combined with the functions MATCH and MIN to find the same.

Assume that the cells A1:A1 has the values and you have the specified number in the cell “B1” Ex: 9 to find the nearest number and display it in cell B2.

Enter the following formula and press the keyboard shortcut key Ctrl + Shift + Enter.

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

image

Senthil Kumar B
Senthil Kumar is a former Microsoft MVP (Most Valuable Professional). He is a Co-Author of the book "Windows 10 Development Recipes using JavaScript and CSS" for Apress Publication. He is a technical presenter, blogger, mentor and a Geek.  Senthil is a regular speaker is various local user groups. He has presented at conferences like Great Indian Developer Summit (GIDS) & Microsoft DevCamps. You can reach out to him via his Twitter handle @isenthil.