No Problem. Glad I could help (kind of :-).

FWIW the solution I posted will work -- I tested it thoroughly :-)

Gary


AG Wrote:
Thanks for the reply Gary.
Your solution might work but see Kevin's reply for a simplier option.

"AG" wrote:

Looking for a formula that will give the value of the leftmost cell
(column
A), 1 row up from a cell containing the maximum value in a table of
values
contained in the range $B$5:$AF$97, named rng.

I can find the address of the max value via the array formula:


=CELL("Address",INDEX(rng,MATCH(TRUE,COUNTIF(OFFSE T(rng,ROW(rng)-CELL("Row",rng),0,1,),MAX(rng))0,0),MATCH(MAX(rng ),INDEX(rng,MATCH(TRUE,COUNTIF(OFFSET(rng,ROW(rng)-CELL("Row",rng),0,1,),MAX(rng))0,0),0),0)))

So if the max value happened to be in cell $W$57, how would I get the
value
of cell $A$56?



--
GaryE
Posted from - http://www.officehelp.in