Sorry, I didn't make myself clear. The lookup_value is variable so are the
figures in the lookup-vector. The results in the result_vector column are
fixed. If my Lookup_value is 80 I want the closest possible match in the
lookup_vector column regardless of whether it is slightly higher than the
lookup_value. Unfotunately the lookup function always matches to a lower
value if it cannot find an exact match. This can result in too much of an
error.

"Dave Peterson" wrote:

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