Rank values by month

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.

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, it returns the top 3 clients in each of the three months.