What it does
SMALL works like LARGE but counts from the smallest. =SMALL(B2:B20,2) returns the second smallest value. Empty cells and text are skipped, while 0 counts as one of the smallest values.
Put the k values 1, 2, 3… in a column and fill the formula down, and you get a list of the numbers from smallest to largest that re-sorts itself whenever the source changes. In Microsoft 365, SORT does this in a single formula.
Dates are numbers, so SMALL also finds the earliest dates. For the smallest value that meets a condition use MINIFS; to find the rank of a value, RANK.EQ.
Syntax
=SMALL(array, k)=KÜÇÜK(dizi; k)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range to search. Empty cells and text are skipped. |
k | Required | Which smallest value to return; 1 is the smallest. It cannot be less than 1 or more than the number of values in the range. |
Examples
1The three lowest quotes and their suppliers
Column D holds 1, 2 and 3. Column E finds the quote at each position, and column F returns the supplier with INDEX and MATCH.
=SMALL($B$2:$B$6,D2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Supplier | Quote | Position | Quote | Supplier | |
| 2 | Brown & Sons | 48,250.00 | 1 | 46,900.00 | Northgate Ltd | |
| 3 | Northgate Ltd | 46,900.00 | 2 | 47,450.50 | Star Market | |
| 4 | Harbour Trade | 51,300.00 | 3 | 48,250.00 | Brown & Sons | |
| 5 | Star Market | 47,450.50 | ||||
| 6 | Oak Builders | 49,800.00 |
- E2 (filled down to E4)
=SMALL($B$2:$B$6,D2) - F2 (filled down to F4)
=INDEX($A$2:$A$6,MATCH(E2,$B$2:$B$6,0))
The lowest quote comes from Northgate Ltd (46,900.00), the second from Star Market and the third from Brown & Sons.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2List delivery times from shortest to longest
The source list is not sorted. With the positions 1-5 in column D, SMALL lists the times from smallest to largest; when the source changes, the sorted list follows.
=SMALL($B$2:$B$6,D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Days | Position | Time | |
| 2 | S-1001 | 4 | 1 | 2 | |
| 3 | S-1002 | 2 | 2 | 3 | |
| 4 | S-1003 | 7 | 3 | 4 | |
| 5 | S-1004 | 3 | 4 | 5 | |
| 6 | S-1005 | 5 | 5 | 7 |
- E2 (filled down to E6)
=SMALL($B$2:$B$6,D2)
Column E reads 2, 3, 4, 5, 7.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3The three earliest application dates
Dates are numbers, so SMALL finds the earliest ones. The result cells are given a date format.
=SMALL($B$2:$B$6,D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Application | Date | Position | Date | |
| 2 | B-201 | 14-Oct-2025 | 1 | 29-Sep-2025 | |
| 3 | B-202 | 3-Oct-2025 | 2 | 3-Oct-2025 | |
| 4 | B-203 | 21-Oct-2025 | 3 | 8-Oct-2025 | |
| 5 | B-204 | 29-Sep-2025 | |||
| 6 | B-205 | 8-Oct-2025 |
- E2 (filled down to E4)
=SMALL($B$2:$B$6,D2)
The first application came on 29-Sep-2025, the second on 3-Oct-2025 and the third on 8-Oct-2025.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: k is less than 1 or larger than the number of values in the range.
Fix: Limit k with
=COUNT(B2:B20); in a sorted list with spare rows, wrap the formula in IFERROR.- Smallest value is 0
Cause: Rows without data were filled with 0, and SMALL treats zero as a real value.
Fix: Leave the cell empty when there is no data, or use MINIFS to skip zeros.
Good to know
- To keep a long sorted list without errors in the spare rows, use
=IFERROR(SMALL($B$2:$B$20,D2),""). - For the average of the three lowest quotes in Microsoft 365, use
=AVERAGE(SMALL(B2:B20,SEQUENCE(3))).
In Nexell
Get Nexell to do it from a sentence
Show the three lowest quotes from the quote list in a separate table, from lowest to highest
Convert to tableBring your quote list into the tool and type this. Nexell puts the three lowest quotes into a separate table, lowest first, and shows it in a preview first; once you approve, you download it as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find the 3 smallest values in Excel?
Type =SMALL($B$2:$B$20,1), =SMALL($B$2:$B$20,2) and =SMALL($B$2:$B$20,3), or put the k values in a column and fill one formula down.
What is the difference between SMALL and MIN?
MIN returns only the smallest value. SMALL asks which smallest you want; =SMALL(B2:B20,1) equals MIN.