Return closest numeric match

{2 Comments}

In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8.  In cell B8, a user enters 8.  One may want to answer the following questions:

1. Return the closest number which is less than or equal to the number in cell B8
2. Return the closest number which is greater than or equal to the number in cell B8
3. Return the closest match

You may refer to my solution in this workbook.

RSS 2 Comments…

 Share your views
  1. Rituparna Srinivas August 14, 2012 at 1:46 pm

    i am using excel 2003 which does not have iferror. any alternate?

  2. Hi,

    Try this

    =IF(ISERROR(SMALL(A2:A6,COUNTIF(A2:A6,"<"&B8)+1)),"",SMALL(A2:A6,COUNTIF(A2:A6,"<"&B8)+1))

Leave a Comment

Your email address will not be published.

*

*