Function Problems

How to Return the Top Three Names with Duplicate Values in Excel

Jason Miller Oct 21, 2026 868 views

Question details

The user needs an Excel formula to retrieve the names corresponding to the three highest values from a dataset.

How to Return the Top Three Names with Duplicate Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting the top ranking items from a list where multiple names might share the same value, requiring an accurate formula to pull unique names based on sorted values.
Observed behavior: The user requires a method to return the names for the top three values without repeatedly returning the same name for tied duplicate values.

Before you start

Ensure you are using a version of Excel or WPS Spreadsheet that supports modern dynamic array functions, as functions like SORT, SORTBY, TAKE, and CHOOSECOLS are required for this solution.

Solution 1Recommended

Use SORT, TAKE, and CHOOSECOLS Functions (Recommended)

This modern dynamic array approach sorts your data in descending order, isolates the top three rows, and extracts the corresponding names seamlessly.

By combining the SORT, TAKE, and CHOOSECOLS functions, you can process the source data in one fluid step. This prevents the traditional issue of VLOOKUP or INDEX/MATCH returning the first matched name repeatedly when dealing with duplicate values.

Select a destination cell

Click on a blank cell where you want the top three names to be displayed. Make sure there is enough empty space below it for the results to spill.

Input the dynamic formula

Assuming your source data has names in Column A and values in Column B (range A2:B20), type the formula: =CHOOSECOLS(TAKE(SORT(A2:B20, 2, -1), 3), 1).

Execute the formula

Press Enter. The formula will automatically sort the data by the values in descending order (-1), take the top 3 rows, and output only the 1st column (the names).

Use SORT, TAKE, and CHOOSECOLS Functions (Recommended)

Dynamic Spilling: Because this is a dynamic array formula, you only need to enter it into one cell. It will automatically spill the top three names into the adjacent cells below.

Solution 2

Use INDEX and SORTBY Functions

An alternative method using SORTBY and INDEX to achieve the exact same top-three extraction.

Advanced Data Analysis

Easily Manage Advanced Data Arrays in WPS Spreadsheet

WPS Spreadsheet fully supports the newest dynamic array formulas like SORT, TAKE, and CHOOSECOLS, allowing you to instantly extract top values and handle duplicates without complex workarounds.

1. Open your data file: Launch WPS Office and open your existing spreadsheet containing the dataset you want to analyze.
2. Select the output area: Click on a blank cell where the top three names will be displayed.
3. Apply the formula: Type your dynamic array formula (e.g., =CHOOSECOLS(TAKE(SORT(A2:B20, 2, -1), 3), 1)) and press Enter to instantly see the results.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Supports modern dynamic arrays to handle complex data queries instantly.Lightweight, fast, and free to use for everyday professional data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

A #NAME? error typically occurs if your spreadsheet software does not support modern dynamic array functions like CHOOSECOLS or TAKE. Ensure you are using an updated version of WPS Office or Microsoft 365.

How can I get the top 5 names instead of the top 3?

You can easily modify the formula to return more values by changing the row count parameter. For example, in the TAKE function, change the number 3 to a 5: =CHOOSECOLS(TAKE(SORT(A2:B20,2,-1),5),1).

What happens if there is a tie for the third place?

The SORT formula will return the first name it encounters in the source data that has the tied value. To handle ties differently (e.g., secondary sorting by alphabetical order), you can add a secondary sorting criteria inside the SORT or SORTBY function.

Excel & Data Problems