Return Unique Ranked Results with Excel INDEX and MATCH
Question details
The user needs to extract top-ranked results without outputting duplicate rows when the ranking dataset contains identical values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Ranking and extracting tabular data where source values may contain duplicates (ties), requiring a dynamically sorted top-N list.
- Observed behavior
- Standard INDEX and MATCH formulas only return the first matching row, causing repeated outputs for duplicate tied values instead of listing the unique tied entries.
Ensure your version of Excel or WPS Spreadsheet supports modern dynamic array functions like SORT and SEQUENCE, as these are required to bypass the limitations of traditional MATCH formulas.
Use Dynamic Array Functions (INDEX, SORT, and SEQUENCE)
Replace traditional INDEX and MATCH with dynamic array functions to automatically sort and extract top unique results, bypassing the duplicate match limitation entirely.
A standard INDEX and MATCH formula stops at the first match it finds. By nesting SORT and SEQUENCE inside the INDEX function, you can dynamically sort your dataset and extract the exact number of top unique records you need without repetition.
Determine the range containing your data. For example, B13:C207, where column B contains the names or categories, and column C contains the numeric values you wish to rank.
In your destination cell, enter the formula: =INDEX(SORT(B13:C207,2,-1),SEQUENCE(5),SEQUENCE(,2))
Modify the range B13:C207 to match your actual data. The '2' in the SORT function indicates sorting by the 2nd column, and '-1' means descending order. Change SEQUENCE(5) to dictate how many top rows you want (e.g., SEQUENCE(10) for the top 10).
If duplicate values require a specific tie-breaker, add a secondary sort column to your dataset and update the SORT function to evaluate multiple columns.
Effortlessly Extract Ranked Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like SORT, SEQUENCE, and INDEX. You can seamlessly extract unique ranked data and properly handle duplicate matches with robust performance and full formula compatibility.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the ranking data.
- 2. Select the destination cell: Click on the empty cell where you want the top-ranked list to begin spilling.
- 3. Input the dynamic INDEX SORT formula: Type =INDEX(SORT(DataRange, 2, -1), SEQUENCE(5), SEQUENCE(,2)) and press Enter.
- 4. Review the unique results: WPS Spreadsheet will instantly calculate and spill the top 5 unique ranked results into the adjacent cells, ignoring standard MATCH duplicate limitations.
Frequently Asked Questions
Why does INDEX and MATCH return the same result for duplicates?
The standard MATCH function searches from top to bottom and returns the relative position of the very first matching value it finds. If there are duplicates in the array, it will never proceed to subsequent matches, causing INDEX to retrieve the exact same record multiple times.
What should I do if I get a #NAME? error with this formula?
A #NAME? error typically means your spreadsheet software version does not support modern dynamic array functions like SORT or SEQUENCE. You will need to upgrade to a newer version, such as Microsoft 365, Excel 2021, or the latest version of WPS Office.
How do I change the formula to return the bottom 5 results instead of the top 5?
To return the bottom results (ascending order), change the sort order argument in the SORT function from -1 to 1. The modified formula will look like: =INDEX(SORT(B13:C207,2,1),SEQUENCE(5),SEQUENCE(,2)).
Can I use older spreadsheet versions to get unique ranked results with duplicates?
Yes, but it requires a workaround using helper columns. You would typically add a tiny mathematical fraction (like +ROW()/100000) to your ranking values in a helper column to make every duplicate value mathematically unique, then run standard INDEX, MATCH, and LARGE formulas against that specific helper column.
