Knowledge base / Excel
SMALL in Excel: the k-th smallest value, ties and errors
I show how SMALL returns the k-th smallest number, what it does with repeated values and how to pick several smallest results with a condition, including the rows they belong to.
SMALL returns the k-th smallest number in a range. I use it to find the second shortest delivery time, the third lowest test score or the five cheapest items in a price list. With k equal to 1 it simply returns the minimum.
Its mirror image is LARGE, which counts from the largest value. Both have the same two arguments, so once you know one of them, you know both.
SMALL syntax
=SMALL(array, k)array: the range or array of numbers to choose from.k: the position counted from the smallest value. 1 means the smallest, 2 the second smallest, and so on.
If the range holds n numbers, SMALL(array, 1) equals the smallest of them and SMALL(array, n) the largest. The function only counts numeric values. It skips empty cells and text in the range, so a number stored as text silently drops out of the comparison.
Example: the shortest delivery times
I use a synthetic table of eight orders in the range A1:C9:
| Order | Delivery | Days |
|---|---|---|
| O-01 | Courier | 3 |
| O-02 | Locker | 7 |
| O-03 | Courier | 2 |
| O-04 | Pickup | 5 |
| O-05 | Locker | 2 |
| O-06 | Courier | 9 |
| O-07 | Pickup | 4 |
| O-08 | Locker | 6 |
Sorted, the times are 2, 2, 3, 4, 5, 6, 7, 9. You do not have to sort the data yourself; SMALL works on the unsorted range.
=SMALL(C2:C9, 1)
=SMALL(C2:C9, 3)
=SMALL(C2:C9, 8)The results are 2, 3 and 9. The eighth smallest of eight numbers is simply the largest one.
Ties: every value has its own position
Two orders arrived in 2 days. SMALL does not merge them into one position:
=SMALL(C2:C9, 2)The result is 2 again, not 3. When I ask for “the second shortest time”, I first decide whether I mean the second position in the list or the second distinct value. For the second meaning, in Microsoft 365 and Excel 2021 I remove repeats with UNIQUE first:
=SMALL(UNIQUE(C2:C9), 2)Now the result is 3, because the distinct times are 2, 3, 4, 5, 6, 7 and 9.
The #NUM! error and other traps
SMALL returns #NUM! when k is zero or negative, or greater than the number of numeric values in the range. In the example =SMALL(C2:C9, 9) returns #NUM!, because there are eight numbers. The same error appears when the range contains no numbers at all.
A silent mistake is more common. If one delivery time came from a system as text, for example “4”, SMALL skips it and every later position shifts by one. When a result looks suspicious I check the data type, for example with ISNUMBER, or compare COUNT with the number of rows.
I also watch out for comments written inside formulas. Something like =SMALL(C2:C9, 1) // smallest reads well in a tutorial, but pasted into a cell it fails. I keep the description in a neighbouring column or in the header.
The smallest value with a condition
I want the second shortest time for courier deliveries only. The courier times are 3, 2 and 9, so I expect 3.
In Microsoft 365 and Excel 2021 the simplest way is to narrow the data with FILTER first:
=SMALL(FILTER(C2:C9, B2:B9="Courier"), 2)In older versions I use IF and confirm the formula with Ctrl+Shift+Enter, because it works on an array:
=SMALL(IF(B2:B9="Courier", C2:C9), 2)Rows with other delivery methods get the value FALSE here, which SMALL ignores. Both formulas return 3.
Several smallest values at once
In Microsoft 365 and Excel 2021 I can pass a whole list of positions as k. SEQUENCE creates the numbers 1, 2 and 3, and the result spills into three cells:
=SMALL(C2:C9, SEQUENCE(3))I get 2, 2 and 3. The numbers alone are rarely enough, because I also want to know which orders they belong to. Looking up the order number by value fails on a tie: both 2s would point to the same order, O-03. In that case I sort the whole table and take the first rows:
=SORTBY(A2:C9, C2:C9, 1)The first three rows of the result are orders O-03 and O-05 (2 days each) and O-01 (3 days). Every order appears once, ties included.
SMALL compared with other functions
- MIN gives the same result as
SMALL(range, 1)and is easier to read when I only need the minimum. - LARGE works the same way from the largest value.
- SORT and SORTBY are my choice when I need whole rows rather than one number at a given position.
A short exercise
Copy the order table into a sheet. Find the fourth shortest delivery time for all orders and the second shortest for lockers. Then change the time of order O-07 to the text “4” and see how the fourth result changed. Finally, work out which formula would catch such a row before it breaks a report.
Key takeaways
What does SMALL return when two values are the same?
Each copy takes its own position. For the data 2, 2, 3 both SMALL(range, 1) and SMALL(range, 2) return 2, and only k equal to 3 returns 3.
When does SMALL return #NUM!?
When k is zero or negative, when k is greater than the number of numeric values in the range, or when the range contains no numbers at all.
How do I rank values instead of picking one position?
SMALL returns the value at a given position. To get the position of each value from the smallest, use RANK.EQ with 1 as the third argument, for example RANK.EQ(C2, $C$2:$C$9, 1), or sort the whole table with SORT or SORTBY in Microsoft 365 and Excel 2021.
