Need to refer to a cell's address, not it's content...

[Guest]

Hello,

I have a range on which I am looking for the max value:
E51=MAX($B$54:$L$54)

Now, rather than extracting the cell's content, I'd like to extract the
cell's address, so that I can then display the content of that column's
header in range B2:L2.

Example:
If the MAX function in E51 above computes the content of cell H54, rather
than displaying the content of H54 I would like to display the content of H2.

Hopefully I'm making sense...

Thanks in advance for any help you can provide!

Alain

 

[JE McGimpsey]

One way:

=INDEX($B$2:$L$2,MATCH(MAX($B$54:$L$54),$B$54:$L$54,FALSE))

 

[Guest]

JE,

That works for me, thank you very much.

I'm surprised there's no simpler function to give a cell's address; from
there, I would have used the OFFSET function.

Oh well, love to learn something everyday!

Thanks again,

Alain

 

[JE McGimpsey]

I can't think of how that would be any simpler. In Outline, what I
suggested requires 3 functions (INDEX,MATCH,MAX).

Your model would, I would think, also require at least 3
(OFFSET,ADDRESS,MAX), unless you're thinking there should be a special
ADDRESSOFMAX() function.

Also using INDEX instead of OFFSET is more efficient in that INDEX is
not volatile (for later versions of XL) while OFFSET is.