I need help with LOOKUP

[Guest]

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]

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.

 

[Guest]

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]

What did you get when you tried that formula?

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.

 

[RagDyeR]

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!
=====================================================

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.