How to Rank with ordinal suffix in Excel

In this article, we will learn How to Rank with ordinal suffix in Excel.

Scenario:

When working with text or numbers, sometimes we come across a problem. When we need to add some suffix to with a given value. For example, adding st, nd, rd, th at the end of numbers or add prefix Mr, Mrs, Miss, Dr, Er with name values. For this kind of problem we have a solution with formula example and explanation stated below.

How to solve the problem?

For this, we will use the CHOOSE function in Excel. The function takes one argument as index value and rest are the values to choose from. The function extracts the value corresponding to the index number given. Given below is the formula syntax with key values.

Generic formula:

=CHOOSE(index , suffix_1, suffix 2, suffix 3,..)

All suffix values are given separately, not as array in argument.

suffix_1 : 1st suffix value

suffix_2 : 2nd suffix value

suffix_3 : 3rd suffix value and so on

 

Example :

All of these might be confusing to understand. Let's understand the function formula with a problem stated here. For this, we have a list of suffixes we can choose to add to verbs. Below is the formula to choose the suffix for the first verb. 

Use the formula:

=CHOOSE ( 1 , F3 , F4 , F5 , F6 , F7 , F8 , F9 , F10)

Explanation:

  • The function takes index value as 1 and all suffixes values using cell references, separated with commas,
  • The function returns the first suffix (index is 1) from the suffixes which is suffix in F3 cell

The formula returns the "ion" suffix in F3 cell. Now use index value as 2, 3, 4, 5 till 8 variably. Each different index value returns a value corresponding to the suffixes provided.

The formula works fine as it returns the suffixes. Now we will join the text values using the CONCATENATE function or & operator between value references as shown below.

 

Use the formula with CONCATENATE function:

In the above example, we used the formula to choose the suffix. But we can choose and join the 2 values using the CONCATENATE function. Or we can use the ($) operator to join values.

Use the formula:

=CONCATENATE ( B3, CHOOSE ( 1 , F3, F4, F5, F6, F7, F8, F9, F10)

As you can see in the above snapshot that you can add suffixes directly using the formula.

Add st, nd, rd and th suffix at the end of numbers :

Now we understand how to get 1st, 2nd, 3rd indices when the suffix is joined with a number value. Similarly we have a list of numbers and suffix values. See the below formula and snapshot. We will be using the similar formula explained above.

Use the formula:

=CHOOSE(B3,$F$3,$F$4,$F$5,$F$6,$F$7,$F$8,$F$9,$F$10,$F$11,$F$12)

Note: $ (char) with cell reference locks the cell, when copied to other cells.

Here number is the index argument as the suffixes are in the same order. Now you can copy the formula along the Suffix column.

As you can see in the above image that we obtained all the suffix values. Now either you can concatenate the two cells using & (char) or CONCATENATE function or you can also obtain the result using 1 formula as shown below.

Here are our Indices required. You can change suffix and prefix values using the similar formula. Edit prefix values like Mr, Miss, Mrs, Dr or ER with name values. Customize this formula and have fun with Excel. 

Here are all the observational notes regarding using the formula. 

Notes:

  1. If index argument is either negative or more than the length of the values, the function returns #VALUE! Error.
  2. The function returns #NAME? Error if the index value is non- numeric. 
  3. Values in the formula be separated by commas, not be given as array reference.
  4. CONCATENATE function and & (char) joins two values into one cell.

Hope you understood How to Rank with ordinal suffix in Excel. Explore more articles on Excel ranking values & Excel 2019 functions here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com.

 

Related Articles :

Sort Numeric Values with Excel RANK Function : sort numeric values with the help of ranking each number on the list in a separate column and then sorting it.

How to use the RANK function in Excel :  The Rank Function in Excel is used to rank a number within a list

Ranking a List of Numbers : Rank formula to return ranking of any number in the data. Mostly we use rank formulas to show the ranking of students, sales agents, etc.

Use INDEX and MATCH to Lookup Value : combination of INDEX & MATCH function formula to look up value in table as required in Excel.

Popular Articles:

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube