SMALL
Returns the k-th smallest value in a data set.
=SMALL(array, k)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the k-th smallest value in a data set.
The SMALL function retrieves the k-th smallest value from a numeric data set, allowing for flexible ranking beyond the absolute minimum. This is essential for 'Bottom N' analysis, such as identifying the three lowest-performing regions or the earliest delivery dates in a logistics log. Technically, SMALL ignores logical values (TRUE/FALSE) and text strings within the referenced array. If the data set contains duplicate values, SMALL treats them as distinct positions in the rank; for instance, if two items share the lowest value, both k=1 and k=2 will return that same value. A best practice is to pair SMALL with the SEQUENCE or ROW functions to generate sorted lists dynamically. Note that the function is sensitive to the count of numeric entries; if k is larger than the number of numeric values present, the function fails.
Quick reference
Syntax
=SMALL(array, k)Inputs
Arguments
Example: B2:B100
Example: 3
Formula patterns
Examples
Retrieve the second lowest test score
=SMALL(B2:B15, 2)Find the 3rd earliest appointment date
=SMALL(C2:C20, 3)Dynamic ranking using a cell reference for k
=SMALL(A2:A100, E2)Avoid these issues
Common Errors
#NUM!
Cause: The array is empty, contains no numeric data, or k is outside the valid range (k ≤ 0 or k > count of numbers).
Fix: Check that the range contains numbers and verify that k does not exceed the count of numbers in the range using =COUNT(A2:A10).
#VALUE!
Cause: The k argument provided is not a numeric value.
Fix: Ensure the second argument is a number, a cell reference to a number, or a formula that returns a number.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the k-th smallest value in a data set.
array: Data range k: The position (1=1st smallest)
#NUM!: Check that the range contains numbers and verify that k does not exceed the count of numbers in the range using =COUNT(A2:A10). #VALUE!: Ensure the second argument is a number, a cell reference to a number, or a formula that returns a number.