MS Excel: How to Find the Closest Smaller Number in Unordered List of Values
This Excel tutorial explains how to find the closest smaller number from an unordered list of values with screenshots and instructions.
Description
It is much more difficult to find the closest smaller value when your list of numbers is random and not ordered, but it is possible. Let's explore how to do this.
If you want to follow along with this tutorial, download the example spreadsheet.
Example
In our example below, we have a list of material lengths in column A in the range A2:A13. In column B, a customer requests a size such as 318 in cell B2, 445 in cell B3, and so on.
Since we need to provide a length that is no longer than what the customer requested, we need a formula that will find the next smaller value in the list of material lengths (A2:13).
Based on the spreadsheet below:
In cell C2, we've created the following formula that uses the LARGE function with the COUNTIF function to find the next smaller value in the list of material lengths:
=LARGE($A$2:$A$13,COUNTIF($A$2:$A$13,">"&B2)+1)
This formula will look at the value in B2 and find the next smaller value in the range $A$2:$A13. Notice that we used absolute referencing on the range $A$2:$A13 in the formula. This is done so that the formula can be successfully copied to cells C3 and C4 and not readjust the column A cells that will be searched.
More Examples
Here are more examples that show how to find values in unordered lists in Excel:
Advertisements