Find column header through max value in table

Question

I'm trying to find a solution for the following:

I want to have the column header in C13 for the max value of a certain nr. In this regard 2008 has as max value 126 and should give "Material".

Whenever I use the MAXIFS formula it gives #VALUE error.

Any suggestions?

Answer 1

MAXIFS returns #VALUE! when the ranges used do not have the same size and shape. To get the header of the maximum value in the row for NR 2008, use a combination of INDEX/MATCH (or XLOOKUP) instead of MAXIFS.

Assuming this layout:

• NR in A2:A10
• Headers (IT, Material, Mechanical, …) in B1:E1
• Data in B2:E10
• The NR to search (2008) in A13

Use this formula in C13:

=INDEX($B$1:$E$1,
       MATCH(MAX(INDEX($B$2:$E$10,MATCH($A13,$A$2:$A$10,0),0)),
             INDEX($B$2:$E$10,MATCH($A13,$A$2:$A$10,0),0),
             0))

Explanation (non-array in modern Excel):

1. MATCH($A13,$A$2:$A$10,0) finds the row where NR = 2008.
2. INDEX($B$2:$E$10, that_row, 0) returns the entire row of values for 2008.
3. MAX(...) gets the maximum value in that row.
4. The outer MATCH finds the position of that max within the row.
5. INDEX($B$1:$E$1, ...) returns the corresponding column header (e.g., "Material").

If MAXIFS is used anywhere in this scenario, ensure that max_range and each criteria_rangeN are exactly the same size and shape; otherwise Excel will return #VALUE!.

---

References:

• MAXIFS function
• Guidelines and examples of array formulas

Answer 2

Hi @Rolf Gerrits,

In cell C13 you can try the below formula:

=LET(f, FILTER(B2:D10, A2:A10 = A13), FILTER(B1:D1, f = MAX(f)))

HTH

IlirU

Answer 3

Hi,

In cell C13, enter this formula

=LET(a,XLOOKUP(A13,A2:A10,B2:D10),INDEX(B1:D1,1,XMATCH(MAX(a),a)))

Hope this helps.