Excel function

  • Thread starter Thread starter Holdey
  • Start date Start date
H

Holdey

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
 
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)
 
Back
Top