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")+1Result: 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.
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
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:-
