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

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

--
Kevin Vaughn


"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?