This post is all about the MIN() and MAX() equivalent formulas for strings in Google Sheets.
We can use the functions QUERY, SORTN, or a combo formula to find max and min strings based on alphabetic order in Google Sheets.
We will learn all of them. That will help us pick the best formula for finding the maximal and minimal strings in Google Sheets.
The below table contains a few strings and their corresponding value w.r.t. their position based on alphabetic order from lowest to highest.
Strings | Positions Based on Alphabetic Order |
ACR | 0 |
PQR | 3 |
WXYZ | 6 |
LMN | 1 |
OPQ | 2 |
WX315 | 4 |
WX316 | 5 |
WXYZ | 6 |
WXYZ | 6 |
If you check column 2, you can find that the max value is 6, and the min value is 0.
The corresponding strings in the first column are “WXYZ” and “ACR,” respectively.
Positions Based on Alphabetic Order – What does it mean?
If you SORT the strings in the first column, they will be arranged based on the values in column 2.
In A-Z sort, the values will be arranged in the order “ACR,” “LMN,” “OPQ,” and so on.
=sort(A2:A10)
In Z-A sort, the values will be arranged in the order “WXYZ,” “WXYZ,” “WXYZ,” “WX316,” and so on.
=sort(A2:A10,1,0)
We can use COUNTIF to get the string position in column B.
I have used the below array formula in B2.
=ArrayFormula(COUNTIF(A2:A10,"<"&A2:A10))
Formulas to Find Max and Min Strings Based on Alphabetic Order in Google Sheets
The real question is how to find the max and min strings in Google Sheets.
As I have mentioned, I have a few good options in front of me.
Before going to the simplest one, let’s make use of the numbers in column B. I’ll use them in a VLOOKUP formula.
Option 1 – Vlookup
Vlookup Syntax:- VLOOKUP(search_key, range, index, [is_sorted])
Search_Key
Find the max/min value in the range B2:B10 using the MAX/MIN functions. Then use them as the search keys in two Vlookup formulas.
Range
The range will be {B2:B10,A2:A10}
, a virtual array.
Index
The index (output column) will be 2.
Max String Formula:
=vlookup(max(B2:B10),{B2:B10,A2:A10},2,0)
Min String Formula:
=vlookup(MIN(B2:B10),{B2:B10,A2:A10},2,0)
In these two formulas, feel free to replace B2:B10 with the corresponding COUNTIF formula. So we won’t need to use any helper range.
Option 2 – Maximal and Minimal Strings Using QUERY Function in Google Sheets
No doubt, QUERY is one of the functions that make us addicted to Google Sheets.
It helps us perform various data manipulations.
The QUERY Max/Min functions work with non-numeric columns.
If you are one of my regular readers, you may have already experienced that here – How to Aggregate Strings Using Query in Google Sheets.
We can query column A as below to return the maximal string in Google Sheets.
=query(A2:A10,"Select max(A) label max(A)''")
Regarding minimal string, the following Query will return that.
=query(A2:A10,"Select min(A) label min(A)''")
You May Like:- How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
Option 3 – The Best Formula to Find Max/Min String in Google Sheets
We are Google Sheets users. So let’s think differently.
In the beginning, I have used two SORT formulas to sort the strings in column A in ascending and descending order.
If you extract the first values in both those formula results, we can meet our requirements. But SORT doesn’t offer that.
We can either use Array_Constrain or Index with SORT or use another similar function.
There is a dedicated function in Google Sheets. It can not only sort a range but also limit the output to ‘n’ rows.
Yep! SORTN. That’s the function I am going to use.
MAX String:
=sortn(A2:A20,1,0,1,0)
MIN String:
=sortn(A2:A20)
The above two are the best way to find max and min strings based on alphabetic order in Google Sheets.
That’s all about MIN/MAX equivalent formulas for strings in Google Sheets. Thanks for the stay. Enjoy!
Resources (Unique Tutorials)
- Get Min Date Ignoring Blanks in Each Row in Google Sheets.
- How to Get Max Date in Each Row in Google Sheets [Array Formula].
- Column Header of Max Value in Google Sheets Using Array Formula.
- How to Retrieve Column Header of Min Value in Google Sheets.
- Hyperlink Max and Min Values in Column or Row in Google Sheets.
- Row-Wise MIN Using DMIN in Google Sheets.
- Return First and Second Highest Values in Each Row in Google Sheets.
Hi,
In my case, the correct “separated value” for the SORTN function was the semicolon, not the comma.
So
=SORTN(A2:A20;1;0;1;0)
instead of
=SORTN(A2:A20,1,0,1,0)
Hi, Saverio,
You are right. It’s because of the LOCALE settings. More info here – How to Change a Non-Regional Google Sheets Formula.
Hi Prashanth, I have sent you an email with the Sheet’s requirement for the analysis formula.
Hi, Ank,
I have responded via return mail. Please check your inbox.