Google Sheets SMALL function may seem simple at first glance, but did you know that you can use this formula for complex calculations, including dynamic VLOOKUP? In this article, I will guide you through the basic and advanced uses of the SMALL formula in Google Sheets, unlocking its full potential for your data analysis needs.
The Purpose of the SMALL Formula in Google Spreadsheets
The SMALL formula in Google Sheets has the following syntax:
SMALL(data, n)Here, “data” refers to the array or range containing the dataset to consider, while “n” represents the rank from smallest to largest of the element to return. Essentially, the SMALL function returns the nth smallest occurrence of a number from a data set.
To better understand the concept, let’s dive into some examples.
Basic SMALL Formula Examples in Google Spreadsheets
In the example above, you can see that the second formula returns an error value. This is because the range A2:A6 does not contain any numeric values. It is important to note that the SMALL function requires numeric values in the range to perform effectively.
Now, let’s take a look at another example:
=small({100,225,150,400,500},2)This formula returns the value 150, as it is the second lowest value in the array. The first lowest value being 100. Hopefully, you now have a better understanding of how the SMALL formula works. Let’s explore some more advanced examples.
Advanced SMALL Formula Examples in Google Spreadsheets
You can use the SMALL function in combination with VLOOKUP to search for specific values in a range and retrieve corresponding data. However, there are cases where the normal usage of VLOOKUP falls short.
In such situations, the SMALL function can come to the rescue. Although its primary purpose is to return the nth lowest value in a range, we can utilize it to find the nth occurrence of a text or numeric value in a range. This opens up possibilities for dynamic VLOOKUP formulas.
To demonstrate, let’s consider the following formula:
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),2))In this formula, we are using the SMALL function alongside the IF logical function and the ROW function. The result of this formula will be the relative position of the second occurrence of the item “Mango” in the range A2:A6.
By manipulating this formula, you can find the relative position of the first occurrence simply by changing the number 2 to 1:
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),1))Here, the formula returns the value 2, which represents the first occurrence of the item “Mango”.
To break down the formula used, we have two parts:
Part 1: ROW formula
The ROW formula on its own looks like this:
=ArrayFormula(ROW(A2:A6)-1)The range A2:A6 returns the row numbers of the items in the range. However, by subtracting 1 from the row numbers, we obtain the relative positions of the items instead.
Part 2: IF formula
Next, we combine the ROW formula with the IF function to return the row number if the logical expression “Mango” is found, or “False” otherwise:
=ArrayFormula(IF("Mango"=A2:A6,ROW(A2:A6)-1))This formula generates a result where 2 and 4 represent the relative positions of the second occurrence of “Mango” in the range. When combined with the SMALL function, these formulas enable dynamic lookups in Google Sheets.
Do you want to learn more about dynamic VLOOKUP formulas? Visit Crawlan.com for a dedicated tutorial on this topic.
Conclusion
The SMALL formula in Google Sheets is a powerful tool for analyzing data. By leveraging its capabilities, you can perform complex calculations, including dynamic lookups. Whether you are a data analyst or a small business owner, mastering the SMALL formula will enhance your Google Sheets skills and empower you to make better-informed decisions. So go ahead and explore the possibilities that the SMALL formula offers!
Remember, Crawlan.com is your go-to resource for all things Google Sheets. Stay tuned for more tips, tricks, and tutorials to elevate your data analysis game.
