Get Nth Values with SMALL and LARGE in Excel

Updated 14 November 2026 · Standard formula, shown below

This guide explains how to use Excel’s SMALL and LARGE functions to extract the nth smallest or largest value from a range of numbers. You’ll learn the syntax, see step-by-step examples, and avoid common pitfalls.

What You’ll Need

Microsoft Excel (2010 or later, including Microsoft 365).
A worksheet containing a range of numeric values (e.g., sales figures, test scores, temperatures).
Basic familiarity with entering formulas into cells.

Step-by-Step

Identify your data range. For example, suppose your numbers are in cells A2:A100.
Decide which nth value you need. For the smallest nth value, use SMALL; for the largest, use LARGE. If you want the 3rd smallest, n=3; the 2nd largest, n=2.
Enter the formula. Click an empty cell and type:

=SMALL(A2:A100, 3) for the 3rd smallest.

=LARGE(A2:A100, 2) for the 2nd largest.
Press Enter. Excel returns the value at that position. For example, if your data is {5, 2, 9, 1, 7}, SMALL(A2:A6, 2) returns 2 (the second smallest), and LARGE(A2:A6, 2) returns 7 (the second largest).
Use a cell reference for n (optional). Put the nth value in a separate cell, say C1, and use =SMALL(A2:A100, C1). This lets you change C1 to get different positions without editing the formula.
Handle dynamic arrays (Excel 365). If you want several nth values at once, use =SMALL(A2:A100, {1,2,3}) as an array formula. In Excel 365, this spills results into adjacent cells automatically. In older versions, press Ctrl+Shift+Enter after typing.
Combine with other functions. For example, to find the average of the top 5 values, use =AVERAGE(LARGE(A2:A100, {1,2,3,4,5})) (again, array entry in older Excel).

Common Mistakes

Using the wrong n. SMALL with n=1 gives the minimum; LARGE with n=1 gives the maximum. Ensure n is between 1 and the count of numbers in the range.
Including text or blank cells. SMALL and LARGE ignore text and blanks, but if your range contains errors (like #DIV/0!), the formula returns an error.
Forgetting to lock ranges when copying. If you copy the formula down, use absolute references like $A$2:$A$100 to prevent the range from shifting.
Using n=0 or negative n. This returns a #NUM! error. n must be a positive integer.
Assuming SMALL and LARGE sort the data. They don’t change your data; they just return a value based on the position in a sorted order.

Tips

Use =SMALL(range, ROWS($A$1:A1)) to create a formula that you can drag down to list all values from smallest to largest. The ROWS function increments automatically.
To get the nth smallest unique value, you’ll need a more complex array formula, such as =SMALL(IF(COUNTIF($B$1:B1, $A$2:$A$100)=0, $A$2:$A$100), 1) entered with Ctrl+Shift+Enter (in older Excel).
Combine with IF to conditionally get nth values. For example, =LARGE(IF(B2:B100="Yes", A2:A100), 2) returns the 2nd largest value in A where the corresponding B cell is “Yes”. Remember to enter as an array formula in older Excel.
Use the AGGREGATE function if you need to ignore error values: =AGGREGATE(15, 6, A2:A100, 3) gives the 3rd smallest ignoring errors (15 = SMALL, 6 = ignore errors).

Troubleshooting

Formula returns #NUM! This happens when n is larger than the number of numeric values in the range, or n is 0 or negative. Check your n value and the range contents.

Formula returns #VALUE! Usually because the range contains non-numeric text that cannot be coerced. Ensure all cells in the range are numbers or blanks.

Formula returns 0 unexpectedly. If your range includes cells with zero values, SMALL might return 0. If you want to ignore zeros, use an array formula like =SMALL(IF(A2:A100<>0, A2:A100), n).

Results don’t update when data changes. Check that calculation is set to Automatic (Formulas > Calculation Options). If it’s manual, press F9 to recalculate.

Array formula not spilling in Excel 365. Ensure you don’t have anything in the adjacent cells that would block the spill range. Also, if you’re using an older version, remember to press Ctrl+Shift+Enter.

Frequently Asked Questions

Q: What’s the difference between SMALL and LARGE?
A: SMALL returns the nth smallest value; LARGE returns the nth largest. Both require a range and a position number n.

Q: Can I use SMALL to find the median?
A: Yes, if the count of numbers is odd, the median is the ((count+1)/2)th smallest. For even counts, you’d average the two middle values.

Q: How do I get the second highest value excluding duplicates?
A: Use an array formula: =LARGE(IF(FREQUENCY(A2:A100, A2:A100)>0, A2:A100), 2). This requires Ctrl+Shift+Enter in older Excel.

Q: Does SMALL work with dates?
A: Yes, dates are stored as numbers in Excel, so SMALL and LARGE work normally. Format the result cell as a date.

Q: Can I use SMALL across multiple sheets?
A: Yes, you can reference 3D ranges like =SMALL(Sheet1:Sheet3!A1:A10, 2), but it only works if the sheets are consecutive and the ranges are identical.

Q: What if I need the nth smallest in a filtered list?
A: Use the SUBTOTAL function with function_num 15 (SMALL) or 16 (LARGE). For example, =SUBTOTAL(15, A2:A100) gives the smallest in filtered data, but it only supports n=1. For other n, you’d need a more complex approach.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.