How to find cell value if MAX is know?

[wytautas]

Hi,

Excel have formula: =MAX(DB5;CX5;CT5;CP5;CL5;CH5;CD5;BZ5;BV5;BR5;BN5;BJ5;BF5;BB5;AX5;AT5;AP5;AL5;AH5;AD5;Z5;V5;R5;N5;J5;F5), row MAX value is CX5.
I would like to get cell value in CW6 (-1 Row and -1 Column from MAX).

Does anyone have ideas?

Thanks!

 

[AlanY]

try

=ADDRESS(5-1,MATCH(MAX(DB5;CX5;CT5;CP5;CL5;CH5;CD5;BZ5;BV5;BR5;BN5;BJ5;BF5;BB5;AX5;AT5;AP5;AL5;AH5;AD5;Z5;V5;R5;N5;J5;F5),5:5)-1)

 

[wytautas]

try

=ADDRESS(5-1,MATCH(MAX(DB5;CX5;CT5;CP5;CL5;CH5;CD5;BZ5;BV5;BR5;BN5;BJ5;BF5;BB5;AX5;AT5;AP5;AL5;AH5;AD5;Z5;V5;R5;N5;J5;F5),5:5)-1)

No, it doesn't work correctly.

I have uploaded file there: Indexes Data.xlsx - Speedy Share - upload your files here

 

[AlanY]

didn't realised that you're going to put the formula in row 5 as well.

try this

=ADDRESS(ROW(A5)-1,MATCH(MAX(CX5:DB5:CP5:CT5:CH5:CL5:BZ5:CD5:BR5:BV5:BJ5:BN5:BB5:BF5:AT5:AX5:AL5:AP5:AD5:AH5:V5:Z5:N5:R5:F5:J5),$A5:$DB5)-1)

 

[JimM]

AlanY, wouldn't your formula give the address of the cell rather than the contents ?

Presumably you could use

=INDIRECT(ADDRESS(ROW(A5)-1,MATCH(MAX(CX5:DB5:CP5:CT5:CH5:CL5:BZ5:CD5:BR5:BV5:BJ5:BN5:BB5:BF5:AT5:AX5:AL5:AP5:AD5:AH5:V5:Z5:N5:R5:F5:J5),$A5:$DB5)-1))

 

[AlanY]

ok, that should works, in DH5 and copy down

=ADDRESS(ROW(A5)+1,MATCH(MAX(DB5,CX5,CT5,CP5,CL5,CH5,CD5,BZ5,BV5,BR5,BN5,BJ5,BF5,BB5,AX5,AT5,AP5,AL5,AH5,AD5,Z5,V5,R5,N5,J5,F5),A5:DB5,0)-1,1)

or you can simply that, if to use column DD

=ADDRESS(ROW(A5)+1,MATCH(DD5,A5:DB5,0)-1,1)

 

[AlanY]

AlanY, wouldn't your formula give the address of the cell rather than the contents ?

Presumably you could use

=INDIRECT(ADDRESS(ROW(A5)-1,MATCH(MAX(CX5:DB5:CP5:CT5:CH5:CL5:BZ5:CD5:BR5:BV5:BJ5:BN5:BB5:BF5:AT5:AX5:AL5:AP5:AD5:AH5:V5:Z5:N5:R5:F5:J5),$A5:$DB5)-1))

i think the OP already calculated the values but needed the address

 

[AlanY]

AlanY, wouldn't your formula give the address of the cell rather than the contents ?

Presumably you could use

=INDIRECT(ADDRESS(ROW(A5)-1,MATCH(MAX(CX5:DB5:CP5:CT5:CH5:CL5:BZ5:CD5:BR5:BV5:BJ5:BN5:BB5:BF5:AT5:AX5:AL5:AP5:AD5:AH5:V5:Z5:N5:R5:F5:J5),$A5:$DB5)-1))

apology, you're right (just read post#1 again).

try this
=INDIRECT(ADDRESS(ROW(A5)+1,MATCH(DD5,A5:DB5,0)-1,1))

btw, I assumed it's +1 Row as your example shows CW6.

Hi,

Excel have formula: =MAX(DB5;CX5;CT5;CP5;CL5;CH5;CD5;BZ5;BV5;BR5;BN5;BJ5;BF5;BB5;AX5;AT5;AP5;AL5;AH5;AD5;Z5;V5;R5;N5;J5;F5), row MAX value is CX5.
I would like to get cell value in CW6 (-1 Row and -1 Column from MAX).

Does anyone have ideas?

Thanks!

 

[wytautas]

Thanks AlanY and JimM, I have added INDIRECT and get what I want! Thanks!

 

[AlanY]

glad that you got it sorted