One way:

=MIN(IF(ABS(B1-A1:A4)=MIN(ABS(B1-A1:A4)),A1:A4))

With 80 in B1 and 55, 68, 81, 86 in A1:A4

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


Alan Collins wrote:

I need to find the closest match to the lookup_value. LOOKUP returns the
nearest match less than the lookup_value but this may not always be the
closest match. e.g. My lookup_value is 80, the column it is searching has
the following 55;68;81;86, LOOKUP will return results from the row which
contains 68. Obviously 81 is closer, can anyone suggest a way around this?

--

Dave Peterson