Convert INDEX/MATCH formulas to cell address

  • Hello!


    I'm a bit rusty on my VBA, but here goes:


    I have a workbook that has thousands of INDEX MATCH formulas that are drastically slowing down the performance of excel. Ideally what I'd like to do is write a macro that will replace the INDEX MATCH formulas in a specific selection with the direct cell reference. For example, replace "=INDEX(A:A, MATCH(D2, C:C, 0))" with "=A5". It seems that a find and replace macro using the CELL("address" function would make the most sense here but I'm open to other ideas.


    I found the below code for adding IFERROR to a given formula:

    It seems like I should be able to sub out the "=IFERROR(" with "=CELL(""address"", " but I keep getting a 1004 error.


    Any help would be greatly appreciated!


    Thanks,

    Rob

  • Hello and Welcome to the Forum :)


    Obviously, I do not know how your workbook is structured ...


    But, why do you bother with formulas ... when you need results ...?


    Is there a specific constraint ?


    In addition, the macro you have posted is not related to your question ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    The index match formulas save a lot of time up front to ensure the correct data is being pulled in, otherwise I would save myself the time and just direct link in the first place :).


    The reason I posted the IFERROR macro is because I believe it's doing something very close to what I'm trying to accomplish. That is, to go through all of my formulas and add a string at the beginning of each to get the desired result. In my case it's CELL("address" instead of IFERROR.


    Thanks.

  • Would you mind attaching a sample file with say 10 records to illustrate your objective ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    May be... you only need to use the formula =ADDRESS(MATCH(D2, C:C, 0),1)


    If not, do not hesitate to attach a sample file ... showing your expected result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Do not hesitate to post your feedback ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)