Search code examples
excelreferencecellmax

Excel formula to find reference used by other cell

Is there a way to find out the address of the cell being referenced in another cell in excel?

E.g. cell C1 contains formula =max(A:A) and returns a value of 10 which is actually referenced to cell A10. Can I use a formula in cell B that returns 'A10'?

And no, I don't want to use VBA at all.

Solution

  • Assuming that your entries are in Cells A1:A7, you can do it this way ...

    In Cell B1, the formula =MAX(A1:A7) and

    in Cell B2, the cell location of the maximum number in the range (shown in B1) is given by the formula

    =CELL("address",INDEX(A1:A7,MATCH(MAX(A1:A7),A1:A7,0)))

    OR

    =ADDRESS(MATCH(MAX(A1:A7),$A$1:A7,0),1)

    Mark as answer if it helps.