Max and Min Strings Based on Alphabetic Order in Google Sheets

Published on

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.

StringsPositions Based on
Alphabetic Order
ACR0
PQR3
WXYZ6
LMN1
OPQ2
WX3154
WX3165
WXYZ6
WXYZ6

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)
Formula to Find Max and Min Strings in Google Sheets

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)

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. 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)

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.