Converting Formulas to Ultimate Cell References [IndexMatch, Offset, Indirect]

[x0nar]

Hi,
I was wondering if it would possible to convert Formulas to the most final cell reference using VBA Macro.

For eg.
Let's say the formula in Cell H2 is as follows

=IFERROR(INDEX($D$2:$D$7,MATCH($F2,$C$2:$C$7,0)),"")

Now let's say the evaluation of this index match function directs us to Cell D3 (ie the Value in Cell F2 was same as that in Cell C3).
So would it be possible to convert this formula to

=D3

As its final evaluation was the value of D3.


To get to this I've tried using Evaluate(ActiveCell.Formula).Address

However it returns me with an error.

Thanks.

 

[Special-K99]

Does this work?

=INDIRECT(CELL("address",OFFSET($D$2,MATCH($F2,$c$2:$c$7,0)-1,0)))