Function Problems

How to Display All Tied Top Results in Excel

WPS Content Manager Oct 1, 2026 869 views

Question details

The user needs to find a way to extract and display all tied top-scoring items or a top-N list in Excel without missing duplicates.

Product: Excel
Device & OS: not provided
Scenario: Creating an automatically populated top-ten list or finding the highest-scoring items from a dataset where multiple items share the exact same top score.
Observed behavior: Standard formulas using MATCH only return the first matching result, ignoring subsequent items that share the highest score.

Before you start

Ensure you are using a version of Excel (such as Microsoft 365 or Office 2021) that supports Dynamic Array formulas, as functions like FILTER, SORTBY, and TAKE are required to process multiple matches simultaneously.

Solution 1Recommended

Use Dynamic Array Formulas (SORTBY and TAKE) to Extract a Top List

Utilize modern Excel dynamic array functions to sort data by score and extract the top N results, naturally including tied scores if handled properly.

Traditional lookup functions stop at the first match. Dynamic arrays can sort the entire dataset by value and return the specified number of top items instantly.

Organize your source data

Ensure your items (e.g., flavor names) are located in one continuous range like C2:Z2, and their corresponding scores are located directly below them in C4:Z4.

Apply SORTBY and TAKE functions

Select the target cell where you want the top results to begin. Enter the formula =TAKE(SORTBY(Sheet1!C2:Z2, Sheet1!C4:Z4, -1), , 5). This sorts the items in descending order based on the scores and extracts the top 5.

Transpose for vertical layout

If you want the top list to populate vertically in columns rather than horizontally, wrap your formula in the TRANSPOSE function: =TRANSPOSE(TAKE(SORTBY(Sheet1!C2:Z2, Sheet1!C4:Z4, -1), , 5)) and press Enter.

Use Dynamic Array Formulas (SORTBY and TAKE) to Extract a Top List

Dynamic Spilling: The results will automatically spill into adjacent cells. Make sure the output area is empty to avoid a #SPILL! error.

Solution 2

Combine TEXTJOIN and FILTER for Exact Tied Maximums

Extract all items that share the exact same top score and output them into a single cell, separated by commas.

Easily Manage Array Formulas with WPS Spreadsheet

WPS Office provides robust support for modern dynamic array formulas, allowing you to easily filter, sort, and display top tied results without needing complex legacy workarounds.

1. Open your file in WPS: Launch WPS Spreadsheet and open your dataset containing the scores and items.
2. Select the destination: Click on the cell where you want your top-tier list to begin spilling.
3. Enter the formula: Input your dynamic array formula, such as =TRANSPOSE(TAKE(SORTBY(...))), into the formula bar.
4. View spilled results: Press Enter to instantly generate the top-N list displaying all tied matching items correctly.

Fully compatible with Microsoft Excel formulas (.xlsx)Natively supports dynamic array functions like FILTER, SORT, and UNIQUEFree, lightweight, and fast alternative for data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP or MATCH only return one result for tied scores?

Traditional lookup functions like VLOOKUP, HLOOKUP, and MATCH are designed to return the first exact match they encounter in an array. They search sequentially and stop searching immediately once the first match is found, ignoring any subsequent identical values.

What if I have an older version of Excel that doesn't support FILTER or SORTBY?

In older versions like Excel 2016 or 2019, you must use complex array formulas combining INDEX, SMALL, IF, and ROW functions. These must be entered using Ctrl+Shift+Enter. Upgrading to a newer software or using WPS Office Free provides an easier way to access dynamic arrays.

Can I return the top 3 results instead of the top 5?

Yes. When using the TAKE function within your formula (e.g., TAKE(array, rows, columns)), simply change the rows/columns argument from 5 to 3. For example: =TAKE(SORTBY(C2:Z2, C4:Z4, -1), , 3).

Excel & Data Problems