Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the nth smallest value in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=SMALL(B2:B6,2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Sales |
| North | 4200 |
| South | 3100 |
| East | 5800 |
| West | 2700 |
| Central | 4900 |
=SMALL(B2:B6,2)→3100
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the cell where you want the result.
- 2Type =SMALL( then select the range of numbers, e.g. B2:B6.
- 3Enter a comma, then type the rank you want (1 = smallest, 2 = second smallest, etc.).
- 4Close the parenthesis and press Enter.
Tips
- To get the nth LARGEST value instead, use =LARGE(range, n). Both functions ignore text and blank cells.
- If n exceeds the number of values in the range, you'll get a #NUM! error — guard with IFERROR if needed.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if there are duplicate values?
SMALL treats duplicates as separate entries. =SMALL(range,1) and =SMALL(range,2) will both return the same number if the two smallest values are equal.
Can I use SMALL with a condition?
Not directly. Use an array formula like =SMALL(IF(A2:A6="East",B2:B6),1) confirmed with Ctrl+Shift+Enter in Excel, or as a regular formula in Google Sheets.
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.