SMALL returns the k-th smallest value from a range or array of numbers. It is useful for finding lowest scores, bottom sales, or any ranked value without sorting the data.
=SMALL(array, k)What does SMALL do?
SMALL is a statistical function that pulls out the k-th smallest number from a set of values. You give it a range and a rank (k), and it returns the value that sits in that position when the numbers are sorted from smallest to largest. For example, =SMALL(A2:A10,1) gives the minimum, =SMALL(A2:A10,2) gives the second smallest, and so on.
This matters because you often need the lowest or near-lowest values without rearranging your data. Sorting changes the order of your sheet and can break other formulas; SMALL leaves everything in place and simply reports the value you ask for. It also works well inside larger formulas, such as conditional formatting rules or dashboards that highlight the bottom performers.
Imagine you manage a sales team and want to know the three lowest monthly totals for a coaching conversation. Instead of sorting the whole table, you can use =SMALL(B2:B13,1), =SMALL(B2:B13,2), and =SMALL(B2:B13,3) in three cells. The data stays as it is, and you get exactly the figures you need.
When to use SMALL
- Finding the lowest or second-lowest price in a supplier list.
- Identifying the bottom three scores in a class or test results.
- Reporting the smallest sales figure for each region without sorting.
- Extracting the k-th smallest delivery time from a log.
- Building a dynamic ranking where k comes from another cell.
- Highlighting the lowest values with conditional formatting.
SMALL syntax
=SMALL(array, k)| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array of numbers to search. Text and blanks are ignored. Example: B2:B10. |
k | Required | The position from the smallest. Use 1 for the minimum, 2 for the second smallest, and so on. Example: 2. |
What it returns: The k-th smallest numeric value from the array. If k is larger than the count of numbers, SMALL returns #NUM!.
SMALL examples
1 Find the lowest and second-lowest test scores
This example shows the basic use of SMALL on a small list of scores. The formula =SMALL(B2:B6,1) looks at the scores in B2:B6 and returns the smallest number. Because k is 1, it gives the minimum score. If you change k to 2, you get the second smallest. The data does not need to be sorted.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Anna | 78 | 65 | ||
| 3 | Ben | 92 | |||
| 4 | Cara | 65 | |||
| 5 | Dan | 88 | |||
| 6 | Eva | 71 | |||
| 7 |
2 Get the third-smallest delivery time
Here you want the third-fastest delivery time from a log. The formula =SMALL(B2:B7,3) examines the times in B2:B7 and returns the value in the third position when sorted from smallest to largest. Text in column A is ignored because SMALL only looks at numbers. The result is 18, the third-smallest time.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Minutes | |||
| 2 | ORD-101 | 12 | 18 | ||
| 3 | ORD-102 | 25 | |||
| 4 | ORD-103 | 18 | |||
| 5 | ORD-104 | 30 | |||
| 6 | ORD-105 | 15 | |||
| 7 | ORD-106 | 22 | |||
| 8 |
3 Find the second-lowest price from a supplier list
You have a list of supplier prices and need the second-cheapest option. The formula =SMALL(B2:B6,2) returns the second smallest price in B2:B6. This is handy when the absolute lowest price is from a supplier you cannot use, so you want the next best. The result is 9.75.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Supplier | Price | |||
| 2 | Alpha | 12.5 | 9.75 | ||
| 3 | Beta | 9.75 | |||
| 4 | Gamma | 15 | |||
| 5 | Delta | 8.2 | |||
| 6 | Epsilon | 11.4 | |||
| 7 |
4 Use a cell reference for k to make it dynamic
Instead of hard-coding k, you can put the rank in a cell. The formula =SMALL(B2:B7,E2) uses the value in E2 as k. If E2 contains 2, you get the second-smallest sales figure. Change E2 to 3 and the result updates automatically. This makes it easy to build a small ranking table without editing formulas.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | ||||
| 2 | Mia | 1200 | 950 | 2 | ||
| 3 | Noah | 950 | ||||
| 4 | Olivia | 1100 | ||||
| 5 | Paul | 800 | ||||
| 6 | Quinn | 1300 | ||||
| 7 | Rita | 1050 | ||||
| 8 |
5 Combine SMALL with IF to get the lowest value for a region
You want the smallest sale amount for the North region only. The formula =SMALL(IF(A2:A8="North",B2:B8),1) uses an array logic: IF returns the sales for North rows and FALSE for others, then SMALL ignores FALSE and returns the smallest number. In modern Excel this works as a normal formula; in older versions you may need to enter it with Ctrl+Shift+Enter. The result is 300.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 500 | 300 | ||
| 3 | South | 700 | |||
| 4 | North | 300 | |||
| 5 | East | 450 | |||
| 6 | North | 600 | |||
| 7 | South | 200 | |||
| 8 | East | 800 | |||
| 9 |
Common SMALL errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | k is greater than the number of numeric values in the array, or k is 0 or negative. | Make sure k is between 1 and the count of numbers. Use =COUNT(B2:B10) to check how many numbers you have. |
| #VALUE! | k is not a number, for example a text value or an empty cell. | Enter a numeric value for k or reference a cell that contains a number. |
| #NAME? | The function name is misspelled, such as SMAL instead of SMALL. | Check the spelling and use SMALL exactly. |
| Wrong result (ignores text) | SMALL ignores text and logical values in the array, so they are not counted in the ranking. | If you need to rank text-based numbers, convert them to real numbers first, for example with VALUE. |
| Formula returns 0 unexpectedly | The array contains empty cells that Excel treats as 0 in some array operations. | Use a range that contains only the data, or wrap with IF to exclude blanks. |
Tips for using SMALL
- Use
=SMALL(range,1)as an alternative toMINwhen you want consistency with other SMALL formulas. - Put k in a separate cell so you can change the rank without editing the formula.
- Remember that SMALL ignores text, so a column with mixed numbers and text may give unexpected ranks.
- Combine SMALL with
IFto get the k-th smallest value that meets a condition. - Use SMALL inside conditional formatting to highlight the bottom values in a range.
- If you need the k-th largest, use the
LARGEfunction instead.
Frequently asked questions
What is the difference between SMALL and MIN?
MIN returns the single smallest value. SMALL can return the smallest, second smallest, or any k-th smallest value, so it is more flexible.
Can SMALL handle duplicate values?
Yes. Duplicates are counted individually. For example, if the smallest value appears twice, =SMALL(range,1) and =SMALL(range,2) both return that same value.
Why does SMALL return #NUM!?
This happens when k is larger than the number of numeric values in the array, or when k is zero or negative. Check the count of numbers and adjust k.
Does SMALL work with dates?
Yes, because Excel stores dates as numbers. SMALL returns the k-th smallest date serial number, which you can format as a date.
Can I use SMALL with a condition?
Yes, by using an array formula like =SMALL(IF(condition, range), k). In Excel 365 this works normally; in older versions you may need Ctrl+Shift+Enter.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SMALL reference