# 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.

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.

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.

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

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

1. excellent

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).

3. Perfect, just what I was looking for!

4. oh. the format i placed got edited when i posted it.