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