Using SORTN function it’s quite easy to find all lowest price items in Google Sheets. just look at the screenshot below.
When you have quotes from multiple vendors for different items, you may want to choose the lowest priced item from a list.
In Google Sheets, it’s possible to extract all the lowest priced items with a tiny formula.
Let me put that aside (of course I’ll come back to the original topic) and go to another example.
If your table has only the price of a single item, no need to go for the SORTN formula which we are going to discuss in detail in this tutorial.
You can simply Sort the column that containing the price in ascending order and limit the returned row to 1 with Array_Constrain.
Didn’t get? Then see the formula and screenshot.
=ARRAY_CONSTRAIN(sort(A2:C10,2,TRUE),1,3)
You can shorten the formula if you only want the price of the item and not require any other additional information like seller name. A MIN formula like below can do the job.
=min(B2:B10)
But our example is different. We have multiple items and their price from different sellers. So we can’t use any of the above-mentioned formulas to find all lowest price items in Google Sheets.
So as mentioned at the beginning of this tutorial, I’m going to use Google Sheets SORTN function.
The Formula to Find All Lowest Price Items in Google Sheets
To get the result as per our first screenshot, you should use the below formula.
=SORTN(SORT(A2:C,2,TRUE),ROWS(A2:C),2,ARRAY_CONSTRAIN(SORT(A2:C,2,TRUE),ROWS(A2:C),1),TRUE)
I’ve applied this formula in cell E2 on my sheet. There is one advantage of using this formula.
In a three column price list where the range is A2:C, you can use this formula as it is in any column other than column A, B, and C.
If you apply this formula in these columns it would cause a circular dependency error. The reason, our range is infinite (A2:C).
If you are so particular to use this formula in Column A, B or C, that below the above data range, you should first change the range in your formula. Here in this example, the range would be like A2: C10.
In the above para, I have mentioned about one advantage of using this formula to find all lowest price items in Google Sheets. Even though this formula returns an array result from an infinite range, there is one peculiarity with this formula. What’s that?
Unlike other array formulas where infinite ranges are applied, you can enter data below the formula result leaving one row after the result.
If it were any other array formulas in E2, the formula in E2 would have returned an #REF! error.
This’s one of the interesting features of SORTN that you can exploit in different ways. Saying that I’m taking you to the important part of this tutorial.
Master Formula Explanation
If you go through this explanation carefully, you don’t require to ask the question of how to find all lowest price items in Google Sheets again.
See the Master SORTN formula once again below and the syntax follows.
=SORTN(SORT(A2:C,2,TRUE),ROWS(A2:C),2,ARRAY_CONSTRAIN(SORT(A2:C,2,TRUE),ROWS(A2:C),1),TRUE)
SORTN Syntax.
SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])
First I’ll explain to you how this formula is functioning based on the syntax.
This time, please only pay your attention to the above syntax. I’ll explain the formula part after that.
Just like SORT function, SORTN function can also SORT any column or columns in a range. But there is one difference.
The SORTN can remove repeated items based on any single column. That column is called sort_column in the syntax and the instruction to remove duplicates is the display_ties_mode in the syntax.
Number 2 is the display ties mode to remove duplicates. Actually, we can use the formula as below.
Our master formula is an improvised version of this formula.
=SORTN(A2:C,10,2,A2:A,TRUE)
In this;
1. A2:C is the data range.
2. 10 is the number of rows to return in the result. Since we are unsure about this number, it’s better to put the total number of rows in our table here. That’s why I put 10.
3. 2 is the display tie mode for removing duplicates.
4. A2: A is the column to sort. Based on this column SORTN removes duplicates.
You can use this formula instead of our master formula on one condition. You should first manually sort the column 2 (price) in ascending order. Otherwise, this formula would return the wrong result.
Why?
Because SORTN removes second , third or any number of concurrences of all items based on the sort_column (Product). This sorting I’ve automated in my master formula.
Formula Arguments
First, pay your attention to the colouring. The highlighted part is the most important part of this formula which I’ve used to find all the lowest priced items in Google Sheets.
I’ll come to that later. First, let me explain to you other part of the formula.
[n] – This means the total number of rows in your price list. In my sample data, it’s 10 rows from A2: C10.
I can put there the number 10 directly. But instead, I’ve used the ROWS function. The Rows formula can return the total number of rows in my sheet including blank rows.
Our range is not A2: A10 in the formula (see the highlighted part). It’s infinite like A2:C. So you should use Rows formula as above instead of putting number 10.
[display_ties_mode] – Nothing concrete. You can use the number 2 as tier mode. It directs the formula to remove duplicate rows.
[is_ascending] – Put TRUE if you want your product names in ascending order, else FALSE. It’s not much important.
Now the two highlighted formulas are left. You may please spend some time to learn that too. So that, here after you can easily find all lowest price items in Google Sheets using SORTN, irrespective of the data range.
=SORT(A2:C,2,TRUE)
This formula used as range.
This formula sorts column 2 (Price) in our data in ascending order. So the lowest priced items will be on top.
=ARRAY_CONSTRAIN(SORT(A2:C,2,TRUE),ROWS(A2:C),1)
This formula used as sort_column
This formula also sorts column 2 (Price) in our data in ascending order. But the Array_Constrain formula limits the output to only column 1 (A2: A).
The ROWS formula in the last part indicates the total number of rows to return and the number 1 indicates the number of columns to return.
If any doubt please check my Array_Constrain tutorial. You can pick the functions that mentioned in this tutorial from my Function Guide section. That’s all.
