Google Sheets SMALL function seems so simple at first look. But do you know you can use this simple formula in complex calculations that even for dynamic VLOOKUP. So, this time I am going to explain you how to use SMALL Formula for basic as well as complex calculations in Google Sheets.
Purpose of SMALL Formula in Google Spreadsheets
data – The array or range containing the dataset to consider.
n – The rank from smallest to largest of the element to return.
The SMALL function in Google Sheets returns nth smallest occurrence of a number from a data set.
See the SMALL function Examples under the below subheading to get familiar with it.
Basic SMALL Formula Examples in Google Spreadsheets
In the above screeshot, the second formula returns error value. This is because range “A2:A6” has no numeric value. That means, there should be numeric values in the range for the SMALL function to perform.
Check the other examples in the above screenshot and then move to the following SMALL Formula example.
This formula returns the value 150 as it’s the 2nd lowest value in the array, where first lowest value is 100. Hope you could understand this concept. See one more example.
The below formula can SUM the smallest two numbers from the range “C2:C6” using the SUM and SMALL formula combination.
SMALL function is not a “Small” Google Sheets function. The below example is an eye opener. The below example part is very important as we can use this method to make dynamic lookup formulas.
Advanced SMALL Formula Examples in Google Spreadsheets
You can use Vlookup to search down the first column of a range with a search key. If matching value found, it can return value of a specific cell in the same row.
Below VLOOKUP formula returns the result 200. It is the value in the second column of the first occurrence of the Search Key “Mango” in Cell A3. It repeats second time in cell A5. But we can’t use VLOOKUP to find it in normal way.
But you can do that with a combination of formulas where Google Sheets SMALL function can play a vital role. How?
Here we are going to learn the role of SMALL function in such lookup, not how to use SMALL function for lookup.
Thought the normal purpose of SMALL function is to return the Nth Lowest value in a range, we can use it to return Nth Occurrence of a Text or Numeric Value in a range. This value you can use in dynamic VLOOKUP. That steps you can find below. To learn the DYNAMIC VLOOKUP go to our dedicated tutorial HERE.
Use of SMALL Function to Return Nth Occurrence of a Text String
See the above small data set. Here you can see one item “Mango” repeated twice. With VLOOKUP, you can use Search Key as “Mango” and look for the first occurrence of this. That’s in Cell A3. There is no way to look for the second occurrence in Cell A5.
What Google Sheets Small formula can do in this case. It can return the relative position of the second, third or nth occurrence of the repeated item. Here is that trick.
We can use the same above small data set. This formula, for example in Cell D6, returns the value 4. which is the relative position of “Mango” in cell A5, which is the second occurrence of that item. To find the first occurrence, just change the number 2 to 1 in the last part of the above formula which is Red coloured. See that modified formula below.
This formula returns the answer 2, which is the relative position of the first occurrence of the item “Mango”.
Here is the Break up of the formula used.
Part 1: ROW formula
If you extract the ROW formula part from the above, it will be as follows.
Here “A2:A6” is the range. The above formula without “-1” at the end will return the row numbers of the items in the range. For example 2 for Apple, 3 for Mango and so on. But we need the relative position like Apple 1, Mango 2 and like wise. To do that we applied a “-1” at the end of the formula. You should change this number to “-2”, if your range is “A3:A7”. Hope you understood.
Part 2: IF formula
Now we can use IF function with this ROW formula to return the row number, if the logical expression “Mango” is found, or else to return “False”.
The result of the above formula will be as below.
Here in this formula result, 2 and 4 are the relative position of “Mango” in the range. Now see the below two formulas which I’ve already mentioned above.
This formula returns the number 2, which is the first occurrence of item “Mango”
This formula returns the number 4, which is the second occurrence of item “Mango”
You can use this part to create Dynamic Lookup in Google Spreadsheet.