Index contains Address information?

[Scotster]

Just a query regarding the result from an Index formula really. I have a formula that I got help to create but I'm struggling to understand how the address is realised from the resulting index calculation.

The very simplified version of the formula is below:

=SUM(INDEX(A$2:A9,7,1):A9)

The "7" in the above is calculated but I wanted to give it as basic as I could. Table below to show the result from using the above formula.

The Index part of the formula will return "80" on its own. However, using the SUM, it references the resulting Index address of A8.

It made me wonder, is there a way of simply showing the address as the result an index formula, rather than the value of the location? I've used Match etc to find the address, but this would be far more elegant if it was easy to do. Is this a well known use of the function? I had never even considered it before.

10	Result
20	170
30
40
50
60
70
80
90

 

[RoryA]

You can use:

=CELL("address",INDEX(A$2:A9,7,1))

I don't really know why you would want to though.

 

[Scotster]

You can use:

=CELL("address",INDEX(A$2:A9,7,1))

I don't really know why you would want to though.

Thanks for that. I guess it was more for curiosity than anything. I thought the calculated formula was simply a value, rather than the stored address (and thus the value).

I guess it would be a good alternative to using multiple match criteria for a address formula lookup, where the address is required rather than the result.