What's the difference between SMALL and MIN?

MIN returns only the smallest value (equivalent to SMALL with k=1), while SMALL lets you retrieve the 2nd smallest, 3rd smallest, and beyond. Use SMALL when ranking values by size or building percentile analyses.

Can SMALL handle negative numbers and unsorted data?

Yes, SMALL correctly ranks any numbers—including negatives, zeros, and decimals—regardless of input order. It sorts values internally without modifying your source data, making it safe for real-world datasets.

How do I use SMALL to find the k-th smallest based on criteria?

Wrap SMALL with IF in an array formula: =SMALL(IF(criteria, values), k). In Excel 365, use FILTER: =SMALL(FILTER(costs, criteria), k). For single conditions, MINIFS or MAXIFS offer cleaner syntax.

What happens if k is a decimal like 2.5?

SMALL truncates decimals to integers, so 2.5 becomes 2, returning the 2nd smallest. If you need fractional ranking or percentiles, use PERCENTILE or QUARTILE functions instead.

SMALL function

SMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.

=SMALL(array, k)

Generate a SMALL formula

Describe what you need. The generator will reach for SMALL where SMALL is the right tool, and tell you when it is not.

Arguments

How SMALL reads its arguments

Argument	Required	Description
array	Required	A range or array to search; automatically ignores blank cells, text, and errors unless they prevent calculation.
k	Required	A positive integer indicating which smallest value to return; k=1 yields the minimum, k=2 the second minimum, and so on; must be between 1 and the count of numeric values in array.

Returns

Returns a number representing the k-th smallest value in the specified array.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the minimum service cost

Vehicle	Cost
F-150	150
Civic	85
Tacoma	320
Accord	45
Ranger	275
Corolla	120
Silverado	450

=SMALL(E2:E8, 1)

Result: 45

With k=1, SMALL returns the smallest value in the cost column. The Accord's maintenance at $45 is the lowest in the fleet's records.

2. Find the second-lowest maintenance cost

Vehicle	Cost
F-150	150
Civic	85
Tacoma	320
Accord	45
Ranger	275
Corolla	120
Silverado	450

=SMALL(E2:E8, 2)

Result: 85

k=2 skips the minimum and returns the next smallest cost. The Civic's service charge of $85 is the second-lowest maintenance expense in the fleet.

3. Identify a mid-range service cost

Vehicle	Cost
F-150	150
Civic	85
Tacoma	320
Accord	45
Ranger	275
Corolla	120
Silverado	450

=SMALL(E2:E8, 4)

Result: 150

With k=4 on a 7-item dataset, we retrieve the 4th-smallest value ($150 for the F-150), useful for finding middle-tier costs without invoking MEDIAN.

Common errors

Which SMALL error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The k argument is zero, negative, or exceeds the number of numeric values in the array—for example, k=10 when the array contains only 7 numbers.	Ensure 1 ≤ k ≤ COUNT(array). For dynamic k values, wrap with IFERROR: =IFERROR(SMALL(array, k), "Value not found") to gracefully handle out-of-range requests.
#VALUE!	The k argument is text, a formula that returns text, or resides in a cell formatted as Text rather than Number.	Verify k is numeric. Right-click the cell, choose Format Cells, and change the format from Text to Number. Use =VALUE() to convert text strings to numbers if needed.
#REF!	The array argument references cells that have been deleted, moved, or the sheet they belong to has been removed, breaking the reference link.	Restore the deleted cells, or update the formula to point to the correct range. Use named ranges for added stability against future structural changes.

Tips and when to use something else

Use SMALL(array, 1) as a more flexible alternative to MIN when you need extended functionality; however, MIN is simpler and clearer if you only want the minimum.
Combine SMALL with ROWS or sequence functions like =SMALL(costs, ROW(A$1:A$7)) to generate a sorted list from smallest to largest without manually sorting the original data.
For conditional ranking, use FILTER to narrow data first, then apply SMALL: =SMALL(FILTER(costs, criteria), k). Alternatively, wrap SMALL with IF in an array formula.
SMALL ignores text and blank cells, making it robust for real-world datasets. However, error values in the array will stop calculation—wrap data with IFERROR to clean problematic cells first.

Related functions

LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.RANK.EQReturns the position of a number in a sorted list, useful for finding how a value ranks among peers in descending (or ascending) order.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.

Frequently asked questions

What's the difference between SMALL and MIN?: MIN returns only the smallest value (equivalent to SMALL with k=1), while SMALL lets you retrieve the 2nd smallest, 3rd smallest, and beyond. Use SMALL when ranking values by size or building percentile analyses.
Can SMALL handle negative numbers and unsorted data?: Yes, SMALL correctly ranks any numbers—including negatives, zeros, and decimals—regardless of input order. It sorts values internally without modifying your source data, making it safe for real-world datasets.
How do I use SMALL to find the k-th smallest based on criteria?: Wrap SMALL with IF in an array formula: =SMALL(IF(criteria, values), k). In Excel 365, use FILTER: =SMALL(FILTER(costs, criteria), k). For single conditions, MINIFS or MAXIFS offer cleaner syntax.
What happens if k is a decimal like 2.5?: SMALL truncates decimals to integers, so 2.5 becomes 2, returning the 2nd smallest. If you need fractional ranking or percentiles, use PERCENTILE or QUARTILE functions instead.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17