How can i find the nearest value to a value in an array or column

i.e. find nearset to 14 in column of vlaue 1,5,8,13,16,18
need this to come back with value of 13

=INDEX(A1:A6,MATCH(MIN(ABS(A1:A6-14)),(ABS(A1:A6-14)),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom





"Holdey" wrote in message
...
How can i find the nearest value to a value in an array or column

i.e. find nearset to 14 in column of vlaue 1,5,8,13,16,18
need this to come back with value of 13

If the column is always sorted ascending, you can use VLOOKUP to
return the next lowest value to lookup.

=VLOOKUP(14,{1;5;8;13;16;18},1,TRUE)

will return 13.

If A1=14 and B1:B6={1;5;8;13;16;18} then you can use this function:

=VLOOKUP(A1,$B$1:$B$6,1,TRUE)

On Dec 12, 11:16 am, Holdey wrote:
How can i find the nearest value to a value in an array or column

i.e. find nearset to 14 in column of vlaue 1,5,8,13,16,18
need this to come back with value of 13