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?

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

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

What did you get when you tried that formula?

Alan Collins wrote:

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


--

Dave Peterson

Try this *array* formula with your lookup value entered in D1:

=INDEX(B1:B10,MATCH(MIN(ABS(A1:A10-D1)),ABS(A1:A10-D1),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Alan Collins" wrote in message
...
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