Return adress name of result

[Ramon1878]

When I look for min/max, not only I would like the result, but also the cell name the result is in as well as it's range name.

e.g. =MAX(B2:C20) The max is 100 in B6, B6 is part of a named range called Week 1.

Therefore we would like to have a formula that can retrieve:

100, B6, WEEK 1

Please help, I'be been up all night looking for this. Many thanks.

 

[bosco_yip]

The Define name : WEEK_1 (The name doesnot contain a space)

Formula :

=MAX(B2:C20,WEEK_1)

or,

=MIN(B2:C20,WEEK_1)

Regards