Select max value in a row and pick the column header
 

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.

Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

Yes it does!

Thanks.

"Domenic" wrote:

Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

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...



"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

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.

"Jim May" wrote in message
news:5VLDe.80813$Fv.9465@lakeread01...
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...



"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

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.

Jim May wrote:

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...

"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

--

Dave Peterson

Thanks for the confirming dialog Dave.

"Dave Peterson" wrote in message
...
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.

Jim May wrote:

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...

"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

--

Dave Peterson

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!

In article <5VLDe.80813$Fv.9465@lakeread01, "Jim May"
wrote:

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...



"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.

Thanks Domenic, your comments clarify the Help "verbage".


"Domenic" wrote in message
...
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!

In article <5VLDe.80813$Fv.9465@lakeread01, "Jim May"
wrote:

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...



"Domenic" wrote in message
...
Try...

E2, copied down:

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

Hope this helps!

In article ,
"EK" wrote:

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.