How to Rank Data by Alphabetical Order in Google Sheets

GiGiKing·March 27, 2024·0 comments

In a few scenarios, you may find yourself needing to rank data by alphabetical order in Google Sheets. Whether it’s for sorting a Pivot table or organizing your data, this technique is incredibly useful. However, the native RANK function in Google Sheets doesn’t directly support ranking text values. Fear not! I’ll guide you through two alternative solutions using the MATCH and COUNTIF functions.

Rank Data Using the MATCH Function

Let’s start with the MATCH function. This formula matches a specified value in an array and returns its relative position. In this case, we’re matching a text value in a sorted array to determine its rank.

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

To rank data by alphabetical order using the MATCH function, follow these steps:

Sort the array in ascending order: {"a";"c";"b"}
Use the MATCH function: =MATCH("c", SORT({"a";"c";"b"}), 0)
The formula will return the relative position of the matching value, which is 3.

Make sure to set the search_type parameter to 0 to avoid potential issues with duplicate values.

Rank Data Using the COUNTIF Function

The COUNTIF function counts the number of occurrences of a specific value in a given range. In our case, we’ll use it to determine the rank of a text value in the array.

Syntax: COUNTIF(range, criterion)

To rank data by alphabetical order using the COUNTIF function, follow these steps:

Use the COUNTIF function: =COUNTIF({"a";"c";"b"},"<"&"c")+1
The formula counts the number of values that are less than "c" in the array and adds 1 to obtain the rank.

The comparison operator (<) works with both numbers and text strings in the COUNTIF function.

Replacing the Array with a Cell Range

In the examples above, we used an array directly within the formula. However, you can replace the array with a cell range for more dynamic calculations.

First, select the cell range you want to use as the data. For example, B2:B represents the range B2 to the last cell in column B.

Then, modify the formulas as follows:

MATCH function: =MATCH(B2, SORT($B$2:$B), 0)
COUNTIF function: =COUNTIF($B$2:$B,"<"&B2)+1

By using a cell range, your formulas will update automatically when you add or remove data from the range.

Array Formulas to Rank Data by Alphabetical Order

If you want to find the rank of all text values in a list, you can use array formulas. These formulas provide a more efficient way to handle large datasets.

To use array formulas, follow these steps:

Using a non-array formula, insert the following formula in cell G2 and drag it down to G3:G8: =COUNTIF($B$2:$B,"<"&$B$2:B2)+1
Alternatively, you can use the MATCH function with an array formula: =MATCH(B2, SORT($B$2:$B), 0)

By using array formulas, you’ll get all the ranks in one go, saving you time and effort.

Conclusion

You’ve learned two different methods to rank data by alphabetical order in Google Sheets. Whether you choose to use the MATCH or COUNTIF function, both options provide reliable and efficient solutions. Remember to adjust the formulas to match your specific data range. Now go forth and organize your data with ease!

Thanks for staying with us. Enjoy the newfound knowledge!