How to Return the Top Three Values per Category with an Excel Formula
Question details
The user wants to extract the three highest percentage values for every category using a single-cell dynamic array formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing grouped data to find the highest-performing metrics or top entries for multiple categories without using manual sorting or helper columns.
- Observed behavior
- Instead of filtering categories one by one, the user needs an automated formula that dynamically calculates and spills the top three ranked values alongside their sorted categories.
Ensure your spreadsheet software (such as Microsoft 365 or WPS Office) supports modern dynamic array functions like LET, SEQUENCE, FILTER, and UNIQUE, as these are required for this advanced calculation.
Use a Single-Cell LET and MAP Array Formula
This recommended solution uses a single, robust formula to automatically sort categories and list the first, second, and third highest values simultaneously.
This dynamic array formula uses the LET function to declare local variables, making the long formula easier to manage. It extracts unique categories, calculates the rows needed, and uses MAP combined with LAMBDA to filter and find the 'LARGE' values.
Click on the single blank cell where you want the final array of sorted categories and top values to spill.
Type or paste the following formula into the formula bar: =LET(srcNames,$A$2:$A$20,srcVals,$B$2:$B$20,u,UNIQUE(srcNames),n,ROWS(u),repNames,SORT(INDEX(u,MOD(SEQUENCE(n*3)-1,n)+1)),ranks,MOD(SEQUENCE(n*3)-1,3)+1,percs,MAP(repNames,ranks,LAMBDA(nm,k,LARGE(FILTER(srcVals,srcNames=nm),k))),HSTACK(repNames,percs))
Press Enter. The formula will automatically generate the categories and their respective top three values in adjacent columns.
Use a Two-Step Approach for Categories and Percentages
If you prefer separating the logic into two distinct columns rather than a single spilling array, you can use this two-formula method.
Master Dynamic Arrays with WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like LET, UNIQUE, FILTER, and SEQUENCE, allowing you to perform complex data analysis and extraction quickly without complicated workarounds.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your raw category and percentage data.
- 2. Input the array formula: Click an empty cell and paste the provided LET/MAP formula to extract the top three records.
- 3. Format your results: Highlight the output values, navigate to the Home tab, and apply Percentage or Number formatting to properly display your top metrics.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when there is existing data, text, or a space in the output area where the formula needs to place its results. You must clear the adjacent cells below and to the right so the array has enough room to expand.
Can I modify the formula to return the top 5 values instead of top 3?
Yes. In the single-cell LET formula, simply change the multiplier '3' in the segments SEQUENCE(n*3) and MOD(..., 3) to '5'. This adjusts the array logic to generate five ranks per category.
How does the MAP function work in this formula?
The MAP function takes the dynamically generated arrays for category names and ranks, and applies a custom LAMBDA function to each pair. It filters your dataset for the specific category name and uses the LARGE function to find the exact nth largest value corresponding to the rank.
Will these functions work in Excel 2016 or Excel 2019?
No. Functions like LET, UNIQUE, FILTER, and SEQUENCE are modern dynamic array functions. They require Microsoft 365, Excel 2021, or compatible modern alternatives like the latest version of WPS Office.
