How to Rank Data by Alphabetical Order in Google Sheets

Published on

In a few scenarios, one may require to rank data by alphabetical order in Google Sheets. I’ve recently used this technique to sort a Pivot table by an nth text column.

To rank data by alphabetical order, we can use Match or Countif in Google Sheets.

It’s because the native RANK function doesn’t support ranking a text. After all, the ‘value’ parameter in it requires a number.

Syntax: RANK(value, data, [is_ascending])

So when you rank the alphabet “c” in the array {"a";"c";"b"}, you would get #VALUE! error instead of 3.

As I have mentioned, we can use either the Match or Countif solutions to rank a text in Google Sheets.

Both are capable of returning multiple ranks. Here are a few examples.

Example to Rank Data by Alphabetical Order in Google Sheets

First, we will try the formulas without referring to data in a cell range. Instead, we will use the array within.

Non-Working Formula:

=rank("c",{"a";"c";"b"})

Result: #VALUE!

Two Working Formulas:

=match("c",sort({"a";"c";"b"}),0)

Result: 3

=countif({"a";"c";"b"},"<"&"c")+1

Result: 3

Can you please explain the logic behind the above two formulas, please?

Yep! Let me start with MATCH.

The Match here matches “c” in the sorted array {"a";"b";"c"} and returns the relative position of the matching value, which is 3.

Syntax: MATCH(search_key, range, [search_type])

The last parameter, i.e., search_type, does matter. You should set it to 0 (zero). Otherwise, if there are duplicates, the formula would return the relative position of the last occurrence.

That’s the logic behind using Match to rank data by alphabetical order in Google Sheets.

What about COUNTIF?

Syntax: COUNTIF(range, criterion)

The comparison operators work with numbers as well as text strings in Countif

If we use =countif({"a";"c";"b"},"c"), it will return the count of “c”, which is 1.

If we use =countif({"a";"c";"b"},"<"&"c")+1 it will return the rank of “c” in the given array.

How to Replace Array with Cell Range Here?

Above, we have used an array within the formula to return the rank of a text in Google Sheets.

Can we replace it (data) with a cell range?

Yep! Please see to it.

Rank Data by Alphabetical Order in Google Sheets

I have replaced the array {"a";"c";"b"} with B2:B in both Match and Countif formulas.

You can follow the below method when you would like to find the rank of all the strings in the data by alphabetical order.

Array Formulas to Rank Data by Alphabetical Order in Google Sheets

I want to find the rank of all text values in a list. I can then use that output in a Pivot table for the purpose I have already mentioned in the first paragraph of this tutorial.

Sample Data: A1:F8

Column to Rank Data by Alphabetical Order: B (B2:B8 as B1 contains filed label).

Here is how to do it using a non-array formula first.

In cell G2, insert the below formula and drag to G3:G8.

=COUNTIF($B$2:$B,"<"&$B$2:B2)+1
Array Formula to Rank Text in Google Sheets

range: $B$2:$B (used absolute cell range so won’t change when drag-down)

criterion: "<"&$B$2:B2 (used absolute+relative reference so changes part of the reference when dragging down)

Check each cell in G2:G8 to understand it. I mean, how relative and absolute cell references work.

If you prefer to use Match, insert the below formula in cell G2 and copy-paste (drag) down.

=match(B2,sort($B$2:$B),0)

search key: B2 (relative)

range: sort($B$2:$B) – absolute.

Can we use array formulas to rank data by alphabetical order?

Yes! Empty G1:G. Insert ={"Helper";ArrayFormula(if(B2:B="",,COUNTIF(B2:B,"<"&B2:B)+1))} or ={"Helper";ifna(ArrayFormula(match(B2:B,sort(B2:B),0)))}in cell G1.

That’s all about how to rank data by alphabetical order in Google Sheets.

Thanks for the stay. Enjoy!

Related:-

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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.