Return the Header for the Maximum Value in Noncontiguous Excel Cells
Question details
Find the column header associated with the largest value among selected noncontiguous cells in a worksheet row.

- 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.
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.
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.
Click the cell where you want the matching column header to appear.
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.
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.

Match the maximum value against the entire row
Use this shorter formula only when no unrelated numeric values in the row could be selected instead.
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. Open the workbook in WPS Spreadsheets: Open WPS Office, select Spreadsheets, and open the Excel workbook containing the headers and values.
- 2. Choose a result cell: Click an empty cell where the maximum value's header should be returned.
- 3. Enter the CHOOSE formula: Paste the INDEX, MATCH, MAX, and CHOOSE formula, then replace the sample cell references with your noncontiguous cells.
- 4. Confirm the formula: Press Enter and verify that the displayed result matches the header above the largest selected value.

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.




