There's got to be a better way to handle it than this, but until one of the
MVPs weigh in with an alternative, try this. It assumes you range of values
is in cells B1:F1 and the value you're testing for is in A1.

It's an array formula, meaning you have to enter it by pressing
Ctrl-Shift-Enter.

Also, if the test value equals the mid-point between 2 numbers in the array,
it defaults to the lower number

=IF(ISERROR(HLOOKUP(A1-MIN(ABS(A1-B1:F1)),B1:F1,1,0)),HLOOKUP(A1+MIN(ABS(A1-B1:F1)),B1:F1,1,0),HLOOKUP(A1-MIN(ABS(A1-B1:F1)),B1:F1,1,0))

"Mayo" wrote:


The lookup functions in Excel only look for values equal to the
specified value or the largest value below the specified value. I want
my function to find the value as close as possible to the specified
value.

Example: 1.6 ; 1.7; 1.9 ; 2.05 ; 2.1
Lookup functions for finding the value 2 will return 1.9 instead of
2.05.


--
Mayo
------------------------------------------------------------------------
Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371
View this thread: http://www.excelforum.com/showthread...hreadid=396420