And the formula in G10 is: where client (B5:B17) date (C5:C17) and amount (D5:D17) are named ranges. Note: these are array formulas and must be entered with control + shift + enter, except in Excel 365. 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: 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: 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: 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: 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: 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
