Formula for the value in the cell one row above the row of the max of a range

[Stan]

May I please ask for your kind help?

Every 154 rows, I have 2 rows with data.
Suppose I have data in cells A153:E154.

I am trying to write a formula for G154, that would

1. match the max(A154:E154)
2. Find the column where the max [found in step 1] is
located

3. Return the value in the cell one row above this [#154]
row, same column# as the column found in step 2 above.

For example, if the max of range A154:E154 is in cell
C154, I would like to have the value of cell C153
displayed in cell G154.


I have been trying to use all sorts of combinations of
Match(), Index(), Address(), Cell() and Indirect(), but I
guess my knowledge of Excel functions is not enough to
solve this.

One formula that I tried was

Indirect(Address(154, match(max(A154:E154), A154:E154,
0),4))

I thought Address=Address(row number, col number,)

but this formula doesn't seem to work, when I paste it
down - "Address(154" part - doesn't change when I paste it
down...

Thank you for your kind help

 

[Ron Rosenfeld]

May I please ask for your kind help?

Every 154 rows, I have 2 rows with data.
Suppose I have data in cells A153:E154.

I am trying to write a formula for G154, that would

1. match the max(A154:E154)
2. Find the column where the max [found in step 1] is
located

3. Return the value in the cell one row above this [#154]
row, same column# as the column found in step 2 above.

For example, if the max of range A154:E154 is in cell
C154, I would like to have the value of cell C153
displayed in cell G154.

=OFFSET(A153,0,MATCH(MAX(A154:E154),A154:E154,0)-1)


--ron

 

[Ron Rosenfeld]

Thank you! You truly made my day.

You're welcome. Your clear explanation of the problem made it easier than is
sometimes the case.



--ron