How can I get the Cell Reference of a Named Range in Excel?

Question

I solved the issue, but I'm wondering if there's a better way to do this. What I have is a column of some named ranges ("NAME"), and I want a formula to the right that looks up the named range and gives me the cell reference ("C352").

I eventually solved it with this:

=CHAR(64+COLUMN(INDIRECT(C2, FALSE)))&ROW(INDIRECT(C2, FALSE))

The CHAR part is to translate the column number (3) to the letter (C), and it doesn't work past column Z.

This shouldn't be the best way to do it. I saw some VBA solutions, is that the only possible way to do this better?

Answer 1

=ADDRESS(ROW(INDIRECT(C2)),COLUMN(INDIRECT(C2)),4,1)

How it works

• INDIRECT(C2) returns a reference address specified by C2. The range refered to by the named range in this case.
• ROW( ... ) returns the row of the cell specified
• COLUMN( ... ) ditto for column
• ADDRESS( r, c, 4, 1) returns the address or row r, column c. 4 means relative (ie no $'s. 1 means A1 style. So ADDRESS(ROW( ... ),COLUMN( ... ),4,1) will be the address of the named range in A1 notation

Answer 2

If you can live with $ in the reference you could use:

=CELL("address",INDIRECT(C1))

or:

=SUBSTITUTE(CELL("address",INDIRECT(C1)),"$",)

to drop the $

Answer 3

=ADDRESS(ROW(INDIRECT(C2, FALSE)), COLUMN(INDIRECT(C2, FALSE)), abs_num)

where abs_num is 1 to return an absolute address (e.g., "$C$352"), 4 to return a relative address (e.g., "C352"), or 2 for "C$352", or 3 for "$C352".  (abs_num defaults to 1 (i.e., absolute) if omitted.)