Here is a custom function to get the closest value from a data range
If your data range is A1:A20 and the look-up value is B1
=Closest(B1, A1:A20)
To install the custom function:
Alt+F11 to open the VB editor
Select the Insert menu\ Module
Paste the code below in the VB edit window
---
Function Closest(rngVal As Range, rngData As Range)
Application.Volatile
Dim temp As Double
Dim Cell As Range
'
temp = 1.79769313486231E+308
For Each Cell In rngData
If IsNumeric(Cell.Value) Then
If Abs(Cell.Value - rngVal.Value) < temp Then
temp = Abs(Cell.Value - rngVal.Value)
Closest = Cell.Value
End If
End If
Next Cell
End Function
----
In the case where your number is equal distant from two values in the data range, (e.g 15 is equal distant between 10 and 20), the Closest function will return the first value in the data range.