Cell value using Max

[Guest]

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(OFFSET(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?

 

[Guest]

If the cell returning the address of $w$57 is in B2, try this formula:

=INDIRECT("a"&ROW(INDIRECT(B2))-1)

 

[Guest]

This works for; nice & simple, too!

 

[Guest]

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