Excel SMALL Function

SMALL returns the nth smallest value in a set of numbers. Ask for the 1st smallest and you get the minimum; ask for the 5th smallest and you get whatever number sits in that position once the data is ranked. The gotcha most people hit first: if two values are tied, SMALL treats them as separate ranks, so the 2nd and 3rd smallest could return the exact same number.

Syntax

=SMALL(array, k)

Parameter	Required	Description
`array`	Yes	The range or array of numbers to rank. Text, logical values, and blank cells inside the range are ignored, not counted as zero.
`k`	Yes	The rank you want, counted from the smallest value. `1` returns the minimum, `2` returns the second smallest, and so on.

Basic Example

A teacher has 30 exam scores in a named range called scores and wants to know the third-lowest score in the class.

=SMALL(scores, 3)

// scores = the range of exam results
// 3      = return the 3rd smallest value in that range

SMALL ranks every number in scores from lowest to highest, then returns whatever value lands in the 3rd position. It doesn't sort the actual cells. It just tells you what that value is, so you can use it in a formula or display it on its own.

How SMALL Works

It ranks from the bottom up

k = 1 always means the smallest value in the array, not the first cell in the range. If you want the largest values instead, you need LARGE, which uses the identical syntax but ranks from the top down.

It ignores text, logicals, and blanks

SMALL only counts numeric values. A blank cell, a text entry like "N/A", or a TRUE/FALSE value inside the array gets skipped entirely rather than treated as zero. This matters if your data has missing entries mixed in with real numbers.

It propagates errors instead of skipping them

If any cell in array contains an error value like #N/A or #DIV/0!, SMALL returns that same error instead of ignoring the bad cell and ranking the rest. One broken cell can break the whole formula.

k can be an array, not just a single number

Feed SMALL an array of ranks instead of one number, and in Excel 365 it spills a full list of results automatically. This is the fastest way to build a ranked list without copying a formula down row by row.

Duplicate values produce ties

If two cells hold the same number, SMALL doesn't skip the repeat. Asking for the 2nd and 3rd smallest from a list containing two identical values of 42 returns 42 both times. That's expected behavior, not a bug, but it trips up anyone who assumes each rank maps to a unique number.

Common Use Cases

Build a bottom-5 list with one formula

You're tracking monthly sales totals in a range named sales_totals and want the five weakest performers without sorting the sheet. This uses SEQUENCE, which requires Excel 365 or Excel 2021.

=SMALL(sales_totals, SEQUENCE(5))

// SEQUENCE(5) generates {1;2;3;4;5}, so SMALL returns
// the 1st through 5th smallest values in one spilled array

This spills five results down from the cell you entered it in. Change the 5 to any number and the list grows or shrinks automatically. On older versions without SEQUENCE, get the same result with separate formulas down a column: =SMALL(sales_totals,1), =SMALL(sales_totals,2), =SMALL(sales_totals,3), and so on.

✓

Wrap SEQUENCE inside SMALL any time you need a ranked list of more than one value. It replaces the old trick of writing =SMALL(range,1), =SMALL(range,2), =SMALL(range,3) as separate formulas down a column.

Find the nth smallest value within one category

You want the second-lowest sales total, but only among reps in the West region. Regions live in a named range called region.

=SMALL(FILTER(sales_totals, region="West"), 2)

// FILTER narrows sales_totals down to West-region rows only
// SMALL then returns the 2nd lowest value from that filtered subset

FILTER requires Excel 365 or Excel 2021. On older versions, the equivalent is an array formula built with IF, entered with Ctrl+Shift+Enter:

=SMALL(IF(region="West", sales_totals), 2)

Find the nth smallest value across multiple criteria

This is where most SMALL tutorials stop short. To rank within two conditions at once, combine them with multiplication inside FILTER or IF, which acts as an AND.

=SMALL(FILTER(sales_totals, (region="West")*(quarter="Q1")), 1)

