Find the closest value
 

try this idea
=INDEX(d:d,MATCH(6151,c:c))

--
Don Guillett
SalesAid Software

"TimN" wrote in message
...
How can I find the closest match to a numbe?

I have a cell, say A1 that contains a value of 6151. I have a range of
values in a column say C1:C50 with various numbers. In column D1:D50 there
are values that correspond to those in column C.

I want to write a formula that looks at the value in A1, finds the closest
match to it in C1:C50 and whatever number that is, return the
corresponding
value in column D. So if my value in A1 is 6151 and the closest number in
the range of C1:C50 is 6200, and in column D next to 6200 is a 5, I want
the
formula to return that 5.

Any ideas? Sounds a lot like VLOOKUP, but I can't get it to work becuase
my
6150 is not in the VLOOKUP range.

Find the closest value
 

Hello Tim,

Are you sure that you use the suggested formula?

I used
=INDEX(D1:D50,MATCH(MIN(ABS(C1:C50-A1)),ABS(C1:C50-A1),))
(as array formula!) and it worked.

Regards,
Volker