Nth Smallest Value with Criteria in Excel
This article explains how to find the nth smallest value in a dataset that meets specific criteria using Excel. You will learn the formulas, their components, and practical examples to apply them effectively.
Background and Use Cases
Excel’s SMALL function returns the nth smallest value from a range, but it ignores any conditions. When you need the 2nd smallest sales figure for a specific region, or the 3rd lowest test score for a particular class, you must combine SMALL with IF or use the AGGREGATE function. These techniques are essential for data analysis, reporting, and decision-making where simple sorting is not enough.
How It Works: The Core Formulas
There are two primary ways to calculate the nth smallest value with criteria in Excel. Both require array formulas (enter with Ctrl+Shift+Enter in older versions) or dynamic array support (Excel 365).
- SMALL + IF:
=SMALL(IF(criteria_range=criteria, value_range), n)– This evaluates the IF function for each cell, returning the value if it matches the criteria, and FALSE otherwise. SMALL then ignores FALSE values and returns the nth smallest from the remaining numbers. - AGGREGATE:
=AGGREGATE(15, 6, value_range/(criteria_range=criteria), n)– AGGREGATE with function 15 (SMALL) and option 6 (ignore errors) divides each value by the result of the criteria check. When the criteria is met, the division yields the original value; when not met, it produces a #DIV/0! error, which is ignored.
Step-by-Step Example
Suppose you have a list of sales figures in column B (B2:B10) and corresponding regions in column A (A2:A10). You want the 2nd smallest sales value for the “East” region.
- Select a cell for the result, say D2.
- Enter the array formula:
=SMALL(IF(A2:A10="East", B2:B10), 2)and press Ctrl+Shift+Enter. In Excel 365, just press Enter. - If you prefer AGGREGATE, enter:
=AGGREGATE(15, 6, B2:B10/(A2:A10="East"), 2)and press Enter (no array entry needed).
Both formulas return the same result: the second smallest East region sales figure.
How to Choose the Right Approach
Your choice between SMALL+IF and AGGREGATE depends on your Excel version and personal preference.
- SMALL+IF is more intuitive and easier to read, especially for beginners. However, it must be entered as an array formula in versions before Excel 365, which can confuse some users.
- AGGREGATE works without special key combinations and handles errors more gracefully. It is slightly more complex to understand but is more robust in mixed data.
Common Mistakes and Troubleshooting
Several pitfalls can cause errors or incorrect results.
- Forgetting to press Ctrl+Shift+Enter in older Excel versions – the formula will return a #VALUE! error.
- Using the wrong range sizes – criteria and value ranges must be the same size, or the formula will return #VALUE!.
- Text vs. number criteria – ensure the criteria text matches exactly, including spaces and case (though case is not sensitive in Excel).
- No matching values – if no cell meets the criteria, SMALL returns #NUM! and AGGREGATE returns #NUM! as well.
Alternatives and Advanced Variations
For more complex scenarios, consider these alternatives:
- Multiple criteria: Use the multiplication operator to combine conditions:
=SMALL(IF((range1=crit1)*(range2=crit2), value_range), n). - Dynamic arrays: In Excel 365, you can use the FILTER function to extract all matching values, then apply SMALL to the filtered array:
=SMALL(FILTER(value_range, criteria_range=criteria), n). - LARGE for nth largest: Simply replace SMALL with LARGE to get the nth largest value with the same criteria logic.
Frequently Asked Questions
Why does my SMALL+IF formula return #VALUE!?
This usually means you haven’t entered it as an array formula. In Excel 2019 and earlier, select the cell, press F2, then press Ctrl+Shift+Enter. In Excel 365, it should work with a simple Enter.
Can I use wildcards like * in the criteria?
Yes, but only with text criteria. For example, IF(ISNUMBER(SEARCH("East", A2:A10)), B2:B10) will match any cell containing “East”. However, the SMALL+IF formula with a direct equal sign does not support wildcards; you need to use SEARCH or FIND.
What if there are duplicate values?
SMALL counts duplicates as separate entries. For instance, if the two smallest values are both 10, the 1st and 2nd smallest will both be 10. This is usually the desired behavior.
How do I find the nth smallest ignoring zeros?
Add a condition to exclude zeros: =SMALL(IF((criteria_range=criteria)*(value_range>0), value_range), n).
Is AGGREGATE faster than SMALL+IF?
For most datasets, the performance difference is negligible. AGGREGATE may be slightly faster on very large arrays because it avoids the array entry overhead, but the difference is rarely noticeable.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.