Function Problems

Return the Header for the Maximum Value in Noncontiguous Excel Cells

Camila MilosovichCamila Milosovich Nov 12, 2026 870 views

Question details

Find the column header associated with the largest value among selected noncontiguous cells in a worksheet row.

Return the Header for the Maximum Value in Noncontiguous Excel Cells
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user needs to compare values in separated cells, such as C5, AG5, AP5, and AY5, and return the header above the cell containing the maximum value.
Observed behavior
A standard lookup range cannot directly match a list of noncontiguous cell references, so the formula must create a lookup array or search the full row.
Before you start

Confirm that the headers are in row 1 and the values being compared are in the same row, such as row 5. Check whether other numeric values between the selected cells could be larger, because this affects whether a full-row match is suitable.

Solution 1Recommended

Use CHOOSE to return the header for the highest noncontiguous value

Use this method when only specific separated cells should be included in the comparison, even if other numbers exist elsewhere in the row.

CHOOSE creates a temporary array containing only the cells you want to compare. MATCH locates the maximum value within that array, and INDEX returns the matching header from row 1.

1
Select the result cell

Click the cell where you want the matching column header to appear.

2
Enter the noncontiguous lookup formula

Type or paste =INDEX($1:$1,1,MATCH(MAX(C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5),CHOOSE({1,2,3,4,5,6,7,8,9},C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5),0)) and press Enter.

3
Replace the references for your worksheet

Edit C5, AG5, AP5, AY5, BH5, BQ5, BZ5, CI5, and CR5 in both parts of the formula so they match the cells you want to evaluate.

Use CHOOSE to return the header for the highest noncontiguous value
Duplicate maximum values: If two or more selected cells have the same maximum value, MATCH returns the header for the first matching cell listed in the CHOOSE portion of the formula.
WPS Spreadsheets formula tools

Return a Maximum-Value Header with WPS Spreadsheets

WPS Spreadsheets supports the same INDEX, MATCH, MAX, and CHOOSE functions needed to identify the header for the largest value across noncontiguous cells. Paste the formula into a result cell, adjust the references, and calculate the matching header immediately.

  1. 1. Open the workbook in WPS Spreadsheets: Open WPS Office, select Spreadsheets, and open the Excel workbook containing the headers and values.
  2. 2. Choose a result cell: Click an empty cell where the maximum value's header should be returned.
  3. 3. Enter the CHOOSE formula: Paste the INDEX, MATCH, MAX, and CHOOSE formula, then replace the sample cell references with your noncontiguous cells.
  4. 4. Confirm the formula: Press Enter and verify that the displayed result matches the header above the largest selected value.
Use INDEX, MATCH, MAX, and CHOOSE formulas for noncontiguous cell comparisons.Identify the column header associated with the highest selected value.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for quick formula editing and migration.
microsoft office alternative - wps office

Frequently Asked Questions

How can I return a column header based on the largest value in separated cells?

Use MAX to calculate the highest value, CHOOSE to create an array of the separated cells, MATCH to find its position, and INDEX($1:$1,...) to return the corresponding header from row 1.

Why should I use CHOOSE instead of searching the whole row?

CHOOSE limits the lookup to the exact noncontiguous cells you specify. Searching $5:$5 can return a different column if another matching value appears elsewhere in row 5.

What happens when two noncontiguous cells share the same maximum value?

MATCH with a match type of 0 returns the first matching position. With the CHOOSE formula, this is the first maximum-valued cell listed in the CHOOSE arguments.

Can I use this formula when my headers are not in row 1?

Yes. Replace $1:$1 in the INDEX function with the row containing your headers. For example, use $3:$3 when the headers are in row 3.

Does WPS Spreadsheets support the Excel functions used in this formula?

Yes. WPS Spreadsheets supports INDEX, MATCH, MAX, and CHOOSE, so you can use this method in compatible .xlsx and .xls files.