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.
To retrieve the Closest largest value, follow below steps & formula:-
This is the way we can use the Small and large function to retrieve the closest largest and smallest values from the data.
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 firstname.lastname@example.org
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.