Excel Unplugged

Search a Range for the closest value to a chosen number

How to find a closest value to a chosen number within an Excel range? Let’s do it through a simple example!

We start with a range of values in Excel (A1:G20).

Search a Range for the closest value to a chosen number

Now we are looking for a formula to get the closest value to the value we input in cell I1. First we’re looking for the closest value lower than the selected one.

Closest value lower then selected

Here’s the formula

 =SMALL($A$1:$G$20,COUNTIF($A$1:$G$20,"<"&I1))

The key to this formula is the function SMALL which works like this

=SMALL(Range,n)

And it returns the n-th smallest number in a selected range. In our case, we pair SMALL with COUNTIF which simply gives us a count of how many values are smaller than the chosen number. Once this count is inserted into the SMALL function, we actually get the largest value smaller than the selected number, so exactly what we need.

What about the closest number higher than the selected one?

Closest value higher then selected

Here’s the formula

 =LARGE($A$1:$G$20,COUNTIF($A$1:$G$20,">"&I1))

The key to this formula is the function LARGE, which is the “big” sister of the SMALL function mentioned above. Here’s the functions syntax.

=LARGE(Range,n)

You would never have guessed it, but LARGE returns the n-th largest number in a selected range. This time the COUNTIF function simply gives us a count of how many values are larger than the chosen number. This in combination with the LARGE function, gives us the smallest value larger than the selected number, so again, just what the doctor ordered.

Search a Range for the closest value to a chosen number

You can download the sample workbook that I used for all the screenshots here.

This was a very short post that could easily be entitled “what do functions LARGE and SMALL do?”, and trust me, these are two functions that you should know and use during your Excel adventures. Putting aside the obvious use of simply finding the second largest or 10th smallest value, and the use implied in this post, you could actually use them to SUM up the 10 largest values in a range, which I wrote about in THE SUM OF 10 LARGEST VALUES post, which I strongly urge you to read.

Learn more

Check out our YouTube channel and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!

Comments 8

  1. Peter says:

    Hi, considering closest value lower than the selected one, what happens if you select a number in I1 that’s below the minimal number in the range? For example try 2. It returns num error. Is there a way to avoid this? Such for example to express something along the lines: “The smallest number is 12” or something other preferable to an error.

    Otherwise great article, thanks for the lessons!

    1. Sure thing, you can do a =IFERROR(SMALL(range,COUNTIF(range,”<"&I1)),"Minimum is "&MIN(range))

      1. Peter says:

        Gasper, thank you so much! Your contribution is making learning excel exciting!

  2. Sam says:

    @Gašper – Do you know a way to implement Small / Large function in Power Query (M)

    1. Yes, you do that in two steps…
      First you Sort By the desired column (If small then Ascending If Large then Descending)
      = Table.Sort(#”Changed Type”,{{“Numbers”, Order.Descending}})
      And then you get the desired element from the desired column by inserting a custom step and give it a column name in Square Brackets and the desired element (0 based) in Curly Brackets
      = #”Sorted Rows”[Numbers]{3}
      The three means you’re going to get the Fourth largest (since the sort was descending) value

  3. Benjamin Ben says:

    Gasper can this be used in an index match function, when one of your match parameters is not in the list.?

    1. Hi Benjamin. Yes, you could combine it with the Index Match combination but honestly, XLOOKUP is the way to go as all these features are built in.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.