LOOKUP & RETURN CELL ADDRESS

What formula would I use to look up the address of the maximum value in a
column and return the cell address not the value?

--
Carolan

Carolan wrote:
> What formula would I use to look up the address of the maximum value in a
> column and return the cell address not the value?
>

See the formula system I describe in:

http://tinyurl.com/dph4d

Assume you have a column header in row one, & that cell is named 'hdr'
Assume your column of values is named tbl, and starts in row 2

=ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),COLUMN(hdr))



will give you the address of the FIRST value equal to the max value in the
list

"Carolan" wrote:

> What formula would I use to look up the address of the maximum value in a
> column and return the cell address not the value?
>
> --
> Carolan

"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote...
>Assume you have a column header in row one, & that cell is named 'hdr'
>Assume your column of values is named tbl, and starts in row 2
>
>=ADDRESS(ROW(OFFSET(hdr,MATCH(MAX(tbl),tbl),0)),COLUMN(hdr))
....

MATCH(MAX(tbl),tbl) would only work when tbl is sorted in ascending order,
in which case ROWS(tbl) would be much simpler. Perhaps you meant
MATCH(MAX(tbl),tbl,0)?

More compact to use

=CELL("Address",INDEX(tbl,MATCH(MAX(tbl),tbl,0)))