ExcelBanter - Closest number match help ...

Thats great. Many thanx.

NP

"Frank Kabel" wrote in message
...
Hi
then try the following array formula
=INDEX(A1:A4,MATCH(TRUE,A1:A4=800,0))

--
Regards
Frank Kabel
Frankfurt, Germany

"NP" schrieb im Newsbeitrag
...
Thanks for that. However, I forgot to also mention that I would like
the
value returned to be higher. For example ... if A5 contains 800 then
I would
like the value returned in B5 to be 1500.

Any ideas please?

Many thanks,

NP.

"Frank Kabel" wrote in message
...
Hi
try the followung array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A4,MATCH(MIN(ABS(A1:AA4-A5)),ABS(A1:A4-A5),0))

--
Regards
Frank Kabel
Frankfurt, Germany

"NP" schrieb im Newsbeitrag
...
Looking for a way to find the number that falls closest to
another
number in
Excel ...

Cell A1 contains value 750
Cell A2 contains value 1500
Cell A3 contains value 3000

When I enter a value in cell A5 (e.g. 800), the result I would
like
retuned
in cell B5 is the number from cell a1, a2 or a3 which is closest
to
the
value in A5. Anyone any ideas how to do this please?

Many thanks,

NP.