Retrieving the Closest Larger / Closest Smaller Values from a List when there is No Exact Match

In this article, we will learn how to retrieve the closest smaller and closest larger value from a list in Microsoft Excel. To retrieve the closest larger value, we will use Small function along with Countif function, and for closest smaller value, we will use Large function and countif function.

Why and how we use Small, Large and Countif function?

Countif is used to count the number of times specific data is repeated in a range of cells.

Large is used to return the k-th largest value in a data set.

Small is used to returns the k-th smallest value in a data set.

 

Let’s understand with example how to use small and countif function to retrieve the closest larger value and large function along with countif function to retrieve closest smaller value.

 

We have fruits data with quantity in range A1:B14. In other cell, we have lookup value. So on the basis of lookup value, we want to retrieve closest smaller and larger values.
 

image 1
 
To retrieve the Closest smaller value, follow below steps & formula:-

  • Enter formula in cell E2.
  • =LARGE($B$2:$B$14,COUNTIF($B$2:$B$14,”>”&D2)+1)
  • Press Enter.
  • The closest smallest value will be retrieved from the data.

 
image 2

 

To retrieve the Closest largest value, follow below steps & formula:-

  • Enter formula in cell E2.
  • =SMALL($B$2:$B$14,COUNTIF($B$2:$B$14,”<”&D2)+1)
  • Press Enter.
  • The closest smallest value will be retrieved from data.

 
image 3
 

This is the way we can use the Small and large function to retrieve the closest largest and smallest values from the data.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

 

Users are saying about us...

  1. Thank you for this one. But i ran into another problem though. Please help me.

    A B H I
    1 5 10 3 13
    2 3 13
    3 5 20
    now that i have found the nearest smaller number to 15 i want H1 to have the same row as the value it got from I. How do i do this? THANKS IN ADVANCE!

  2. This is great, just what I wanted, thanks. But a word of warning, it doesn’t work if you copy and paste exactly the formulas given here as the quotation marks are ” (code 148) not ” (code 34).

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube