Function Problems

How to Return the Header for the Highest Average or Total in Excel

Kushani Nimanthika Oct 8, 2026 868 views

Question details

The user wants to use a formula to return the text from the top row (header) of a column that contains the highest calculated average or total within a given range.

How to Return the Column Header for the Highest Average in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing multiple columns of numeric data to dynamically identify and label the top-performing category.
Observed behavior: The user needs a dynamic formula that continuously displays the correct column header from row 1 associated with the highest column average or total, updating instantly when source numbers change.

Before you start

Ensure your dataset is organized with clear text headers in the first row and purely numerical data in the rows beneath, and remove any merged cells that might disrupt the column arrays.

Solution 1Recommended

Use an Advanced One-Step Formula (INDEX, LET, and BYCOL)

Best for modern spreadsheet versions, this calculates the totals or averages dynamically in memory without requiring an extra helper row.

This approach utilizes dynamic array functions. It calculates the sum or average for each column, identifies the maximum value, and retrieves the corresponding header—all within a single cell.

Select the Target Cell

Click on the cell where you want the highest average's column header to be displayed (e.g., cell B21).

Enter the Dynamic Array Formula

Type the formula: =INDEX(A1:J1,LET(b,BYCOL(A2:J16,LAMBDA(a,AVERAGE(a))),MATCH(MAX(b),b,0)))

Apply and Verify

Press Enter. The cell will now automatically calculate the averages for columns A through J, find the maximum, and output the text from row 1 of that specific column.

Use an Advanced One-Step Formula (INDEX, LET, and BYCOL)

Changing Average to Sum: The formula above uses AVERAGE. If you need to find the column with the highest total instead, replace the AVERAGE function inside the LAMBDA with SUM.

Solution 2

Use a Helper Row with INDEX and MATCH

A traditional, highly compatible method that references a row where your totals or averages are already calculated.

Solution 3

Use a Helper Row with XLOOKUP

A simplified, modern alternative to INDEX/MATCH when referencing existing calculated data.

Advanced Data Analysis

Perform Complex Data Analysis Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, fully supporting advanced lookup functions and dynamic arrays to help you find maximum averages and retrieve column headers with ease.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data range.
2. Select the Target Cell: Click on the cell where you want the highest average's column header to be displayed.
3. Input the Formula: Type the provided INDEX/MATCH or dynamic array formula and press Enter.
4. Update Dynamically: Modify your source data; WPS Spreadsheet will instantly recalculate and update the header automatically.

Built-in support for advanced functions like XLOOKUP, INDEX, MATCH, and dynamic arrays.100% format compatibility with Microsoft Excel (.xlsx) files.Lightweight and completely free for efficient data management.Familiar interface ensures a seamless transition for all your spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

How do I return the header for the lowest average instead?

You can use the exact same formulas but replace the MAX() function with the MIN() function. This will prompt the formula to look for the smallest value in the calculated arrays or helper rows.

Why is my formula returning an #N/A error?

This usually happens if the lookup value isn't found in the lookup range, or if your header range (e.g., A1:J1) and data range (e.g., A17:J17) are not the same width. Ensure the number of columns in both ranges match exactly.

Do I have to use a helper row to find the maximum value?

No. By using the dynamic array formula with LET and BYCOL (as shown in the recommended solution), you can calculate the column totals in memory without taking up extra rows in your worksheet.

Does XLOOKUP work in older versions of Excel?

XLOOKUP is only available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version like Excel 2016, you must use the INDEX and MATCH combination instead.

Excel & Data Problems