// (region="West")*(quarter="Q1") returns TRUE only when both conditions match
// SMALL returns the lowest sales total among West-region, Q1 rows

In legacy Excel without FILTER, the same logic works as an array formula:

=SMALL(IF((region="West")*(quarter="Q1"), sales_totals), 1)

Enter it with Ctrl+Shift+Enter on anything earlier than Excel 365. Excel 365 evaluates it as a normal formula thanks to native dynamic array support.

Handling Errors

SMALL throws two catchable errors, and both point to a mismatch between k and the data.

Common causes of #NUM!:

k is less than 1 or greater than the count of numeric values in array
The array contains no numeric values at all, so there's nothing to rank

Common causes of #VALUE!:

k is text instead of a number, like "3rd" instead of 3

=IFERROR(SMALL(sales_totals, 10), "Not enough data")

This is useful on dashboards where k comes from a dropdown or another formula. If a user asks for the 10th smallest value in a range that only has 6 numbers, SMALL returns #NUM! without the wrapper, and IFERROR swaps in a readable message instead.

⚠

Don't wrap SMALL in IFERROR just to hide a wrong k. If the formula returns #NUM! constantly, check whether k is actually larger than your data set before assuming the wrapper fixed the problem.

Notes & Gotchas

What does the SMALL function do in Excel?

SMALL returns the nth smallest numeric value from a range or array. Give it a range and a rank, and it hands back the number sitting at that position once the data is sorted ascending, without actually rearranging any cells.

What is the difference between SMALL and LARGE?

SMALL ranks from the bottom, LARGE ranks from the top. SMALL(range,1) returns the minimum value; LARGE(range,1) returns the maximum. Everything else about the two functions, syntax, error behavior, handling of text and blanks, is identical.

Why does the SMALL function return a #NUM! error?

The most common cause is a k value that's out of range, either less than 1 or greater than how many numbers actually exist in array. It also fires if the array contains zero numeric values, since there's nothing to rank at all. Check the count of numbers in your range against the k you're requesting before assuming the formula is broken.

How do you use the SMALL function with multiple criteria (array formula)?

Combine conditions with multiplication inside FILTER or IF, since multiplying two TRUE/FALSE arrays acts as an AND. In Excel 365, =SMALL(FILTER(range, (cond1)*(cond2)), k) works as a normal formula. In older versions, the same logic needs =SMALL(IF((cond1)*(cond2), range), k) entered with Ctrl+Shift+Enter.

What is the difference between the SMALL function and MIN?

MIN only returns the single lowest value in a range; SMALL can return any ranked position you ask for. SMALL(range,1) and MIN(range) return identical results, but MIN has no way to get the 2nd or 3rd lowest value the way SMALL does with k=2 or k=3.

What happens when the array contains duplicate values?

SMALL doesn't collapse duplicates into a single rank. If a data set contains 15, 15, and 22, asking for the 1st and 2nd smallest both return 15. This becomes a real problem when you're using SMALL alongside MATCH to find which row holds the nth smallest value, since MATCH returns the position of the first match it finds, not necessarily the one tied to your specific rank.

A common fix is to break ties before ranking by adding a tiny, unique fraction based on row position:

=SMALL(scores + (ROW(scores)-ROW(INDEX(scores,1)))/100000, 3)

This nudges every value by an amount too small to change the ranking order, but large enough to make each entry unique, so a follow-up MATCH lands on the correct row every time.

Does SMALL work with dates and times?

Yes. Dates and times are stored as serial numbers in Excel, so SMALL ranks them exactly like any other numeric value. =SMALL(order_dates, 1) returns the earliest date in the range, and formatting the result cell as a date displays it correctly.

Related Functions

Function	Use this when...
`LARGE`	You need the nth largest value instead of the nth smallest.
`MIN`	You only need the single lowest value, not a specific rank.
`RANK`	You need to know a value's rank within a data set, rather than pull the value at a given rank.
`FILTER`	You need to narrow an array to matching rows before ranking it with SMALL.