Returning a column name or number

F

Frank

After I find the max value in a row of numbers , how can I return the column
letter or column name in the next cell?
Thank You
 
S

Sandy Mann

With the Row of numbers in - say - C7:O7 then try:

=CHAR(63+COLUMN(C7)+MATCH(MAX(C7:O7),C7:O7,FALSE))

Note: if there is more than one MAX() value this will return the letter of
the column of the first one.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
With the Row of numbers in - say - C7:O7 then try:

=CHAR(63+COLUMN(C7)+MATCH(MAX(C7:O7),C7:O7,FALSE))

Note: if there is more than one MAX() value this will return the letter of
the column of the first one.
Click to expand...
....

Doesn't work so well for ranges that extend to the right of column Z.

The ADDRESS function would make more sense here.

=SUBSTITUTE(ADDRESS(1,MIN(COLUMN(rng))-1+MATCH(MAX(rng),rng,0),4),"1","")

and, FWIW, this might work better in versions for languages that don't use
the Latin alphabet.
 
F

Frank

--
Frank


Sandy Mann said:
With the Row of numbers in - say - C7:O7 then try:

=CHAR(63+COLUMN(C7)+MATCH(MAX(C7:O7),C7:O7,FALSE))

Note: if there is more than one MAX() value this will return the letter of
the column of the first one.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk




Sandy,
Click to expand...
Please excuse my lack of understanding of C7:07
Assuming that in Row 2 I have values of 2,4,6,8 respectively in Columns
B,C,D,E and the Columns are labeled Able, Baker, Charlie, Dog and I use the
MAX function in the cell at Row 2 Column F to determine the highest value in
the row (8) what algorithm can I use to show in the next cell that the
highest value is in Column D or, better yet, under the name Dog
Thank You
 
T

T. Valko

Frank said:
--
Frank



Please excuse my lack of understanding of C7:07
Assuming that in Row 2 I have values of 2,4,6,8 respectively in Columns
B,C,D,E and the Columns are labeled Able, Baker, Charlie, Dog and I use
the
MAX function in the cell at Row 2 Column F to determine the highest value
in
the row (8) what algorithm can I use to show in the next cell that the
highest value is in Column D or, better yet, under the name Dog
Thank You
Click to expand...


Column headers in the range B1:E1
Numeric values in the range B2:E2

=INDEX(B1:E1,MATCH(MAX(B2:E2),B2:E2,0))

Biff
 
Top