Rank values by month
And the formula in G10 is:
where client (B5:B17) date (C5:C17) and amount (D5:D17) are named ranges.
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:
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 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):
Only amounts in April survive and make it through IF; all other values are 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:
Working from the inside out, the MATCH function is configured to use Boolean logic like this:
The lookup value is 1, and the lookup array is constructed with this expression:
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:
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.