SMALL
Returns the second, third or any k-th number counting from the smallest: the three lowest prices, the three fastest deliveries.
SMALL(array, k)- array
- A range of numbers, for example $B$2:$B$8.
- k
- Which place you need: 1 gives the smallest number, 2 the next one after it.
Return value
The k-th smallest number: with k = 1 the smallest, with k = 2 the second from the bottom. If k is greater than the count of numbers, you get a #NUM! error.
Examples
The three fastest deliveries
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
Carrier
Delivery, days
Place
Days
SpeedyShip
4
1
QuickPost
2
2
TransLine
6
3
Courier+
3
FreightExpress
5
SameDay
1
Sheet1
Details
SMALL with k = 1 works like MIN, with k = 2 it returns the second smallest number, and so on. This is how you find several best results where less is better: prices, lead times, expenses.
Empty cells and text in the range are skipped, but zeros count and come first.
Common mistakes
- A range without $ signs: when you drag the formula, it shifts, and the result isn’t based on the whole list.
- k is greater than the count of numbers in the range, or less than 1: the formula returns #NUM!.
- A zero instead of “no data” ends up in first place. Leave unknown values empty.