cell in row has highest value and rtrns col hdr

[Michael]

hi,
can anyone advise as to which formula would be able to find the highest
("=MAX(A$:T$)")value in a row of cells and once identified return the column
header? thanks for any help!

 

[Dave Peterson]

=index(1:1,match(max(2:2),2:2,0))

Will find the first largest value in row 2 and return the header from row 1.

 

[Guest]

hi,
can anyone advise as to which formula would be able to find the highest
("=MAX(A$:T$)")value in a row of cells and once identified return the column
header? thanks for any help!

Michael

=INDIRECT(ADDRESS(1,MATCH(MAX(A2:T2),A2:T2,0)))&",
"&INDIRECT(ADDRESS(1,MATCH(MAX(A2:T2),A2:T2,1)))

gives you up to two headers, assuming that a number may be duplicated. MAke
sure the whole formula is pasted in one line in U2 and copy it down

Regards
Peter

 

[Guest]

On second thoughts it doesn't work try

=INDIRECT(ADDRESS(1,MATCH(MAX(A4:F4),A4:F4,0)))

which will just give you the first header if the max is duplicated.

Peter

 

[Guest]

Ah well, at last one for two maxs in row if you need it

=IF(COUNTIF(A4:t4,MAX(A4:t4))>1,INDIRECT(ADDRESS(1,MATCH(MAX(A6:t6),A6:t6,0)))&",
"&INDIRECT(ADDRESS(1,MATCH(MAX(A6:t6),A6:t6,1))),INDIRECT(ADDRESS(1,MATCH(MAX(A5:t5),A5:Ft,0))))

paste to u 4 and copy

Peter

 

[Michael]

Dave,
Thank you very much for your response. If there is more than 1 cell that
has is equal to the "highest" value, will this also display the subsequent
col. hdrs?

 

[Dave Peterson]

Nope. It just displays the first match.

Dave,
Thank you very much for your response. If there is more than 1 cell that
has is equal to the "highest" value, will this also display the subsequent
col. hdrs?

 

[Michael]

Dave,
thanks again for your attention and response to what is a lack of my ability
to figure this out. Can you offer a solution for this?

 

[Dave Peterson]

Maybe you can use one of the lookup formulas on Chip Pearson's page.

I'd look at the arbitrary lookup section.

Dave,
thanks again for your attention and response to what is a lack of my ability
to figure this out. Can you offer a solution for this?

 

[Michael]

Dave,
I hate to be the reason the folks with your talent might become "unnerved"
with involving yourself providing direction and knowledge to one, myself, a
dribbling idiot, which now has to ask you...how does one find Chip Pearson's
page? Thanks again...100 thanks for your patience!!
Michael

 

[Dave Peterson]

Oopsie...

http://cpearson.com/excel/lookups.htm

But if you use google to search the *excel* newsgroups, you would have found
thousands of hits to Chip's site. It's quite popular. You may want to bookmark
it for other stuff.

Dave,
I hate to be the reason the folks with your talent might become "unnerved"
with involving yourself providing direction and knowledge to one, myself, a
dribbling idiot, which now has to ask you...how does one find Chip Pearson's
page? Thanks again...100 thanks for your patience!!
Michael

 

[Michael]

Dave,
Many thanks!! This one is a bit over my head. I'll give myself a week to
play with this and see if I eventually grasp.
Thank you.
Michael R