Select max value in a row and pick the column header

E

EK

A B C D Result
1 2 3 4 D
1 2 4 3 C
3 1 2 4 D
4 3 2 1 A

I would like to know the formula to get Result.
 
J

Jim May

How is it that we test using the row argument only (getting the maximum) and
Excel returns the column letter; and we give the column argument 0
(zero)..??? programming,, go figure...
 
J

Jim May

Been studying this more since posting, and I suppose the formula is of the
"array-type" Index() and according to help, if the 1st argument is a single
row or column, then row number (Max()) produces the array-element number in
the index (arg1);
Also, besides, the last 0 (zero) I originally took to refer to the column
argument, when in fact it is the 3rd part of the row argument (the
Match_Type)..
Sorry, for the "false-alarm"; I think I got it now.
 
D

Dave Peterson

The formula:

=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

Is return something from A1:D1 (the headers)

It's gonna find the largest value in A2:D2 with the max() function.

And look into A2:D2 to find what column in that range has the maximum (the 0
means it has to be an exact match).

=Match() will return a 1, 2, 3, 4, ... depending on where the match was found.
(1 for first element, 2 for second element, ....)

So =index(a1:d1,#) will return the value in the #th position of that range
(a1:d1).

======
If you plop some test data into a worksheet and then put the formula in another
cell, you can highlight each piece of the formula and hit f9 to see what it
evaluates to.

=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

select max(a2:d2) and hit f9
select match(----0) and hit f9
select all of it and hit f9.

=======
In later versions of excel (xl2002+), you can use
tools|formula auditing|evaluate formula
to do the same thing.
 
J

Jim May

Thanks for the confirming dialog Dave.

Dave Peterson said:
The formula:

=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

Is return something from A1:D1 (the headers)

It's gonna find the largest value in A2:D2 with the max() function.

And look into A2:D2 to find what column in that range has the maximum (the
0
means it has to be an exact match).

=Match() will return a 1, 2, 3, 4, ... depending on where the match was
found.
(1 for first element, 2 for second element, ....)

So =index(a1:d1,#) will return the value in the #th position of that range
(a1:d1).

======
If you plop some test data into a worksheet and then put the formula in
another
cell, you can highlight each piece of the formula and hit f9 to see what
it
evaluates to.

=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

select max(a2:d2) and hit f9
select match(----0) and hit f9
select all of it and hit f9.

=======
In later versions of excel (xl2002+), you can use
tools|formula auditing|evaluate formula
to do the same thing.
Click to expand...
 
D

Domenic

As per Excel's help file...

"If array contains only one row or column, the corresponding row_num or
column_num argument is optional."

So, in this case, since we only have one row, the row_num argument is
omitted and MATCH(MAX(...)) is used for the column_num argument.

Hope this helps!
 
Top