Trevor ... (Happy morning)

It has been a long week so I am just getting back to all of this ... Since I
have had no further response from T. Valko & if you are still listening ...
Would you be able to explain formula Valko provided in same way you explained
yours (line item step by step) ... Not only is this helpful for me to better
understand the specific functions, but also the combination of functions &
order of calculations etc ... Formula is:


=IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"")

Thank you in advance ... Kha


"Trevor Shuttleworth" wrote:

Thanks for the feedback. You have a great day too


"Ken" wrote in message
...
Trevor ... (Happy morning)

I placed values in the applicable Range of a Blank WorkBook & worked this
formula thru to help me better understand the Formulas & sequence of
calculations (excellent explanation) ... I have learned much from those
that
are intimate with Excel & support these boards ... Please know I
appreciate
the time you have taken to provide this guidance for one of my many Excel
short-comings.

My Thanks ... Have a Happy & Safe Day ... Kha


"Trevor Shuttleworth" wrote:

OK

MAX(B3:P3)
returns the maximum value in the range B3 to P3

MATCH(MAX(B3:P3),B3:P3,0)
returns the offset of the maximum value within the range

MATCH(MAX(B3:P3),B3:P3,0)+1
calculates the column number (taking into account we start in column
B)

ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)
turns that into an address ... row 2, column whatever we just
calculated

=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1))
gets the value from the calculated address

So, let's say the maximum value of 9 was in cell L3 which is column 12

=MAX(B3:P3) = 9
=MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11
=MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12
=ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2
=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) =
column
12 or whatever

Regards

Trevor


"Ken" wrote in message
...
Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you
take a
moment to give me a step by step (in order) line-item breakdown of how
this
formula works? I need to ask T. Valko same thing ... His formula is
flawless
too.

Thank you for the guidance ... Kha

"Trevor Shuttleworth" wrote:

One way:

=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1))

If there are multiple maximum values, this will return the name of the
first
one.

Regards

Trevor


"Ken" wrote in message
...
Excel2003 ...

Range B2:P2 ... Names
Range B3:P42 ... Values

Looking for Formula in Range Q3:Q42 to return:

Q3 ... "Name" associated with MAX Value found in Range B3:P3
Q4 ... "Name" associated with MAX Value found in Range B4:P4
Q5 ... "Name" associated with MAX Value found in Range B5:P5

Note: also need to know how to handle if multiple "Names" found
with
same
MAX Value in the Range???

Thanks ... Kha