Exceljet

Quick, clean, and to the point

Rank values by month

Excel formula: Rank values by month
Summary 

To display a list of names, ranked by a numeric value, you can use a set of formulas based on LARGE, INDEX, MATCH, with help from the TEXT function. In the example shown, the formula in G5 is:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

And the formula in G10 is:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

where client (B5:B17) date (C5:C17) and amount (C5:C17) are named ranges.

Note: these are array formulas and must be entered with control + shift + enter, except in Excel 365.

Explanation 

This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.

Note there is no actual rank in the source data. Instead, we are using the LARGE function to work directly with amounts. Another approach would be to add rank to the source data with the RANK function, and use the rank value to retrieve client names.

Part 1: retrieve top 3 amounts each month

To retrieve the top 3 amounts for each week, the formula in G5 is:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

Working from the inside out, we first use the TEXT function to get month names for each date in the named range date:

TEXT(date,"mmmm") // get month names

The custom number format "mmmm" will return a string like "April", "May", "June" for each name in the named range date. The result is an array of month names like this:

{"April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June"}

The TEXT function delivers this array to the IF function, which is configured to filter dates on a given month by testing the month name against the value in G4 (a mixed reference, so the formula can be copied down and across):

IF(TEXT(date,"mmmm")=G$4,amount) // filter on month

Only amounts in April survive and make it through IF; all other values are FALSE:

{10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Finally, the LARGE function uses the value in F5 (also a mixed reference) to return the "nth" largest value that remains. In cell G5, LARGE returns 18,500, the "1st" largest value. As the formula is copied down and across the table, the LARGE function returns the top 3 amounts in each of the three months.

Now that we know the top 3 values in each month, we can use this information like a "key" to retrieve the client name for each.

Part 2: retrieve client names

Note: This is an example of using INDEX and MATCH with multiple criteria. If this concept is new to you, here is a basic example.

Formula to retrieve names based on rank

To retrieve the name associated with the top three values in G5:I7, we use INDEX and MATCH:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

Working from the inside out, the MATCH function is configured to use Boolean logic like this:

MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)

The lookup value is 1, and the lookup array is constructed with this expression:

(amount=G5)*(TEXT(date,"mmmm")=G$9)

The expression that creates the lookup array uses Boolean logic to "filter out" amounts that are (1) not in April, and (2) not the value in G5 (18,500). The result is an array of 1s and 0s like this:

{0;0;1;0;0;0;0;0;0;0;0;0;0}

With a lookup value of 1 and zero for match type (to force an exact match) MATCH returns 3 directly to the INDEX function:

=INDEX(client,3) // returns "Janus"

INDEX returns the third value in the named range client, "Janus".

As the formula is copied down and across the table, the it returns the top 3 clients in each of the three months.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.