Excel Formulas to Rank the Values based on Month ~ Simple Tricks!!

For instance, we have a list of names with dates and amounts. Now we need to display the list of names and amounts, that are ranked by a numeric value. In this article, we will show the simple trick to rank the values based on the month in Excel. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

• If you want to rank the values based on month, use the below formula.

To Rank the Amount = =LARGE(IF(TEXT(date,”mmmm”)=G$4,amount),$F5)

To Rank the Names = =INDEX(name,MATCH(1,(amount=G5)*(TEXT(date,”mmmm”)=G$9),0))

Syntax Explanations:

• LARGE – In Excel, the LARGE Function will help to return the Nth Largest value from the given range of data.
• IF – This function will return one value for a TRUE result, and another for a FALSE result. Read more on the IF Function.
• TEXT – Text Function in Excel will help you to convert a number to text in the given format.
• INDEX – The INDEX function helps to return the value at a given position in a range or array
• MATCH – This function will locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
• Absolute Reference ($) – The Absolute Reference is an actual fixed location in a worksheet.
• Name – It represents the input names from your worksheet.
• Date – It represents the input dates from your worksheet.
• Amount – It represents the input amounts from your worksheet.
• Comma symbol (,) – It is a separator that helps to separate a list of values.
• Parenthesis () – The main purpose of this symbol is to group the elements.
• Multiplication (*) – In this symbol will multiply any two values or numbers.

Example:

Let’s consider the below example image.

• First, we will enter the input values in Column B to Column D.

• Now enter the first formula in cell G3.

• After that, Press CTRL + SHIFT + ENTER Keys.
• It will display the result as shown below.

• Now we are going to find the names for the specified amount.

• Enter the second formula in cell G8.

• After that, Press CTRL + SHIFT + ENTER Keys.
• It will display the result as shown below.

Verdict:

Hope you like this article on how to rank the values based on the month in Excel. Please share your query below in the comment box. We will assist you. Thank you so much for visiting our site!! Click here to know more about Geek Excel *and Excel Formulas *!!

Related Article:

• Excel Formulas to Highlight the Values Not Between X and Y!!
• Excel Formulas to Highlight the Integer Values Only ~ A Simple Tutorial!!