The SMALL Function's primary objective is to provide the range's nth-lowest value. Apart from this, you can utilize the SMALL Function in several other ways when working with Google Sheets.

In this guide, we will discuss the SMALL function in detail, provide some examples, and show how it can be nested with some other functions. Read on to learn more.

When to Use the SMALL Function in Google Sheets

The SMALL function’s primary use is to return the cell with the lowest value after searching across a range of cells. For example, if you have a list of values in column A and want to get the number that is the lowest in the list, you may use the SMALL Function to find it.

You can also use the SMALL function to find the 2nd, 3rd, 4th, 5th, etc., lowest value in a range of data. You just have to set the n argument to whichever value you’d like to find.

Syntax of the SMALL Function

The SMALL function uses two arguments to complete its calculations. Below is an example of the syntax structure for the SMALL function.

        =SMALL(data, n)
    

Let’s take a closer look at each phrase to gain a better understanding of what every term means:

• = (equals sign): In Google Sheets, we always begin with the equal sign whenever we want to do a calculation or call a function. It lets the program know we want it to treat the text in the cell as a formula.
• SMALL: This is the function name, so Google Sheets knows which calculation to perform. In this case, identify the nth smallest value in a data set.
• data: This is the array or range that contains the dataset that SMALL will consider. The collection of values set contains data that might be in the form of a date, number, duration value, etc. It is required that all of the values are of the same type.
• n: This is an optional argument and represents the ranking of the value you wish to obtain, represented as a number. The ranking value is a number and must be within the range of 1 to the number of values included in the collection.

For example, if you set n to 4, the SMALL function will give the element in the data collection that is the fourth smallest. If you don’t use the n argument, it will search for the smallest value in the range.

How to Use the SMALL Function in Google Sheets

Let's look at a few practical illustrations. Check out the below examples to better understand how to use the SMALL function in Google Sheets.

Simple SMALL Function

Consider the below data set. Let’s say we wanted to figure out the lowest value. Here’s how we’d do it:

1. Click on an empty cell, C2 in this case
2. Enter the initial part of the formula, which is =SMALL(
3. Highlight or type the range you wish to find the smallest value in. In our example, it’s A2:B11.
4. Type a comma "," to indicate we are moving on to the next argument.
5. Enter the n argument. Since we are finding the smallest value, we type 1.
6. Press Enter

Here’s what the result would be:

We could continue this pattern but change the n value to find the 2nd, 3rd, etc., lowest value. Like so:

Sort Using SMALL

Sorting data in Google Sheets from smallest to largest is a practical but little-known use of the SMALL Function. We employ the SMALL Function and the ROW Function to sort the data. Below is the formula for the function that we are going to use:

        =SMALL($B$2:$B$11, ROW()-1)
    

Note that the code above locks cell references while copying formulas or using autofill. It does so by using absolute references (the $ marks) to indicate those values don’t change for the formulas in subsequent cells.

This function uses the ROW Function to define the n-value. As a result, it sorts them from smallest to greatest, starting with n=1 (the lowest number) and going up to n=10 (the largest number).

Nesting SMALL With IF

There are many other ways to nest the SMALL function, but perhaps the most useful is with the IF function. Suppose we are interested in learning the fasted time from a kart driver in class 2 or worse from the data above. To do this, we will employ an ARRAYFORMULA, SMALL, and IF function, like so:

        =ARRAYFORMULA(SMALL(IF(B2:B9 >=2,C2:C9),1))
    

We used a Google Sheets ARRAYFORMULA to be able to search two columns at once. And the nested IF function of:

        IF(B2:B9>=2,C2:C9)
    

This means the values in column C will only be considered if the value in column B is 2 or more. Check out our Google Sheets IF Function Guide if you want to learn more about how this works.

Potential Error Causes for the SMALL Function in Google Sheets

Alphanumeric Data

The SMALL Function will disregard non-numeric data. Here’s an example:

However, columns with no numeric data will return a #NUM! error, like this:

This is because the SMALL function can’t process non-numerical data.

Out-of-Range Error

SMALL will produce a #NUM! Error when you input a number n, bigger than the value of entries in the data range.

You'll see we are looking for the 12th least number out of a possible set of just 10. Therefore, SMALL gives #NUM! Error.

Alternative to the SMALL Function in Google Sheets

The MIN function is an alternative method for finding the smallest value in Google Sheets. The MIN function returns the lowest value among the given numbers and is entirely automated.

In this instance, we provide two numbers to the MIN function, and MIN returns the lower value. Its syntax is:

         =MIN(value1,value2) 
    

In the example above, the MIN function is used to find the smaller of two numbers. You could also use the MIN function with a cell range instead of two cell references, and it would find the lowest value. However, the MIN function can only find the lowest value, not the nth lowest.

Only a SMALL Part of Your Learning

Now that you understand how the SMALL function works, you’re on your way to mastering Google Sheets. Yet, there’s still a lot to learn, so make sure you keep practicing, and you’ll be a pro in no time.