but the formula is not working when it is looking for a
value which is not actually in the table. and it is not returning me the correct
value.

-If you want the closest value to MAX(M:M)/2 when there is no exact match  you
have to do a couple things:

A. Step 1 as above, but then sort the data in ascending order by the new
column A.

B. In the formula in step 2 above, replace FALSE with TRUE.