Function Problems

How to Return the Three Highest Unique Values and Headers in Excel

Khadija Khan Oct 13, 2026 869 views

Question details

The user needs to find the highest, second-highest, and third-highest unique values in a dataset and return their corresponding column headers, effectively removing duplicate numbers before ranking.

Extract the Top Three Highest Unique Values and Headers in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing complex data analysis where top-ranking metrics must be extracted alongside their category names, without duplicate values skewing the top three results.
Observed behavior: Standard lookup and ranking functions fail to filter out duplicate values automatically and struggle to return matching headers when ties or duplicated top values occur.

Before you start

Ensure your spreadsheet software is updated to a version that supports modern dynamic array functions, such as UNIQUE, FILTER, LET, and LAMBDA, as these are required for handling unique rankings efficiently.

Solution 1Recommended

Use Dynamic Array Formulas to Return Values and Headers

This solution uses a combination of advanced functions (LET, LARGE, UNIQUE, FILTER) to dynamically extract the top unique values and map them to their corresponding headers, even if they are in horizontal rows.

By defining variables with the LET function, you can combine UNIQUE and LARGE to find the exact top values, and then use FILTER and REDUCE to pull the correct headers. This method perfectly handles tied values across your dataset.

Define your named ranges

For ease of use, select your header row (e.g., BG2:BY2) and name the range 'headers'. Select your data row (e.g., BG3:BY3) and name it 'numbers'.

Select an empty output cell

Click on a blank cell with enough empty space below and to the right for the results to spill over.

Enter the advanced LET formula

Type the following formula: =LET(top,LARGE(UNIQUE(numbers,TRUE),{1,2,3}),arr,IFERROR(DROP(REDUCE("",top,LAMBDA(a,v,VSTACK(a,FILTER(headers,numbers=v)))),1),""),HSTACK(TRANSPOSE(top),TAKE(arr,,1)))

Execute the formula

Press Enter. The formula will spill to show the top three unique numeric values in one column and their corresponding headers in the adjacent column.

Use Dynamic Array Formulas to Return Values and Headers

Handling Ties: If multiple headers share the same top value, the FILTER function inside the formula will appropriately pull all tied headers.

Solution 2

Extract Top Unique Values Only (Simple Vertical List)

If you only need the highest unique numeric values without the corresponding headers, you can use a simpler combination of LARGE and UNIQUE on a vertical dataset.

Advanced Spreadsheet Formulas

Easily Manage Complex Data Analysis with WPS Spreadsheet

WPS Spreadsheet provides robust support for modern array functions, allowing you to easily extract unique values, rank datasets, and filter results without lag.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data and headers.
2. Locate the Target Cell: Click on the cell where you want your top three records to be displayed.
3. Input the Formula: Type or paste your combined UNIQUE, LARGE, and FILTER formula into the formula bar.
4. Calculate Results: Press Enter to instantly execute the dynamic array and display your top unique values and corresponding headers.

Fully compatible with Microsoft Excel formulas, ensuring your UNIQUE and LARGE functions work seamlessly.Supports dynamic arrays and advanced logic functions for complex data extraction.Lightweight and optimized for speed, even when parsing large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the LARGE function return duplicate values?

The LARGE function simply orders all numbers in a range from highest to lowest. If the number 100 appears twice, LARGE will return 100 for both the 1st and 2nd positions. Wrapping your range in the UNIQUE function removes these duplicates before LARGE evaluates them.

Can I return multiple headers if there is a tie for the highest value?

Yes. By utilizing the FILTER function matched with the top unique values generated by LARGE, you can dynamically extract all headers that share the exact same top numeric value.

What if my software version doesn't support dynamic arrays?

In older software versions without UNIQUE or FILTER, you must use a complex array formula (entered with Ctrl+Shift+Enter) combining IF, MATCH, COUNTIF, and INDEX to manually skip over duplicated ranks, which can be difficult to maintain.

Excel & Data Problems