How to use the SMALL function
The SMALL function returns the k-th smallest value from a group of numbers. The first argument is a cell range or array that you want to find the k-th smallest number in.
The second and last argument is k which is a number from 1 up to the number of values you have in the first argument.
Example shown in the above image, formula in cell E3 returns 17 because it is the third smallest number in cell range B3:B11.
Cell range B3:B1 contains the following numbers: 65, 50, 17, 22, 20, 66, 13, 18, and 15. Cell D3 contains the number that specifies which k-th smallest number to extract.
Table of Contents
1. SMALL Function Syntax
SMALL(array, k)
2. SMALL Function Arguments
array | Required. A group of numbers you want to extract the k-th smallest number from. |
k | Required. k-th value, 1 returns the smallest number, 2 returns the second smallest number etc. |
The SMALL function is very versatile and is, in my opinion, one of the most used functions in Microsoft Excel. You can construct both regular and array formulas with the SMALL function.
It also ignores blank values and text values, however, not error values. This article explains a workaround if you have error values in your data set.
How to ignore error values using the SMALL function
You can use a cell range across multiple columns like:
It will also work with multiple non-adjacent cell ranges with minor changes to the formula.
3. How does the SMALL function handle text and blank values?
The image above shows a formula in cell B13 that extracts the third smallest value from cell range B3:B10. Note that the cell range contains both text values and blank cells.
becomes
SMALL({3; 6; "A"; 5; "X"; 0; 4; 4}, 3)
Text strings and blanks are overlooked. The array becomes
SMALL({3; 6; ; 5; ; ; 4; 4}, 3)
and returns 4. 4 is the third smallest numerical value in the array.
4. How to use constants (hardcoded) values in the SMALL function
In case you want to work with an array instead of a cell range in the SMALL function use curly brackets like this:
This means that the values are hardcoded into the formula, however, you still enter it as a regular formula.
There is one downside with this approach and that is that you must edit the formula to be able to change a value in the array.
To convert a cell range to an array select the cell reference in the formula and press function key F9.
This will convert the cell range to an array of values.
5. How to use a condition in the SMALL function
This array formula in cell F7 calculates the second smallest number from cell range C4:C14 based on a condition specified in cell F3.
The IF function returns one value if the logical test returns TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
In this case, the IF function compares the values in cell range B4:B14 to the value in cell F3 and returns and an array that contains boolean values TRUE or FALSE.
SMALL(IF(B4:B14=F3, C4:C14, ""), F4)
becomes
SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)
becomes
SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, C4:C14, ""), F4)
The IF function then returns the corresponding value from the second argument if TRUE and the third argument if FALSE.
SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, C4:C14, ""), F4)
becomes
SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE},{55.47; 60.69; 5.83; 8.08; 94.68; 70.79; 41.03; 24.44; 81.68; 72.67; 2.25},""), F4)
becomes
SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)
and returns 8.08 in cell F7.
6. How to quickly return sorted numbers using the SMALL function
Formula in cell E3:
The second argument k can be changed from a number to a function that returns numbers, this can be handy when you want to return multiple numbers sorted from small to large.
SMALL(array, k )
The ROWS function returns the number of rows a cell range contains. If you combine absolute and relative references into one cell reference you can build a dynamic cell reference that changes when you copy the cell and paste to cells below.
$A$1:A1
The first part of the cell reference is absolute meaning it won't change when the cell is copied and pasted to cells below. You can see that it is absolute bu the $ dollar signs in front of the column letter and the row number.
The colon is used to describe a cell range that contains multiple cells however it can also describe a reference to a single cell. The second part is relative meaning it will change when you copy the cell.
For example, the table below demonstrates how the cell references in the formula change when copied.
Cell E3: =SMALL($B$3:$B$11, ROWS($A$1:A1))
Cell E4: =SMALL($B$3:$B$11, ROWS($A$1:A2))
Cell E5: =SMALL($B$3:$B$11, ROWS($A$1:A3))
The cell range expands by one row for each new cell below you paste it to. The ROWS function calculates the number of rows in that cell range and returns that number.
Cell E3: =SMALL($B$3:$B$11, 1)
Cell E4: =SMALL($B$3:$B$11, 2)
Cell E5: =SMALL($B$3:$B$11, 3)
You can press and hold on the black dot in the bottom right corner of the selected cell then drag down as far as needed to quickly copy the cell to cells below, see animated image above.
You can also double press with left mouse button on with left mouse button on the black dot located in the bottom right corner of the selected cell to quickly copy the cell to cells below.
Excel uses existing values in the adjacent column to determine when to stop copying.
'SMALL' function examples
The following 139 articles contain the SMALL function.
This post explains how to lookup a value and return multiple values. No array formula required.
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
Lookup with criteria and return records.
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]
Functions in this article
Functions in 'Statistical' category
The SMALL function function is one of many functions in the 'Statistical' category.
Excel function categories
Excel categories
12 Responses to “How to use the SMALL function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
I am in love with.. your formula explanation.. :)
Waiting eagerly for MMULT & some D-Functions..
Regards,
Deb
Debraj Roy,
Thank you!
I am curious, in what situation do you use MMULT?
Hi Oscar,
We can use MMULT in all cases where SUMPRODUCT fails..
with only Two Criteria..
* Only TWO Array can be multiplied..
* 1st Array's No Of Row.. Should be Same as 2nd Array's No Of Column..
Unlike SUMPRODUCT, It returns ARRAY output..
I think, Binary Addition & Binary Multiplication are the base of all FORMULA's & FUNCTION..
and you are doing a great job, by teaching/using them in your daily blog..
Regards!
Deb
Debraj Roy,
Well, I am learning from you right now.
Can you provide an example where SUMPRODUCT fails and MMULT succeeds?
I searched and found my old mathematics books from college, I had forgotten the basics of multiplying two matrices. :-)
It is worthwhile mentioning that in both Small and Large K could also be an array
So if A1:A10 contains random numbers the below formulas
=Large(A1:A10,{1,2,3}) - Return an array containing the top 3 numbers
=SUM(Large(A1:A10,{1,2,3}) -Array entered Returns the Sum of the top 3 numbers
=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&TopN))))- Array Entered Returns the sum of the Top N numbers as defined in the Cell/Named Constant TopN
=Large(A1:A10,Row(A1:A10))- Array entered returns an array of numbers in A1:A10 in Descending order
Likewise Small
sam,
It is worthwhile mentioning that in both Small and Large K could also be an array
Yes you are right! Thanks for pointing that out.
[…] SMALL(array,k) Returns the k-th smallest number in this data set. […]
=Small({VALUE(DV147),VALUE(DZ147),VALUE(ED147),VALUE(EH147)},2) will not work. If I use sum and the "Value(-----)" amounts, it works.
What am I doing wrong?
The numbers are stored as text in those cells for other reasons.
[…] LARGE function extracts the n-th largest number twice for every two cells. This allows us to return both the […]
I'm using the SMALL function inside an array. I understand how to use the function to return an array where values are greater than or equal to a number. But how do I use the function if I want to return results that are between two numbers?
I've tried nesting an AND statement within the IF statement, but it isn't working (no values are returned).
Any suggestions? Thanks!
julie,
=SMALL(IF(($A$2:$A$10<$F$2)*($A$2:$A$10>$F$3),$A$2:$A$10,""),ROW(A1))
[…] LARGE(array,k) returns the k-th largest row number in this data set. […]